-- =====================================================================
--  HRPay LK — Schema
--  Module 1 (Employee Database) + shared foundation tables.
--  MySQL 8 / MariaDB 10.4+. Run once to provision the database.
-- =====================================================================

CREATE DATABASE IF NOT EXISTS hrpay
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hrpay;

-- ----------------------------------------------------------------------
-- Departments
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS departments (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(20)  NOT NULL,
  name        VARCHAR(120) NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_dept_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Employees  (Module 1 core record; columns also serve later payroll modules)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employees (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_code   VARCHAR(20)  NOT NULL,            -- e.g. EMP0001
  first_name      VARCHAR(80)  NOT NULL,
  last_name       VARCHAR(80)  NOT NULL,
  nic             VARCHAR(12)  NOT NULL,            -- SL NIC (old 9+V/X or new 12-digit)
  date_of_birth   DATE             NULL,
  gender          ENUM('male','female','other') NULL,
  marital_status  ENUM('single','married','other') NULL,
  email           VARCHAR(150)     NULL,
  phone           VARCHAR(20)      NULL,
  address         VARCHAR(255)     NULL,

  department_id   INT UNSIGNED     NULL,
  designation     VARCHAR(120)     NULL,
  employment_type ENUM('permanent','contract','probation','intern') NOT NULL DEFAULT 'probation',
  date_joined     DATE         NOT NULL,
  date_confirmed  DATE             NULL,
  date_left       DATE             NULL,
  status          ENUM('active','inactive','resigned','terminated') NOT NULL DEFAULT 'active',

  -- Payroll-related (used by Modules 4/5/6; stored here from the start)
  basic_salary    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  epf_number      VARCHAR(30)      NULL,
  is_tax_resident TINYINT(1)   NOT NULL DEFAULT 1,
  bank_name       VARCHAR(120)     NULL,
  bank_branch     VARCHAR(120)     NULL,
  bank_account    VARCHAR(30)      NULL,

  photo_path      VARCHAR(255)     NULL,
  created_by      INT UNSIGNED     NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_emp_code (employee_code),
  UNIQUE KEY uq_emp_nic (nic),
  KEY idx_emp_dept (department_id),
  KEY idx_emp_status (status),
  KEY idx_emp_name (last_name, first_name),
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
      REFERENCES departments (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Users  (system logins; role drives RBAC; may link to an employee)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username       VARCHAR(60)  NOT NULL,
  email          VARCHAR(150) NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  role           ENUM('admin','hr','manager','employee') NOT NULL DEFAULT 'employee',
  employee_id    INT UNSIGNED     NULL,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until   DATETIME         NULL,
  last_login_at  DATETIME         NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_username (username),
  UNIQUE KEY uq_user_email (email),
  KEY idx_user_emp (employee_id),
  CONSTRAINT fk_user_emp FOREIGN KEY (employee_id)
      REFERENCES employees (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Login attempts  (throttling / brute-force defence, keyed by IP+username)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_attempts (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username    VARCHAR(60)  NOT NULL,
  ip_address  VARCHAR(45)  NOT NULL,
  successful  TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attempt_lookup (username, ip_address, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Audit log  (who did what, when — required for payroll accountability)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED     NULL,
  action      VARCHAR(60)  NOT NULL,    -- e.g. employee.create
  entity      VARCHAR(60)      NULL,    -- e.g. employee
  entity_id   VARCHAR(60)      NULL,
  details     JSON             NULL,
  ip_address  VARCHAR(45)      NULL,
  user_agent  VARCHAR(255)     NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_user (user_id),
  KEY idx_audit_entity (entity, entity_id),
  KEY idx_audit_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------
-- Seed data
-- ----------------------------------------------------------------------
INSERT INTO departments (code, name) VALUES
  ('ADM', 'Administration'),
  ('HR',  'Human Resources'),
  ('FIN', 'Finance'),
  ('ENG', 'Engineering')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- The default admin user is NOT seeded here (a hash must be generated safely).
-- Run the setup script after import:  php bin/setup_admin.php
-- It creates username "admin" with a password you provide and a proper hash.