/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.952Z
 *
 * Track D (P1-9) — Knex migration for the Finance 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) {
  // fee_groups
  await knex.schema.raw("-- =============================================\r\n  -- FEE GROUPS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS fee_groups (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    description TEXT,\r\n    amount REAL NOT NULL,\r\n    type TEXT CHECK(type IN ('tuition', 'transport', 'hostel', 'exam', 'library', 'activity', 'other')),\r\n    frequency TEXT CHECK(frequency IN ('one-time', 'monthly', 'termly', 'annually')),\r\n    academic_year TEXT,\r\n    due_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  );");

  // student_fees
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT FEES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS student_fees (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    fee_group_id INTEGER NOT NULL REFERENCES fee_groups(id),\r\n    amount REAL NOT NULL,\r\n    paid_amount REAL DEFAULT 0,\r\n    discount_amount REAL DEFAULT 0,\r\n    fine_amount REAL DEFAULT 0,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'partial', 'paid', 'cancelled')),\r\n    due_date DATE,\r\n    paid_at DATETIME,\r\n    plan_id INTEGER REFERENCES fee_plans(id),\r\n    applied_by INTEGER REFERENCES users(id),\r\n    academic_year 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  );");

  // payments
  await knex.schema.raw("-- =============================================\r\n  -- PAYMENTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS payments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_fee_id INTEGER NOT NULL REFERENCES student_fees(id),\r\n    amount REAL NOT NULL,\r\n    payment_date DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    payment_method TEXT CHECK(payment_method IN ('cash', 'bank', 'card', 'paynow', 'ecocash', 'internal_transfer')),\r\n    reference_number TEXT,\r\n    transaction_id TEXT,\r\n    paynow_poll_url TEXT,\r\n    collected_by INTEGER REFERENCES users(id),\r\n    notes TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'failed', 'cancelled')),\r\n    paid_at DATETIME,\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  );");

  // expenses
  await knex.schema.raw("-- =============================================\r\n  -- EXPENSES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS expenses (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    category TEXT NOT NULL,\r\n    subcategory TEXT,\r\n    description TEXT NOT NULL,\r\n    amount REAL NOT NULL,\r\n    expense_date DATE,\r\n    vendor TEXT,\r\n    invoice_number TEXT,\r\n    payment_method TEXT,\r\n    created_by INTEGER REFERENCES users(id),\r\n    approved_by INTEGER REFERENCES users(id),\r\n    approved_at DATETIME,\r\n    rejection_reason TEXT,\r\n    receipt_image TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected', 'paid')),\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  );");

  // chart_of_accounts
  await knex.schema.raw("-- ============== Operational core ==============\r\n\r\n  -- 1. Suppliers / vendors — REUSE the inventory module's suppliers table\r\n  -- (created above, line ~626). Inventory + finance share the same vendor\r\n  -- registry; no separate table needed. Finance controllers SELECT from the\r\n  -- same rows with their broader column set (code, city, bank_details).\r\n\r\n  -- 2. Chart of accounts (nominal ledger; full GL postings deferred).\r\n  CREATE TABLE IF NOT EXISTS chart_of_accounts (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    code TEXT NOT NULL UNIQUE,\r\n    name TEXT NOT NULL,\r\n    type TEXT NOT NULL CHECK(type IN ('asset','liability','equity','revenue','expense')),\r\n    parent_id INTEGER REFERENCES chart_of_accounts(id),\r\n    color TEXT,\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  );");

  // bank_accounts
  await knex.schema.raw("-- 3. Bank accounts / cash boxes / mobile-money wallets (single-level).\r\n  CREATE TABLE IF NOT EXISTS bank_accounts (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    bank_name TEXT,\r\n    account_number TEXT,\r\n    account_type TEXT DEFAULT 'bank' CHECK(account_type IN ('bank','cash','mobile_money')),\r\n    currency TEXT DEFAULT 'USD',\r\n    opening_balance REAL DEFAULT 0,\r\n    current_balance REAL DEFAULT 0,\r\n    chart_account_id INTEGER REFERENCES chart_of_accounts(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  );");

  // bank_reconciliations
  await knex.schema.raw("-- 4. Bank reconciliation sessions (one per statement period).\r\n  CREATE TABLE IF NOT EXISTS bank_reconciliations (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    bank_account_id INTEGER NOT NULL REFERENCES bank_accounts(id),\r\n    period_start DATE NOT NULL,\r\n    period_end DATE NOT NULL,\r\n    statement_balance REAL NOT NULL,\r\n    book_balance REAL DEFAULT 0,\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft','balanced','discrepancy')),\r\n    discrepancy_notes TEXT,\r\n    reconciled_at DATETIME,\r\n    reconciled_by INTEGER REFERENCES users(id),\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  );");

  // bank_reconciliation_lines
  await knex.schema.raw("-- 5. Per-line reconciliation matches (payment ↔ statement entry).\r\n  CREATE TABLE IF NOT EXISTS bank_reconciliation_lines (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    reconciliation_id INTEGER NOT NULL REFERENCES bank_reconciliations(id),\r\n    payment_id INTEGER REFERENCES payments(id),\r\n    statement_date DATE,\r\n    statement_amount REAL,\r\n    statement_reference TEXT,\r\n    match_status TEXT DEFAULT 'unmatched' CHECK(match_status IN ('matched','unmatched','ignored')),\r\n    resolution TEXT,\r\n    matched_at DATETIME,\r\n    matched_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  );");

  // invoices
  await knex.schema.raw("-- 6. Formal invoices issued to a guardian/student (one per billing event).\r\n  CREATE TABLE IF NOT EXISTS invoices (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    invoice_number TEXT NOT NULL UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    fee_group_id INTEGER REFERENCES fee_groups(id),\r\n    plan_id INTEGER REFERENCES fee_plans(id),\r\n    period_label TEXT,\r\n    subtotal REAL NOT NULL DEFAULT 0,\r\n    discount_amount REAL NOT NULL DEFAULT 0,\r\n    total REAL NOT NULL DEFAULT 0,\r\n    paid_amount REAL NOT NULL DEFAULT 0,\r\n    currency TEXT DEFAULT 'USD',\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft','issued','paid','partial','overdue','cancelled')),\r\n    issued_at DATETIME,\r\n    due_date DATE,\r\n    notes TEXT,\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  );");

  // trips
  await knex.schema.raw("-- 7. School trips / excursions — billed per student.\r\n  CREATE TABLE IF NOT EXISTS trips (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    destination TEXT,\r\n    departure_date DATE,\r\n    return_date DATE,\r\n    cost_per_student REAL NOT NULL DEFAULT 0,\r\n    total_capacity INTEGER,\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft','published','closed','completed','cancelled')),\r\n    description TEXT,\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  );");

  // trip_payments
  await knex.schema.raw("-- 8. Trip payments (one row per student per trip).\r\n  CREATE TABLE IF NOT EXISTS trip_payments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    trip_id INTEGER NOT NULL REFERENCES trips(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    invoiced_amount REAL NOT NULL DEFAULT 0,\r\n    paid_amount REAL NOT NULL DEFAULT 0,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending','partial','paid','refunded','cancelled')),\r\n    paid_at DATETIME,\r\n    notes TEXT,\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    UNIQUE(trip_id, student_id)\r\n  );");

  // fee_plans
  await knex.schema.raw("-- 11. Fee plans — templates of installments. Apply step (§10b) materialises\r\n  -- these into actual student_fees rows.\r\n  CREATE TABLE IF NOT EXISTS fee_plans (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    fee_group_id INTEGER NOT NULL REFERENCES fee_groups(id),\r\n    total_amount REAL NOT NULL,\r\n    installments_count INTEGER NOT NULL DEFAULT 1,\r\n    start_date DATE,\r\n    end_date DATE,\r\n    academic_year TEXT,\r\n    auto_apply_to_class_id INTEGER REFERENCES classes(id),\r\n    notes TEXT,\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  );");

  // fee_plan_installments
  await knex.schema.raw("-- 12. Plan installments (one fee_plans row has N installments).\r\n  CREATE TABLE IF NOT EXISTS fee_plan_installments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    fee_plan_id INTEGER NOT NULL REFERENCES fee_plans(id),\r\n    sequence INTEGER NOT NULL,\r\n    label TEXT,\r\n    amount REAL NOT NULL,\r\n    due_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  );");

  // discounts
  await knex.schema.raw("-- 13. Discounts (per-student, per-group, per-fee-group, or universal).\r\n  CREATE TABLE IF NOT EXISTS discounts (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    kind TEXT NOT NULL CHECK(kind IN ('percent','fixed')),\r\n    value REAL NOT NULL,\r\n    scope TEXT NOT NULL CHECK(scope IN ('student','group','fee_group','all')),\r\n    scope_id INTEGER,\r\n    reason TEXT,\r\n    valid_from DATE,\r\n    valid_to DATE,\r\n    academic_year TEXT,\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  );");

  // student_fee_discounts
  await knex.schema.raw("-- 14. Join table: which discounts apply to which student_fees. Multiple\r\n  -- discounts can stack against a single fee line.\r\n  CREATE TABLE IF NOT EXISTS student_fee_discounts (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_fee_id INTEGER NOT NULL REFERENCES student_fees(id),\r\n    discount_id INTEGER NOT NULL REFERENCES discounts(id),\r\n    amount_applied REAL NOT NULL DEFAULT 0,\r\n    applied_at DATETIME DEFAULT (datetime('now','localtime')),\r\n    applied_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    UNIQUE(student_fee_id, discount_id)\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_fee_discounts" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "discounts" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "fee_plan_installments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "fee_plans" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "trip_payments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "trips" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "invoices" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "bank_reconciliation_lines" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "bank_reconciliations" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "bank_accounts" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "chart_of_accounts" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "expenses" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "payments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_fees" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "fee_groups" + ';');
};
