/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.953Z
 *
 * Track D (P1-9) — Knex migration for the HR & Payroll concern.
 *
 * Each CREATE TABLE statement is the original SQL from
 * server/src/database/init.js, executed via knex.schema.raw() to
 * preserve CHECK constraints, FTS5 virtual tables, triggers, and
 * other constructs the Knex DSL cannot easily express.
 *
 * Future PRs may convert individual tables to knex.schema.createTable()
 * with the schema-builder DSL.
 */

exports.up = async function(knex) {
  // staff_records
  await knex.schema.raw("-- =============================================\r\n  -- STAFF EMPLOYMENT RECORDS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS staff_records (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    user_id INTEGER NOT NULL REFERENCES users(id),\r\n    staff_number TEXT UNIQUE,\r\n    role_id INTEGER REFERENCES staff_roles(id),\r\n    salary_grade_id INTEGER REFERENCES salary_grades(id),\r\n    employment_type TEXT DEFAULT 'full-time' CHECK(employment_type IN ('full-time', 'part-time', 'contract', 'temporary', 'intern')),\r\n    appointment_date DATE,\r\n    confirmation_date DATE,\r\n    termination_date DATE,\r\n    termination_reason TEXT,\r\n    bank_name TEXT,\r\n    bank_branch TEXT,\r\n    account_number TEXT,\r\n    account_name TEXT,\r\n    department TEXT,\r\n    designation TEXT,\r\n    reports_to INTEGER REFERENCES users(id),\r\n    is_active INTEGER DEFAULT 1,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // vacancies
  await knex.schema.raw("-- =============================================\r\n  -- RECRUITMENT MODULE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS vacancies (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    title TEXT NOT NULL,\r\n    description TEXT,\r\n    requirements TEXT,\r\n    department TEXT,\r\n    status TEXT DEFAULT 'open' CHECK(status IN ('open', 'closed', 'cancelled')),\r\n    closing_date DATE,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // applicants
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS applicants (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    vacancy_id INTEGER REFERENCES vacancies(id),\r\n    first_name TEXT NOT NULL,\r\n    last_name TEXT NOT NULL,\r\n    email TEXT NOT NULL,\r\n    phone TEXT,\r\n    resume_url TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'shortlisted', 'interviewed', 'offered', 'hired', 'rejected')),\r\n    interview_date DATETIME,\r\n    notes TEXT,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // leave_types
  await knex.schema.raw("-- =============================================\r\n  -- LEAVE TYPES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS leave_types (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    description TEXT,\r\n    max_days_per_year INTEGER,\r\n    is_paid INTEGER DEFAULT 1,\r\n    requires_approval INTEGER DEFAULT 1,\r\n    color TEXT DEFAULT '#3b82f6',\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // leave_requests
  await knex.schema.raw("-- =============================================\r\n  -- LEAVE REQUESTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS leave_requests (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    staff_id INTEGER NOT NULL REFERENCES users(id),\r\n    leave_type_id INTEGER NOT NULL REFERENCES leave_types(id),\r\n    start_date DATE NOT NULL,\r\n    end_date DATE NOT NULL,\r\n    days_count REAL NOT NULL,\r\n    reason TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected', 'cancelled')),\r\n    approved_by INTEGER REFERENCES users(id),\r\n    approved_at DATETIME,\r\n    rejection_reason TEXT,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // staff_attendance
  await knex.schema.raw("-- =============================================\r\n  -- STAFF ATTENDANCE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS staff_attendance (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    staff_id INTEGER NOT NULL REFERENCES users(id),\r\n    date DATE NOT NULL,\r\n    check_in DATETIME,\r\n    check_out DATETIME,\r\n    status TEXT DEFAULT 'present' CHECK(status IN ('present', 'absent', 'late', 'half-day', 'on-leave')),\r\n    remarks TEXT,\r\n    marked_by INTEGER REFERENCES users(id),\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // payroll_runs
  await knex.schema.raw("-- =============================================\r\n  -- PAYROLL GENERATION\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS payroll_runs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    period_month INTEGER NOT NULL,\r\n    period_year INTEGER NOT NULL,\r\n    description TEXT,\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'calculated', 'approved', 'paid', 'cancelled')),\r\n    total_gross REAL DEFAULT 0,\r\n    total_deductions REAL DEFAULT 0,\r\n    total_net REAL DEFAULT 0,\r\n    approved_by INTEGER REFERENCES users(id),\r\n    paid_at DATETIME,\r\n    created_by INTEGER REFERENCES users(id),\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // payslips
  await knex.schema.raw("-- =============================================\r\n  -- PAYSLIPS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS payslips (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    staff_id INTEGER NOT NULL REFERENCES users(id),\r\n    payroll_run_id INTEGER NOT NULL REFERENCES payroll_runs(id),\r\n    basic_salary REAL NOT NULL,\r\n    housing_allowance REAL DEFAULT 0,\r\n    transport_allowance REAL DEFAULT 0,\r\n    medical_allowance REAL DEFAULT 0,\r\n    other_allowances REAL DEFAULT 0,\r\n    gross_salary REAL NOT NULL,\r\n    tax_deduction REAL DEFAULT 0,\r\n    pension_deduction REAL DEFAULT 0,\r\n    other_deductions REAL DEFAULT 0,\r\n    total_deductions REAL DEFAULT 0,\r\n    net_salary REAL NOT NULL,\r\n    bank_reference TEXT,\r\n    paid_at DATETIME,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'paid', 'cancelled')),\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "payslips" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "payroll_runs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "staff_attendance" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "leave_requests" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "leave_types" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "applicants" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "vacancies" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "staff_records" + ';');
};
