PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS admins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL,
    updated_at TEXT,

    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,

    nome TEXT,
    email TEXT,

    role TEXT NOT NULL CHECK (role IN ('super', 'editor', 'viewer')),
    enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),
    last_login_at TEXT,
    note TEXT
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL,
    updated_at TEXT,

    telefono_whatsapp TEXT NOT NULL,
    nome TEXT NOT NULL,
    cognome TEXT NOT NULL,

    anno_inizio INTEGER,
    anno_fine INTEGER,
    sezione TEXT,

    ruolo TEXT NOT NULL CHECK (ruolo IN ('studente', 'docente', 'parente', 'altro')),

    email TEXT,
    note_utente TEXT,

    consenso_privacy INTEGER NOT NULL CHECK (consenso_privacy IN (0,1)),
    consenso_directory INTEGER NOT NULL DEFAULT 0 CHECK (consenso_directory IN (0,1)),
    consenso_contatto_whatsapp INTEGER NOT NULL DEFAULT 0 CHECK (consenso_contatto_whatsapp IN (0,1)),

    otp_code TEXT,
    otp_created_at TEXT,
    otp_expires_at TEXT,

    verification_status TEXT NOT NULL DEFAULT 'new'
        CHECK (verification_status IN ('new', 'pending_verification', 'verified', 'failed', 'expired')),

    verified_at TEXT,
    verified_by_admin_id INTEGER,

    user_status TEXT NOT NULL DEFAULT 'active'
        CHECK (user_status IN ('active', 'suspended', 'blocked', 'archived')),

    invito_gruppo_annunci INTEGER NOT NULL DEFAULT 0 CHECK (invito_gruppo_annunci IN (0,1)),
    invito_gruppo_chat INTEGER NOT NULL DEFAULT 0 CHECK (invito_gruppo_chat IN (0,1)),
    data_invito_annunci TEXT,
    data_invito_chat TEXT,

    note_admin TEXT,
    tipologia_utente TEXT,
    priorita TEXT,

    ip_registrazione TEXT,
    fonte_registrazione TEXT,

    FOREIGN KEY (verified_by_admin_id) REFERENCES admins(id)
);

CREATE TABLE IF NOT EXISTS audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL,

    admin_id INTEGER,
    user_id INTEGER,

    action TEXT NOT NULL,
    details TEXT,
    ip_address TEXT,

    FOREIGN KEY (admin_id) REFERENCES admins(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS access_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    last_seen_at TEXT,

    user_id INTEGER,
    admin_id INTEGER,

    session_token TEXT NOT NULL UNIQUE,
    session_type TEXT NOT NULL CHECK (session_type IN ('admin', 'user_area', 'magic_link')),
    active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),

    ip_address TEXT,
    user_agent TEXT,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (admin_id) REFERENCES admins(id)
);

CREATE INDEX IF NOT EXISTS idx_users_whatsapp
    ON users(telefono_whatsapp);

CREATE INDEX IF NOT EXISTS idx_users_cognome_nome
    ON users(cognome, nome);

CREATE INDEX IF NOT EXISTS idx_users_verification_status
    ON users(verification_status);

CREATE INDEX IF NOT EXISTS idx_users_user_status
    ON users(user_status);

CREATE INDEX IF NOT EXISTS idx_users_anno_sezione
    ON users(anno_inizio, anno_fine, sezione);

CREATE INDEX IF NOT EXISTS idx_users_ruolo
    ON users(ruolo);

CREATE INDEX IF NOT EXISTS idx_audit_log_user
    ON audit_log(user_id);

CREATE INDEX IF NOT EXISTS idx_access_sessions_token
    ON access_sessions(session_token);