CREATE DATABASE IF NOT EXISTS authenticator_app;

USE authenticator_app;






CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    otp_secret VARCHAR(32) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE auth_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    secret_key VARCHAR(255) NOT NULL, -- Base32 encoded secret for TOTP
    qr_code TEXT NOT NULL, -- Stored QR code image path or Base64
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE authentications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    secret VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);



CREATE TABLE otp_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    otp_code VARCHAR(6) NOT NULL, -- The generated TOTP code
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- When it was generated
    used_at TIMESTAMP NULL DEFAULT NULL, -- When it was used
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE scanned_auths (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    secret VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);


ALTER TABLE scanned_auths ADD COLUMN app_name VARCHAR(255) NOT NULL AFTER id;
ALTER TABLE users ADD COLUMN totp_secret VARCHAR(32) NULL;
ALTER TABLE authentications ADD COLUMN qr_code_path TEXT;


CREATE TABLE registered_apps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    app_id VARCHAR(50) UNIQUE NOT NULL,
    api_key VARCHAR(100) NOT NULL,
    app_name VARCHAR(100) NOT NULL,
    callback_url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_authentications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    secret VARCHAR(50) NOT NULL,
    qr_code_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES registered_apps(app_id) ON DELETE CASCADE
);


ALTER TABLE users ADD COLUMN api_key VARCHAR(64) UNIQUE

UPDATE users SET api_key = MD5(CONCAT(user_id, NOW()));
