-- Novalink HR Portal — schema
-- Run once against a fresh database:
--   mysql -u youruser -p yourdatabase < sql/schema.sql

CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    department_id INT DEFAULT NULL,
    position VARCHAR(100) NOT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(12,2) NOT NULL DEFAULT 0,
    photo_url VARCHAR(255) DEFAULT NULL,
    verification_code VARCHAR(20) DEFAULT NULL UNIQUE,
    grade_level TINYINT NOT NULL DEFAULT 1,
    last_promotion_date DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    leave_type ENUM('annual','sick','casual','maternity','paternity','unpaid','other') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested INT NOT NULL,
    reason TEXT,
    status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    admin_comment VARCHAR(255) DEFAULT NULL,
    decided_by INT DEFAULT NULL,
    decided_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (decided_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_emp_dates (employee_id, start_date, end_date),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    work_date DATE NOT NULL,
    clock_in DATETIME DEFAULT NULL,
    clock_out DATETIME DEFAULT NULL,
    clock_in_lat DECIMAL(10,7) DEFAULT NULL,
    clock_in_lng DECIMAL(10,7) DEFAULT NULL,
    clock_out_lat DECIMAL(10,7) DEFAULT NULL,
    clock_out_lng DECIMAL(10,7) DEFAULT NULL,
    clock_in_ip VARCHAR(45) DEFAULT NULL,
    clock_out_ip VARCHAR(45) DEFAULT NULL,
    status ENUM('present','late','absent','on_leave') NOT NULL DEFAULT 'present',
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_emp_day (employee_id, work_date),
    INDEX idx_date (work_date)
) ENGINE=InnoDB;

-- Binds a device (identified by a long-lived cookie token, not true
-- hardware fingerprinting) to a single employee. Once a device has
-- been used to clock in/out by one employee, no other employee can
-- use that same device — this is the "buddy punching" defense.
CREATE TABLE IF NOT EXISTS device_bindings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_token VARCHAR(64) NOT NULL UNIQUE,
    employee_id INT NOT NULL,
    first_bound_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_employee (employee_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payslips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    period_month TINYINT NOT NULL,
    period_year SMALLINT NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL,
    allowances DECIMAL(12,2) NOT NULL DEFAULT 0,
    deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_pay DECIMAL(12,2) NOT NULL,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_emp_period (employee_id, period_month, period_year)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Sample departments so the portal isn't empty on first run
INSERT INTO departments (name) VALUES
('Human Resources'), ('Engineering'), ('Finance'), ('Sales'), ('Operations');

-- No admin account is seeded here on purpose — a hand-typed password
-- hash is easy to get subtly wrong. Run setup.php once after importing
-- this file to create your first admin login with a real bcrypt hash
-- generated by PHP itself, then delete that script.