PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  full_name TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'employee' CHECK(role IN ('employee','manager','hr','admin')),
  team TEXT NOT NULL DEFAULT '',
  position_title TEXT NOT NULL DEFAULT '',
  cin_nic TEXT NOT NULL DEFAULT '',
  phone TEXT NOT NULL DEFAULT '',
  email TEXT NOT NULL 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 TEXT NOT NULL DEFAULT '',
  bank_account TEXT NOT NULL DEFAULT '',
  basic_salary REAL NOT NULL DEFAULT 0,
  manager_id INTEGER REFERENCES users(id),
  start_date TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  payroll_admin INTEGER NOT NULL DEFAULT 0,
  created_at TEXT 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 REAL NOT NULL DEFAULT 0,
  annual_entitlement REAL NOT NULL DEFAULT 0,
  annual_used REAL NOT NULL DEFAULT 0,
  sick_opening REAL NOT NULL DEFAULT 0,
  sick_entitlement REAL NOT NULL DEFAULT 0,
  sick_used REAL NOT NULL DEFAULT 0,
  PRIMARY KEY(user_id, year)
);

CREATE TABLE IF NOT EXISTS leave_requests (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  leave_type TEXT NOT NULL CHECK(leave_type IN ('annual','sick','unplanned','wfh','unpaid','menstrual')),
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  day_fraction REAL NOT NULL DEFAULT 1,
  reason TEXT NOT NULL DEFAULT '',
  attachment TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected')),
  decided_by INTEGER REFERENCES users(id),
  decision_note TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS weekend_work (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  work_date TEXT NOT NULL,
  work_type TEXT NOT NULL CHECK(work_type IN ('saturday','sunday','on_call')),
  intervention_hours REAL NOT NULL DEFAULT 0,
  notes TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected')),
  decided_by INTEGER REFERENCES users(id),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS monthly_salaries (
  id INTEGER PRIMARY KEY,
  month TEXT NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  basic_salary REAL NOT NULL DEFAULT 0,
  salary_increase REAL NOT NULL DEFAULT 0,
  commission REAL NOT NULL DEFAULT 0,
  overtime_adjustment REAL NOT NULL DEFAULT 0,
  presence_bonus REAL NOT NULL DEFAULT 0,
  special_bonus REAL NOT NULL DEFAULT 0,
  transport REAL NOT NULL DEFAULT 0,
  loan_refund REAL NOT NULL DEFAULT 0,
  medical_deduction REAL NOT NULL DEFAULT 0,
  notes TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(month, user_id)
);

CREATE TABLE IF NOT EXISTS payroll_adjustments (
  id INTEGER PRIMARY KEY,
  month TEXT NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id),
  presence_bonus REAL NOT NULL DEFAULT 0,
  overtime_adjustment REAL NOT NULL DEFAULT 0,
  note TEXT NOT NULL DEFAULT '',
  updated_by INTEGER REFERENCES users(id),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(month, user_id)
);

CREATE TABLE IF NOT EXISTS audit_log (
  id INTEGER PRIMARY KEY,
  actor_id INTEGER REFERENCES users(id),
  action TEXT NOT NULL,
  entity TEXT NOT NULL,
  entity_id INTEGER,
  details TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public_holidays (
  id INTEGER PRIMARY KEY,
  country TEXT NOT NULL CHECK(country IN ('MU', 'FR')),
  holiday_date TEXT NOT NULL,
  name TEXT NOT NULL,
  UNIQUE(country, holiday_date)
);

-- 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 TEXT 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)
);