import http from "node:http";
import fsSync from "node:fs";
import fs from "node:fs/promises";
import path from "node:path";
import crypto from "node:crypto";
import { createRequire } from "node:module";
import { fileURLToPath } from "node:url";
import { calculateTax } from "./services/tax.service.js";
import { roles, transactionStatus, applicationStatus } from "./utils/constants.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const publicDir = path.resolve(__dirname, "../public");
const require = createRequire(import.meta.url);
const mysql = require("mysql2/promise");
const bcrypt = require("bcryptjs");
const proc = typeof process === "undefined" ? { env: {}, argv: [] } : process;

const loadLocalEnv = () => {
  const envPath = path.resolve(__dirname, "../.env");
  if (!fsSync.existsSync(envPath)) return;

  const lines = fsSync.readFileSync(envPath, "utf8").split(/\r?\n/);
  for (const line of lines) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith("#") || !trimmed.includes("=")) continue;
    const [key, ...valueParts] = trimmed.split("=");
    if (!proc.env[key]) {
      proc.env[key] = valueParts.join("=");
    }
  }
};

loadLocalEnv();

const json = (res, status, body) => {
  res.writeHead(status, {
    "Content-Type": "application/json",
    "Cache-Control": "no-store"
  });
  res.end(JSON.stringify(body));
};

const text = (res, status, body, type = "text/plain") => {
  res.writeHead(status, { "Content-Type": type });
  res.end(body);
};

const parseBody = (req) =>
  new Promise((resolve, reject) => {
    let body = "";
    req.on("data", (chunk) => {
      body += chunk;
    });
    req.on("end", () => {
      if (!body) {
        resolve({});
        return;
      }

      try {
        resolve(JSON.parse(body));
      } catch (error) {
        reject(error);
      }
    });
    req.on("error", reject);
  });

const createPool = () =>
  mysql.createPool({
    host: proc.env.DB_HOST || "127.0.0.1",
    port: Number(proc.env.DB_PORT || 3306),
    user: proc.env.DB_USER || "root",
    password: proc.env.DB_PASSWORD || "",
    database: proc.env.DB_NAME || "e_notary",
    waitForConnections: true,
    connectionLimit: 10
  });

const tokenStore = new Map();

const audit = async (db, userId, action, description) => {
  if (!userId) {
    return;
  }

  await db.execute(
    `INSERT INTO audit_logs (user_id, action, description, ip_address)
     VALUES (?, ?, ?, ?)`,
    [userId, action, description, "127.0.0.1"]
  );
};

const makeTemporaryPassword = () => {
  const raw = crypto.randomBytes(4).toString("hex");
  return `Nesos-${raw}`;
};

const makeUsernameFromApplication = async (db, application) => {
  if (application.applicant_email) {
    return application.applicant_email.trim().toLowerCase();
  }

  const base = application.name
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, ".")
    .replace(/^\.+|\.+$/g, "")
    .slice(0, 30) || "notary.owner";
  let candidate = `${base}@notary.so`;
  let counter = 1;

  while (true) {
    const [rows] = await db.execute(`SELECT id FROM users WHERE email = ? LIMIT 1`, [candidate]);
    if (!rows.length) {
      return candidate;
    }
    counter += 1;
    candidate = `${base}${counter}@notary.so`;
  }
};

const getAuthenticatedUser = async (req, db) => {
  const header = req.headers.authorization || "";
  if (!header.startsWith("Bearer ")) {
    return null;
  }

  const token = header.slice(7);
  const userId = tokenStore.get(token);
  if (!userId) {
    return null;
  }

  const [rows] = await db.execute(
    `SELECT u.id, u.name, u.email, u.role, u.notary_id,
            u.password_must_change,
            n.name AS notary_name, n.region, n.district, n.category, n.status AS notary_status,
            n.issued_date, n.expired_date
     FROM users u
     LEFT JOIN notaries n ON n.id = u.notary_id
     WHERE u.id = ? AND u.is_active = 1
     LIMIT 1`,
    [userId]
  );

  return rows[0] || null;
};

const ensureRole = (user, allowed) => user && allowed.includes(user.role);

