-- Upgrade da MVP iniziale a versione con utenti, login sicura e OAuth.
-- Eseguire una sola volta sul database esistente.

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','operator') NOT NULL DEFAULT 'operator',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    INDEX idx_users_active (is_active),
    INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE email_accounts MODIFY encrypted_password TEXT NULL;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_provider ENUM('google','microsoft','custom') NULL AFTER auth_type;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_client_id VARCHAR(255) NULL AFTER oauth_provider;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS encrypted_oauth_client_secret TEXT NULL AFTER oauth_client_id;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_auth_url VARCHAR(500) NULL AFTER encrypted_oauth_client_secret;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_token_url VARCHAR(500) NULL AFTER oauth_auth_url;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_scopes TEXT NULL AFTER oauth_token_url;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_redirect_uri VARCHAR(500) NULL AFTER oauth_scopes;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS encrypted_oauth_access_token MEDIUMTEXT NULL AFTER oauth_redirect_uri;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS encrypted_oauth_refresh_token MEDIUMTEXT NULL AFTER encrypted_oauth_access_token;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_token_expires_at DATETIME NULL AFTER encrypted_oauth_refresh_token;
ALTER TABLE email_accounts ADD COLUMN IF NOT EXISTS oauth_connected_at DATETIME NULL AFTER oauth_token_expires_at;

CREATE TABLE IF NOT EXISTS oauth_states (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state CHAR(64) NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_oauth_state (state),
    CONSTRAINT fk_oauth_states_account FOREIGN KEY (account_id) REFERENCES email_accounts(id) ON DELETE CASCADE,
    CONSTRAINT fk_oauth_states_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_oauth_states_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
