-- Create two-factor authentication table
CREATE TABLE two_factor_auth
(
    id              UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    user_id         UUID        NOT NULL UNIQUE REFERENCES users (id) ON DELETE CASCADE,
    secret_key      VARCHAR(255) NOT NULL,
    enabled         BOOLEAN     DEFAULT FALSE,
    backup_codes    TEXT[],              -- Array of backup codes
    created_at      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create login attempts tracking table for account lockout
CREATE TABLE login_attempts
(
    id              UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    email           VARCHAR(100) NOT NULL,
    ip_address      VARCHAR(45),
    success         BOOLEAN     DEFAULT FALSE,
    failure_reason  VARCHAR(255),
    timestamp       TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create trusted devices table
CREATE TABLE trusted_devices
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    user_id             UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    device_name         VARCHAR(255),
    device_fingerprint  VARCHAR(255) NOT NULL,
    ip_address          VARCHAR(45),
    first_seen          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen           TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_trusted          BOOLEAN     DEFAULT FALSE,
    UNIQUE(user_id, device_fingerprint)
);

-- Add account lockout fields to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS failed_login_attempts INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS account_locked_until TIMESTAMP;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_failed_login TIMESTAMP;

-- Create indexes for better query performance
CREATE INDEX idx_two_factor_auth_user_id ON two_factor_auth (user_id);
CREATE INDEX idx_two_factor_auth_enabled ON two_factor_auth (enabled) WHERE enabled = TRUE;

CREATE INDEX idx_login_attempts_email ON login_attempts (email);
CREATE INDEX idx_login_attempts_timestamp ON login_attempts (timestamp DESC);
CREATE INDEX idx_login_attempts_ip_address ON login_attempts (ip_address);
CREATE INDEX idx_login_attempts_email_timestamp ON login_attempts (email, timestamp DESC);

CREATE INDEX idx_trusted_devices_user_id ON trusted_devices (user_id);
CREATE INDEX idx_trusted_devices_device_fingerprint ON trusted_devices (device_fingerprint);
CREATE INDEX idx_trusted_devices_is_trusted ON trusted_devices (is_trusted) WHERE is_trusted = TRUE;