const serveStaticFile = async (res, filePath, type) => {
  try {
    const file = await fs.readFile(filePath);
    text(res, 200, file, type);
  } catch {
    text(res, 404, "Not found");
  }
};

const fetchDashboardSummary = async (db) => {
  const [[revenueRow]] = await db.execute(
    `SELECT COALESCE(SUM(total), 0) AS totalRevenue FROM payments`
  );
  const [[taxRow]] = await db.execute(
    `SELECT COALESCE(SUM(tax_amount), 0) AS totalTax FROM transactions`
  );
  const [[todayRow]] = await db.execute(
    `SELECT COUNT(*) AS transactionsToday FROM transactions WHERE DATE(created_at) = CURDATE()`
  );
  const [serviceRows] = await db.execute(
    `SELECT type, COUNT(*) AS total FROM transactions GROUP BY type ORDER BY total DESC`
  );

  return {
    totalRevenue: Number(revenueRow.totalRevenue),
    totalTax: Number(taxRow.totalTax),
    transactionsToday: Number(todayRow.transactionsToday),
    byService: serviceRows.reduce((acc, row) => {
      acc[row.type] = Number(row.total);
      return acc;
    }, {})
  };
};

const routes = async (req, res, db) => {
  const url = new URL(req.url, "http://127.0.0.1:4000");
  const user = await getAuthenticatedUser(req, db);

  // Strip /NESOS prefix for routing logic if present
  const BASE = "/NESOS";
  let pathname = url.pathname;
  if (pathname.startsWith(BASE)) {
    pathname = pathname.slice(BASE.length) || "/";
  }

  if (
    user?.password_must_change &&
    pathname !== "/api/auth/change-password" &&
    !pathname.startsWith("/api/auth/login")
  ) {
    return json(res, 403, {
      message: "Password change required before using the system"
    });
  }

  if (req.method === "GET" && pathname === "/api/health") {
    return json(res, 200, { status: "ok" });
  }

  if (req.method === "POST" && pathname === "/api/auth/login") {
    const body = await parseBody(req);
    const [rows] = await db.execute(
     `SELECT u.id, u.name, u.email, u.password_hash, u.role, u.notary_id,
              u.password_must_change,
              n.name AS notary_name, n.region, n.district, n.category, n.status AS notary_status,
              n.issued_date, n.expired_date
       FROM users u
       LEFT JOIN notaries n ON n.id = u.notary_id
       WHERE u.email = ? AND u.is_active = 1
       LIMIT 1`,
      [body.email || ""]
    );
    const foundUser = rows[0];

    if (!foundUser) {
      return json(res, 401, { message: "Invalid credentials" });
    }

    const matched = await bcrypt.compare(body.password || "", foundUser.password_hash);
    if (!matched) {
      return json(res, 401, { message: "Invalid credentials" });
    }

    const token = crypto.randomUUID();
    tokenStore.set(token, foundUser.id);
    await audit(db, foundUser.id, "login", `${foundUser.email} logged in`);

    return json(res, 200, {
      token,
      user: {
        id: foundUser.id,
        name: foundUser.name,
        email: foundUser.email,
        role: foundUser.role,
        password_must_change: Boolean(foundUser.password_must_change),
        notary: foundUser.notary_id
          ? {
              id: foundUser.notary_id,
              name: foundUser.notary_name,
              region: foundUser.region,
              district: foundUser.district,
              category: foundUser.category,
              status: foundUser.notary_status,
              issued_date: foundUser.issued_date,
              expired_date: foundUser.expired_date
            }
          : null
      }
    });
  }

  if (req.method === "POST" && pathname === "/api/applications") {
    const body = await parseBody(req);
    const [result] = await db.execute(
      `INSERT INTO notary_applications
       (name, region, district, category, applicant_name, applicant_email, applicant_phone, license_file, registration_type, issued_date, expired_date, status)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.name,
        body.region,
        body.district,
        body.category,
        body.applicant_name || null,
        body.applicant_email || null,
        body.applicant_phone || null,
        body.license_file || "uploads/license.pdf",
        body.registration_type || "new",
        body.issued_date || null,
        body.expired_date || null,
        applicationStatus.PENDING
      ]
    );

    return json(res, 201, {
      id: result.insertId,
      name: body.name,
      region: body.region,
      district: body.district,
      category: body.category,
      applicant_name: body.applicant_name || null,
      applicant_email: body.applicant_email || null,
      applicant_phone: body.applicant_phone || null,
      license_file: body.license_file || "uploads/license.pdf",
      registration_type: body.registration_type || "new",
      issued_date: body.issued_date || null,
      expired_date: body.expired_date || null,
      status: applicationStatus.PENDING
    });
  }

  if (req.method === "GET" && pathname === "/api/applications") {
    if (!ensureRole(user, [roles.SUPER_ADMIN, roles.MINISTRY_JUSTICE])) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT id, name, region, district, category, applicant_name, applicant_email, applicant_phone,
              license_file, issued_username, status, created_at
       FROM notary_applications
       ORDER BY created_at DESC`
    );
    return json(res, 200, rows);
  }

  if (req.method === "PATCH" && /^\/api\/applications\/\d+\/review$/.test(pathname)) {
    if (!ensureRole(user, [roles.SUPER_ADMIN, roles.MINISTRY_JUSTICE])) {
      return json(res, 403, { message: "Access denied" });
    }

    const applicationId = Number(pathname.split("/")[3]);
    const body = await parseBody(req);
    const [rows] = await db.execute(
      `SELECT * FROM notary_applications WHERE id = ? LIMIT 1`,
      [applicationId]
    );
    const application = rows[0];

    if (!application) {
      return json(res, 404, { message: "Application not found" });
    }

    let credentials = null;

    if (body.status === applicationStatus.ACTIVE) {
      const [existingOwnerRows] = await db.execute(
        `SELECT u.id, u.email
         FROM users u
         WHERE u.role = 'owner' AND (
           u.email = ? OR u.notary_id IN (SELECT id FROM notaries WHERE name = ?)
         )
         LIMIT 1`,
        [application.applicant_email || "", application.name]
      );

      const [existingNotary] = await db.execute(
        `SELECT id FROM notaries WHERE name = ? LIMIT 1`,
        [application.name]
      );

      let notaryId = existingNotary[0]?.id || null;
      if (!notaryId) {
        const [notaryInsert] = await db.execute(
          `INSERT INTO notaries (name, region, district, category, registration_type, issued_date, expired_date, status)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            application.name,
            application.region,
            application.district,
            application.category,
            application.registration_type || "new",
            application.issued_date || null,
            application.expired_date || null,
            applicationStatus.ACTIVE
          ]
        );
        notaryId = notaryInsert.insertId;
      } else {
        await db.execute(
          `UPDATE notaries
           SET status = ?, registration_type = COALESCE(?, registration_type),
               issued_date = COALESCE(?, issued_date), expired_date = COALESCE(?, expired_date)
           WHERE id = ?`,
          [
            applicationStatus.ACTIVE,
            application.registration_type || null,
            application.issued_date || null,
            application.expired_date || null,
            notaryId
          ]
        );
      }

      if (!existingOwnerRows.length) {
        const username = await makeUsernameFromApplication(db, application);
        const tempPassword = makeTemporaryPassword();
        const passwordHash = await bcrypt.hash(tempPassword, 10);
        const ownerName =
          application.applicant_name || `${application.name} Owner`;
        const [ownerInsert] = await db.execute(
          `INSERT INTO users (notary_id, name, email, password_hash, role)
           VALUES (?, ?, ?, ?, 'owner')`,
          [notaryId, ownerName, username, passwordHash]
        );
        await db.execute(`UPDATE users SET password_must_change = 1 WHERE id = ?`, [
          ownerInsert.insertId
        ]);
        await db.execute(`UPDATE notaries SET owner_user_id = ? WHERE id = ?`, [
          ownerInsert.insertId,
          notaryId
        ]);
        credentials = {
          username,
          temporary_password: tempPassword,
          owner_name: ownerName
        };
        await db.execute(
          `UPDATE notary_applications SET issued_username = ? WHERE id = ?`,
          [username, applicationId]
        );
      } else {
        await db.execute(
          `UPDATE notary_applications SET issued_username = ? WHERE id = ?`,
          [existingOwnerRows[0].email, applicationId]
        );
      }
    }

    await db.execute(`UPDATE notary_applications SET status = ? WHERE id = ?`, [
      body.status,
      applicationId
    ]);

    await audit(
      db,
      user.id,
      "review_application",
      `${user.email} set application ${application.name} to ${body.status}`
    );

    return json(res, 200, {
      ...application,
      status: body.status,
      issued_username: credentials?.username || application.issued_username || application.applicant_email || null,
      credentials
    });
  }

  if (req.method === "GET" && pathname === "/api/transactions") {
    if (!user) {
      return json(res, 401, { message: "Authentication required" });
    }

    const privileged = [
      roles.SUPER_ADMIN,
      roles.MINISTRY_FINANCE,
      roles.MINISTRY_JUSTICE
    ].includes(user.role);

    const [rows] = await db.execute(
      `SELECT id, notary_id, type, asset_type, client_name, amount, tax_amount, total_amount,
              details, status, created_by, approved_by, created_at
       FROM transactions
       ${privileged ? "" : "WHERE notary_id = ?"}
       ORDER BY created_at DESC`,
      privileged ? [] : [user.notary_id]
    );

    return json(res, 200, rows);
  }

  if (req.method === "POST" && pathname === "/api/auth/change-password") {
    if (!user) {
      return json(res, 401, { message: "Authentication required" });
    }

    const body = await parseBody(req);
    if (!body.new_password || String(body.new_password).length < 8) {
      return json(res, 400, { message: "New password must be at least 8 characters" });
    }

    const passwordHash = await bcrypt.hash(String(body.new_password), 10);
    await db.execute(
      `UPDATE users SET password_hash = ?, password_must_change = 0 WHERE id = ?`,
      [passwordHash, user.id]
    );
    await audit(db, user.id, "change_password", `${user.email} changed password`);
    return json(res, 200, { message: "Password updated successfully" });
  }

  if (req.method === "GET" && pathname === "/api/notaries") {
    if (
      !ensureRole(user, [
        roles.SUPER_ADMIN,
        roles.MINISTRY_JUSTICE,
        roles.MINISTRY_FINANCE
      ])
    ) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT id, name, region, district, category, status, created_at
       FROM notaries
       ORDER BY name ASC`
    );
    return json(res, 200, rows);
  }

  if (req.method === "GET" && pathname === "/api/staff") {
    if (!ensureRole(user, [roles.OWNER])) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT id, staff_id_code, name, email, role, created_at
       FROM users
       WHERE notary_id = ? AND is_active = 1
       ORDER BY FIELD(role, 'owner', 'teller', 'payment', 'executive'), name`,
      [user.notary_id]
    );
    return json(res, 200, rows);
  }

  if (req.method === "POST" && pathname === "/api/staff") {
    if (!ensureRole(user, [roles.OWNER])) {
      return json(res, 403, { message: "Access denied" });
    }

    const body = await parseBody(req);
    const allowedStaffRoles = [roles.TELLER, roles.PAYMENT, roles.EXECUTIVE];

    if (!allowedStaffRoles.includes(body.role)) {
      return json(res, 400, { message: "Invalid staff role" });
    }

    if (!body.email || !body.name) {
      return json(res, 400, { message: "Name and email are required" });
    }

    const tempPassword = makeTemporaryPassword();
    const passwordHash = await bcrypt.hash(tempPassword, 10);
    const year = new Date().getFullYear();

    try {
      // 1. Create user first to get ID
      const [result] = await db.execute(
        `INSERT INTO users (notary_id, name, email, password_hash, role, password_must_change)
         VALUES (?, ?, ?, ?, ?, 1)`,
        [user.notary_id, body.name, body.email.toLowerCase().trim(), passwordHash, body.role]
      );

      const userId = result.insertId;
      const staffIdCode = `NESOS-${year}-${String(userId).padStart(4, "0")}`;

      // 2. Update with generated ID code
      await db.execute(`UPDATE users SET staff_id_code = ? WHERE id = ?`, [staffIdCode, userId]);

      await audit(db, user.id, "create_staff", `Created staff ${body.email} with ID ${staffIdCode}`);

      return json(res, 201, {
        id: userId,
        staff_id_code: staffIdCode,
        name: body.name,
        email: body.email,
        role: body.role,
        credentials: {
          username: body.email.toLowerCase().trim(),
          temporary_password: tempPassword,
          owner_name: body.name,
          staff_id: staffIdCode
        }
      });
    } catch (error) {
      if (error.code === "ER_DUP_ENTRY") {
        return json(res, 400, { message: "Email already exists" });
      }
      throw error;
    }
  }

  if (req.method === "PUT" && /^\/api\/staff\/\d+$/.test(pathname)) {
    if (!ensureRole(user, [roles.OWNER])) {
      return json(res, 403, { message: "Access denied" });
    }

    const staffId = Number(pathname.split("/")[3]);
    const body = await parseBody(req);
    const allowedStaffRoles = [roles.TELLER, roles.PAYMENT, roles.EXECUTIVE];

    if (body.role && !allowedStaffRoles.includes(body.role)) {
      return json(res, 400, { message: "Invalid staff role" });
    }

    const [rows] = await db.execute(
      `SELECT id FROM users WHERE id = ? AND notary_id = ? AND role != 'owner' LIMIT 1`,
      [staffId, user.notary_id]
    );

    if (!rows.length) {
      return json(res, 404, { message: "Staff member not found" });
    }

    await db.execute(
      `UPDATE users SET name = COALESCE(?, name), email = COALESCE(?, email), role = COALESCE(?, role) WHERE id = ?`,
      [body.name || null, body.email?.toLowerCase().trim() || null, body.role || null, staffId]
    );

    await audit(db, user.id, "edit_staff", `Updated staff member #${staffId}`);
    return json(res, 200, { message: "Staff member updated" });
  }

  if (req.method === "DELETE" && /^\/api\/staff\/\d+$/.test(pathname)) {
    if (!ensureRole(user, [roles.OWNER])) {
      return json(res, 403, { message: "Access denied" });
    }

    const staffId = Number(pathname.split("/")[3]);
    const [rows] = await db.execute(
      `SELECT id FROM users WHERE id = ? AND notary_id = ? AND role != 'owner' LIMIT 1`,
      [staffId, user.notary_id]
    );

    if (!rows.length) {
      return json(res, 404, { message: "Staff member not found" });
    }

    await db.execute(`UPDATE users SET is_active = 0 WHERE id = ?`, [staffId]);
    await audit(db, user.id, "delete_staff", `Deactivated staff member #${staffId}`);
    return json(res, 200, { message: "Staff member removed" });
  }

  if (req.method === "GET" && /^\/api\/public\/verify-staff\/[\w-]+$/.test(pathname)) {
    const code = pathname.split("/")[4];
    const [rows] = await db.execute(
      `SELECT u.name, u.role, u.is_active, n.name as notary_name, n.region, n.district
       FROM users u
       JOIN notaries n ON u.notary_id = n.id
       WHERE u.staff_id_code = ? AND u.is_active = 1 LIMIT 1`,
      [code]
    );

    if (!rows.length) {
      return json(res, 404, { message: "Invalid or inactive staff ID" });
    }

    return json(res, 200, rows[0]);
  }

  if (req.method === "GET" && /^\/api\/public\/verify-notary\/\d+$/.test(pathname)) {
    const notaryId = Number(pathname.split("/")[4]);
    const [rows] = await db.execute(
      `SELECT id, name, region, district, category, status, issued_date, expired_date
       FROM notaries
       WHERE id = ?
       LIMIT 1`,
      [notaryId]
    );

    if (!rows.length) {
      return json(res, 404, { message: "Xafiiskan Nootaayada lama aqoonsan." });
    }

    return json(res, 200, rows[0]);
  }

  if (req.method === "GET" && pathname === "/api/public/notaries") {
    const [rows] = await db.execute(
      `SELECT id, name, region, district, category, status
       FROM notaries
       WHERE status = 'active'
       ORDER BY name ASC`
    );
    return json(res, 200, rows);
  }

  if (req.method === "GET" && pathname === "/api/settings") {
    if (!user) {
      return json(res, 401, { message: "Authentication required" });
    }

    if (user.notary_id) {
      const [rows] = await db.execute(
        `SELECT n.id, n.name, n.region, n.district, n.category, n.status,
                u.name AS owner_name, u.email AS owner_email
         FROM notaries n
         LEFT JOIN users u ON u.id = (
           SELECT id FROM users WHERE role = 'owner' AND notary_id = n.id LIMIT 1
         )
         WHERE n.id = ?
         LIMIT 1`,
        [user.notary_id]
      );
      return json(res, 200, rows[0] || {});
    }

    return json(res, 200, {
      office_name: "Government Office",
      owner_name: user.name,
      owner_email: user.email,
      role: user.role
    });
  }

  if (req.method === "POST" && pathname === "/api/transactions") {
    if (!ensureRole(user, [roles.OWNER, roles.TELLER])) {
      return json(res, 403, { message: "Access denied" });
    }

    const body = await parseBody(req);
    const amount = Number(body.amount || 0);
    const tax = calculateTax(body.type, body.asset_type, amount);
    const total = Number((amount + tax).toFixed(2));

    const [result] = await db.execute(
      `INSERT INTO transactions
       (notary_id, type, asset_type, client_name, amount, tax_amount, total_amount, details, status, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        user.notary_id,
        body.type,
        body.asset_type,
        body.client_name,
        amount,
        tax,
        total,
        JSON.stringify(body.details || {}),
        transactionStatus.PENDING,
        user.id
      ]
    );

    await audit(
      db,
      user.id,
      "create_transaction",
      `${user.email} created transaction #${result.insertId}`
    );

    return json(res, 201, {
      id: result.insertId,
      notary_id: user.notary_id,
      type: body.type,
      asset_type: body.asset_type,
      client_name: body.client_name,
      amount,
      tax_amount: tax,
      total_amount: total,
      details: body.details || {},
      status: transactionStatus.PENDING
    });
  }

  if (req.method === "PATCH" && /^\/api\/transactions\/\d+\/pay$/.test(pathname)) {
    if (!ensureRole(user, [roles.PAYMENT])) {
      return json(res, 403, { message: "Access denied" });
    }

    const transactionId = Number(pathname.split("/")[3]);
    const body = await parseBody(req);
    const [rows] = await db.execute(
      `SELECT * FROM transactions WHERE id = ? LIMIT 1`,
      [transactionId]
    );
    const transaction = rows[0];

    if (!transaction) {
      return json(res, 404, { message: "Transaction not found" });
    }

    await db.execute(`UPDATE transactions SET status = ? WHERE id = ?`, [
      transactionStatus.PAID,
      transactionId
    ]);
    const [paymentResult] = await db.execute(
      `INSERT INTO payments (transaction_id, total, reference_number, phone)
       VALUES (?, ?, ?, ?)`,
      [
        transactionId,
        transaction.total_amount,
        body.reference_number || `REF-${transactionId}`,
        body.phone || ""
      ]
    );

    await audit(
      db,
      user.id,
      "record_payment",
      `${user.email} marked transaction #${transactionId} as paid`
    );

    return json(res, 200, {
      payment: {
        id: paymentResult.insertId,
        transaction_id: transactionId
      },
      transaction: {
        ...transaction,
        status: transactionStatus.PAID
      }
    });
  }

  if (req.method === "PATCH" && /^\/api\/transactions\/\d+\/decision$/.test(pathname)) {
    if (!ensureRole(user, [roles.EXECUTIVE])) {
      return json(res, 403, { message: "Access denied" });
    }

    const transactionId = Number(pathname.split("/")[3]);
    const body = await parseBody(req);
    const [rows] = await db.execute(
      `SELECT * FROM transactions WHERE id = ? LIMIT 1`,
      [transactionId]
    );
    const transaction = rows[0];

    if (!transaction) {
      return json(res, 404, { message: "Transaction not found" });
    }

    const nextStatus =
      body.status === transactionStatus.RETURNED
        ? transactionStatus.RETURNED
        : transactionStatus.COMPLETED;

    await db.execute(
      `UPDATE transactions SET status = ?, approved_by = ? WHERE id = ?`,
      [nextStatus, user.id, transactionId]
    );

    await audit(
      db,
      user.id,
      "decision_transaction",
      `${user.email} set transaction #${transactionId} to ${nextStatus}`
    );

    return json(res, 200, {
      ...transaction,
      status: nextStatus,
      approved_by: user.id
    });
  }

  if (req.method === "GET" && pathname === "/api/reports/dashboard") {
    if (!user) {
      return json(res, 401, { message: "Authentication required" });
    }
    return json(res, 200, await fetchDashboardSummary(db));
  }

  if (req.method === "GET" && pathname === "/api/reports/finance") {
    if (!ensureRole(user, [roles.SUPER_ADMIN, roles.MINISTRY_FINANCE])) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT 
        t.id AS transaction_id,
        t.type AS service_name,
        t.asset_type,
        n.name AS notary,
        n.region,
        n.district,
        t.client_name AS customer,
        p.paid_at AS payment_date,
        p.reference_number,
        p.phone,
        t.amount AS commodity_value,
        t.tax_amount AS mof_tax,
        (p.total - t.tax_amount) AS notary_fee,
        p.total AS total_payment
       FROM transactions t
       JOIN payments p ON p.transaction_id = t.id
       JOIN notaries n ON n.id = t.notary_id
       WHERE t.status = 'completed'
       ORDER BY p.paid_at DESC`
    );
    return json(res, 200, rows);
  }

  if (req.method === "GET" && pathname === "/api/tax-rules") {
    if (
      !ensureRole(user, [
        roles.SUPER_ADMIN,
        roles.MINISTRY_FINANCE,
        roles.MINISTRY_JUSTICE,
        roles.OWNER
      ])
    ) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT id, service_type, asset_type, min_amount, max_amount, tax_type, tax_value
       FROM tax_rules
       WHERE is_active = 1
       ORDER BY service_type, asset_type, min_amount`
    );
    return json(res, 200, rows);
  }

  if (req.method === "GET" && pathname === "/api/audit-logs") {
    if (
      !ensureRole(user, [
        roles.SUPER_ADMIN,
        roles.MINISTRY_FINANCE,
        roles.MINISTRY_JUSTICE,
        roles.OWNER
      ])
    ) {
      return json(res, 403, { message: "Access denied" });
    }

    const [rows] = await db.execute(
      `SELECT a.id, a.action, a.description, a.created_at, u.email
       FROM audit_logs a
       JOIN users u ON u.id = a.user_id
       ORDER BY a.created_at DESC
       LIMIT 100`
    );
    return json(res, 200, rows);
  }

  if (req.method === "GET") {
    const filePath = path.join(publicDir, pathname === "/" ? "index.html" : pathname);
    const ext = path.extname(filePath).toLowerCase();
    const mimeTypes = {
      ".html": "text/html",
      ".js": "text/javascript",
      ".css": "text/css",
      ".json": "application/json",
      ".png": "image/png",
      ".jpg": "image/jpeg",
      ".gif": "image/gif",
      ".svg": "image/svg+xml"
    };

    const type = mimeTypes[ext] || "application/octet-stream";
    const exists = await fs
      .access(filePath)
      .then(() => true)
      .catch(() => false);

    if (exists) {
      return serveStaticFile(res, filePath, type);
    }
  }

  // Fallback to index.html for SPA routing
  if (req.method === "GET" && !pathname.startsWith("/api/")) {
    return serveStaticFile(res, path.join(publicDir, "index.html"), "text/html");
  }

  return json(res, 404, { message: "Not found" });
};

export const startCustomServer = async ({ port = Number(process.env.PORT || 4000), host = "127.0.0.1" } = {}) => {
  const db = createPool();
  const server = http.createServer(async (req, res) => {
    try {
      await routes(req, res, db);
    } catch (error) {
      json(res, 500, { message: error.message || "Server error" });
    }
  });

  await new Promise((resolve) => {
    server.listen(port, host, resolve);
  });

  return server;
};

if (proc.argv[1] === __filename) {
  const port = Number(process.env.PORT || 4000);
  startCustomServer({ port }).then(() => {
    console.log(`Custom E-Notary server listening on http://127.0.0.1:${port}`);
  });
}
