-- Schéma PostgreSQL pour RH Planète (rh_planete)

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(64) UNIQUE NOT NULL,
    full_name VARCHAR(128) NOT NULL,
    first_name VARCHAR(64),
    last_name VARCHAR(64),
    password_hash VARCHAR(256) NOT NULL,
    role VARCHAR(32) NOT NULL DEFAULT 'employee' CHECK (role IN ('employee', 'manager', 'hr', 'admin')),
    team VARCHAR(64) NOT NULL DEFAULT '',
    position_title VARCHAR(128) DEFAULT '',
    cin_nic VARCHAR(32) DEFAULT '',
    phone VARCHAR(32) DEFAULT '',
    email VARCHAR(128) DEFAULT '',
    postal_address TEXT NOT NULL DEFAULT '',
    address_line1 TEXT NOT NULL DEFAULT '',
    address_line2 TEXT NOT NULL DEFAULT '',
    postal_code TEXT NOT NULL DEFAULT '',
    city TEXT NOT NULL DEFAULT '',
    country TEXT NOT NULL DEFAULT '',
    emergency_contact_name TEXT NOT NULL DEFAULT '',
    emergency_contact_phone TEXT NOT NULL DEFAULT '',
    bank_name VARCHAR(64) DEFAULT '',
    bank_account VARCHAR(64) DEFAULT '',
    basic_salary NUMERIC(12, 2) DEFAULT 0,
    manager_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    start_date DATE,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    payroll_admin BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS balances (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    year INTEGER NOT NULL,
    annual_opening NUMERIC(6, 2) NOT NULL DEFAULT 0,
    annual_entitlement NUMERIC(6, 2) NOT NULL DEFAULT 0,
    annual_used NUMERIC(6, 2) NOT NULL DEFAULT 0,
    sick_opening NUMERIC(6, 2) NOT NULL DEFAULT 0,
    sick_entitlement NUMERIC(6, 2) NOT NULL DEFAULT 0,
    sick_used NUMERIC(6, 2) NOT NULL DEFAULT 0,
    PRIMARY KEY(user_id, year)
);

CREATE TABLE IF NOT EXISTS leave_requests (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    leave_type VARCHAR(32) NOT NULL CHECK(leave_type IN ('annual', 'sick', 'unplanned', 'wfh', 'unpaid', 'menstrual')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    day_fraction NUMERIC(3, 1) NOT NULL DEFAULT 1.0,
    reason TEXT NOT NULL DEFAULT '',
    attachment VARCHAR(255),
    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected')),
    decided_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    decision_note TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS weekend_work (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    work_date DATE NOT NULL,
    work_type VARCHAR(32) NOT NULL CHECK(work_type IN ('saturday', 'sunday', 'on_call')),
    intervention_hours NUMERIC(6, 2) NOT NULL DEFAULT 0,
    notes TEXT NOT NULL DEFAULT '',
    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected')),
    decided_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS monthly_salaries (
    id SERIAL PRIMARY KEY,
    month VARCHAR(7) NOT NULL, -- format YYYY-MM
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    basic_salary NUMERIC(12, 2) NOT NULL DEFAULT 0,
    salary_increase NUMERIC(12, 2) NOT NULL DEFAULT 0,
    commission NUMERIC(12, 2) NOT NULL DEFAULT 0,
    overtime_adjustment NUMERIC(12, 2) NOT NULL DEFAULT 0,
    presence_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,
    special_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,
    transport NUMERIC(12, 2) NOT NULL DEFAULT 0,
    loan_refund NUMERIC(12, 2) NOT NULL DEFAULT 0,
    medical_deduction NUMERIC(12, 2) NOT NULL DEFAULT 0,
    notes TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(month, user_id)
);

CREATE TABLE IF NOT EXISTS payroll_adjustments (
    id SERIAL PRIMARY KEY,
    month VARCHAR(7) NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    presence_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,
    overtime_adjustment NUMERIC(12, 2) NOT NULL DEFAULT 0,
    note TEXT NOT NULL DEFAULT '',
    updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(month, user_id)
);

CREATE TABLE IF NOT EXISTS audit_log (
    id SERIAL PRIMARY KEY,
    actor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(64) NOT NULL,
    entity VARCHAR(64) NOT NULL,
    entity_id INTEGER,
    details TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public_holidays (
    id SERIAL PRIMARY KEY,
    country VARCHAR(4) NOT NULL CHECK(country IN ('MU', 'FR')),
    holiday_date DATE NOT NULL,
    name VARCHAR(128) NOT NULL,
    UNIQUE(country, holiday_date)
);

-- Index pour optimiser les requêtes fréquentes
CREATE INDEX IF NOT EXISTS idx_leave_requests_user ON leave_requests(user_id);
CREATE INDEX IF NOT EXISTS idx_leave_requests_dates ON leave_requests(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_weekend_work_user ON weekend_work(user_id);
CREATE INDEX IF NOT EXISTS idx_weekend_work_date ON weekend_work(work_date);
CREATE INDEX IF NOT EXISTS idx_monthly_salaries_month ON monthly_salaries(month);
CREATE INDEX IF NOT EXISTS idx_monthly_salaries_user ON monthly_salaries(user_id);

-- Explicit RH assessment of the first six months; no inference from missing events.
CREATE TABLE IF NOT EXISTS attendance_reviews (
    user_id INTEGER NOT NULL REFERENCES users(id),
    service_start DATE NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('unknown','confirmed','not_met')),
    note TEXT NOT NULL DEFAULT '',
    reviewed_by INTEGER REFERENCES users(id),
    reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(user_id, service_start)
);