-- SQLite Schema for Printer Usage Tracking System

CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    is_active INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    department TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS print_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    name_snapshot TEXT NOT NULL,
    department_snapshot TEXT NOT NULL,
    document_name TEXT,
    pages INTEGER NOT NULL DEFAULT 1,
    status TEXT NOT NULL DEFAULT 'Completed',
    printed_at TEXT NOT NULL,
    print_job_id TEXT UNIQUE,
    printer_name TEXT,
    file_size INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS active_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'WAITING', -- WAITING, COMPLETED, CANCELLED, EXPIRED
    detected_job_id TEXT,
    detected_document TEXT,
    detected_pages INTEGER,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for fast queries
CREATE INDEX IF NOT EXISTS idx_print_logs_printed_at ON print_logs(printed_at);
CREATE INDEX IF NOT EXISTS idx_print_logs_name_snapshot ON print_logs(name_snapshot);
CREATE INDEX IF NOT EXISTS idx_print_logs_dept_snapshot ON print_logs(department_snapshot);
CREATE INDEX IF NOT EXISTS idx_print_logs_status ON print_logs(status);
CREATE INDEX IF NOT EXISTS idx_print_logs_job_id ON print_logs(print_job_id);