-- UP: 20260402030000_strict_employee_separation.up.sql
--
-- Originally: "Drop old employees table (was linked to users - replacing with
-- standalone auth)" - a one-time schema transformation. The db-migrate tool
-- (crates/db-migrate) has no applied-migrations tracking table; it replays
-- every .sql file on every run, which turned this one-time DROP TABLE ...
-- CASCADE into a destructive operation that wiped the live employees table
-- (and every employee account, including admin accounts) on every migration
-- run. The standalone-schema transition already happened in production long
-- ago, so this is now safely a plain idempotent create.

-- 1. EMPLOYEES (Standalone Table - Not Linked to 'users')
CREATE TABLE IF NOT EXISTS employees (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    employee_code VARCHAR(50) UNIQUE,
    department_id UUID REFERENCES departments(id) ON DELETE SET NULL,
    designation_id UUID REFERENCES designations(id) ON DELETE SET NULL,
    role_code VARCHAR(50) NOT NULL DEFAULT 'STAFF',
    status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
    joined_at DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. EMPLOYEE SESSIONS (Standalone Auth)
CREATE TABLE IF NOT EXISTS employee_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_employees_email ON employees(email);
CREATE INDEX IF NOT EXISTS idx_employees_status ON employees(status);
CREATE INDEX IF NOT EXISTS idx_employee_sessions_token ON employee_sessions(token_hash);
