import { Router } from 'express';
import { z } from 'zod';
import { pool, query, withTransaction } from '../db.js';
import { requireAuth, requireNoForcedPasswordChange, requireRoles } from '../middleware/auth.js';
import type { AuthedRequest } from '../types.js';
import { created, fail, ok } from '../utils/http.js';
import { recordActivity, recordSyncEvent } from '../services/audit.js';

const router = Router();

const listQuery = z.object({
  q: z.string().optional(),
  status: z.string().optional(),
  from: z.string().optional(),
  to: z.string().optional(),
  customerId: z.string().uuid().optional(),
  supplierId: z.string().uuid().optional(),
  engineerId: z.string().uuid().optional(),
  limit: z.coerce.number().int().min(1).max(200).default(50),
  offset: z.coerce.number().int().min(0).default(0)
});

function filters(input: z.infer<typeof listQuery>, alias: string, searchable: string[] = []) {
  const params: unknown[] = [];
  const where: string[] = [];
  if (input.q && searchable.length) {
    params.push(`%${input.q.toLowerCase()}%`);
    where.push(`(${searchable.map(c => `lower(coalesce(${alias}.${c}::text,'')) LIKE $${params.length}`).join(' OR ')})`);
  }
  if (input.status) { params.push(input.status); where.push(`${alias}.status=$${params.length}`); }
  return { where: where.length ? `WHERE ${where.join(' AND ')}` : '', params };
}

router.get('/roles', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin'), async (_req, res) => {
  const { rows } = await query('SELECT code,label,description,is_system FROM roles ORDER BY code');
  return ok(res, rows);
});

router.get('/permissions', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin'), async (_req, res) => {
  const { rows } = await query(`SELECT permission_code, role_code FROM role_permissions ORDER BY role_code, permission_code`);
  return ok(res, rows);
});

router.get('/products', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const f = filters(p.data, 'p', ['sku','name','category_name','item_type']);
  const { rows } = await query(`SELECT * FROM products p ${f.where} ORDER BY p.category_name NULLS LAST, p.name LIMIT $${f.params.length+1} OFFSET $${f.params.length+2}`,[...f.params,p.data.limit,p.data.offset]);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

router.get('/parts', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const f = filters(p.data, 'p', ['sku','name','category_name']);
  const where = f.where ? `${f.where} AND lower(coalesce(p.item_type,'')) LIKE '%part%'` : `WHERE lower(coalesce(p.item_type,'')) LIKE '%part%'`;
  const { rows } = await query(`SELECT * FROM products p ${where} ORDER BY p.current_stock ASC, p.name LIMIT $${f.params.length+1} OFFSET $${f.params.length+2}`,[...f.params,p.data.limit,p.data.offset]);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

router.get('/machines', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const f = filters(p.data, 'm', ['brand','model','serial_number']);
  const { rows } = await query(`SELECT m.*,c.name customer_name,c.phone customer_phone FROM machines m LEFT JOIN customers c ON c.id=m.customer_id ${f.where} ORDER BY m.created_at DESC LIMIT $${f.params.length+1} OFFSET $${f.params.length+2}`,[...f.params,p.data.limit,p.data.offset]);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

router.get('/invoices', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const params: unknown[] = [];
  const where: string[] = [];
  if (p.data.customerId) { params.push(p.data.customerId); where.push(`i.customer_id=$${params.length}`); }
  if (p.data.status) { params.push(p.data.status); where.push(`i.status=$${params.length}`); }
  if (p.data.from) { params.push(p.data.from); where.push(`i.invoice_date >= $${params.length}::date`); }
  if (p.data.to) { params.push(p.data.to); where.push(`i.invoice_date <= $${params.length}::date`); }
  params.push(p.data.limit, p.data.offset);
  const { rows } = await query(`SELECT i.*,c.name customer_name,c.phone customer_phone FROM invoices i LEFT JOIN customers c ON c.id=i.customer_id ${where.length?'WHERE '+where.join(' AND '):''} ORDER BY i.invoice_date DESC NULLS LAST, i.created_at DESC LIMIT $${params.length-1} OFFSET $${params.length}`, params);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

const paymentSchema = z.object({
  customerId: z.string().uuid().optional(),
  supplierId: z.string().uuid().optional(),
  invoiceId: z.string().uuid().optional(),
  accountId: z.string().uuid(),
  amount: z.number().positive(),
  paymentDate: z.string().min(10),
  paymentMethod: z.string().min(2),
  reference: z.string().min(1),
  note: z.string().optional()
});

router.post('/payments', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','accounts','manager'), async (req: AuthedRequest, res) => {
  const p = paymentSchema.safeParse(req.body); if (!p.success || !req.auth) return fail(res,400,'VALIDATION_ERROR','Invalid payment data.',p.success?undefined:p.error.flatten());
  const d = p.data;
  const row = await withTransaction(async c => {
    const entryType = d.supplierId ? 'supplier_payment' : 'customer_payment';
    const debit = d.supplierId ? d.amount : 0;
    const credit = d.customerId ? d.amount : 0;
    const { rows } = await c.query(`INSERT INTO payments (customer_id,supplier_id,invoice_id,account_id,amount,payment_date,payment_method,reference,note,created_by) VALUES ($1,$2,$3,$4,$5,$6::date,$7,$8,$9,$10) RETURNING *`,[d.customerId??null,d.supplierId??null,d.invoiceId??null,d.accountId,d.amount,d.paymentDate,d.paymentMethod,d.reference,d.note??null,req.auth!.id]);
    await c.query(`INSERT INTO cash_bank_transactions (account_id,transaction_date,transaction_type,amount,reference,note,created_by) VALUES ($1,$2::date,$3,$4,$5,$6,$7)`,[d.accountId,d.paymentDate,d.customerId?'in':'out',d.amount,d.reference,d.note??null,req.auth!.id]);
    await c.query(`INSERT INTO journal_entries (entry_date,entry_type,party_type,customer_id,supplier_id,account_id,amount,payment_method,reference,description,status) VALUES ($1::date,$2,$3,$4,$5,$6,$7,$8,$9,$10,'posted')`,[d.paymentDate,entryType,d.customerId?'Customer':'Supplier',d.customerId??null,d.supplierId??null,d.accountId,d.amount,d.paymentMethod,d.reference,d.note??'Payment posted']);
    await recordActivity(c,{actorUserId:req.auth!.id,eventType:'payment.received',entityType:'payment',entityId:rows[0].id,message:`Payment posted ${d.reference}`});
    await recordSyncEvent(c,{eventType:'payment.received',entityType:'payment',entityId:rows[0].id,actorUserId:req.auth!.id,payload:{amount:d.amount,reference:d.reference}});
    return rows[0];
  });
  return created(res,row);
});

router.get('/payments', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','accounts','manager','sales'), async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const params: unknown[] = [p.data.limit,p.data.offset];
  const { rows } = await query(`SELECT p.*,c.name customer_name,s.name supplier_name,a.name account_name FROM payments p LEFT JOIN customers c ON c.id=p.customer_id LEFT JOIN suppliers s ON s.id=p.supplier_id LEFT JOIN cash_bank_accounts a ON a.id=p.account_id ORDER BY p.payment_date DESC, p.created_at DESC LIMIT $1 OFFSET $2`,params);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

router.get('/agreements', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const p = listQuery.safeParse(req.query); if (!p.success) return fail(res,400,'VALIDATION_ERROR','Invalid filter.',p.error.flatten());
  const { rows } = await query(`SELECT a.*,c.name customer_name,m.model machine_model,m.serial_number FROM agreements a LEFT JOIN customers c ON c.id=a.customer_id LEFT JOIN machines m ON m.id=a.machine_id ORDER BY a.agreement_date DESC NULLS LAST, a.created_at DESC LIMIT $1 OFFSET $2`,[p.data.limit,p.data.offset]);
  return ok(res, rows, { limit:p.data.limit, offset:p.data.offset });
});

router.get('/ledgers/daily', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts'), async (_req, res) => {
  const { rows } = await query(`SELECT entry_date date, reference, entry_type type, description particulars, CASE WHEN entry_type ILIKE '%expense%' OR entry_type ILIKE '%supplier%' THEN amount ELSE 0 END debit, CASE WHEN entry_type ILIKE '%payment%' OR entry_type ILIKE '%income%' THEN amount ELSE 0 END credit, status FROM journal_entries ORDER BY entry_date DESC, created_at DESC LIMIT 500`);
  return ok(res, rows);
});
router.get('/ledgers/customer', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts','sales'), async (_req, res) => {
  const { rows } = await query(`SELECT je.entry_date date,je.reference,je.description,c.name customer_name,je.amount,je.status FROM journal_entries je JOIN customers c ON c.id=je.customer_id ORDER BY je.entry_date DESC LIMIT 500`);
  return ok(res, rows);
});
router.get('/ledgers/supplier', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts'), async (_req, res) => {
  const { rows } = await query(`SELECT je.entry_date date,je.reference,je.description,s.name supplier_name,je.amount,je.status FROM journal_entries je JOIN suppliers s ON s.id=je.supplier_id ORDER BY je.entry_date DESC LIMIT 500`);
  return ok(res, rows);
});

router.get('/cash-bank', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts'), async (_req, res) => ok(res,(await query('SELECT * FROM cash_bank_accounts WHERE is_active=true ORDER BY account_type,name')).rows));
router.get('/suppliers', requireAuth, requireNoForcedPasswordChange, async (_req, res) => ok(res,(await query('SELECT * FROM suppliers ORDER BY name')).rows));
router.get('/import-orders', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts','store'), async (_req, res) => ok(res,(await query(`SELECT io.*,s.name supplier_name FROM import_orders io LEFT JOIN suppliers s ON s.id=io.supplier_id ORDER BY io.created_at DESC`)).rows));
router.get('/lc-tt', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts'), async (_req, res) => ok(res,(await query(`SELECT lp.*,io.order_number,s.name supplier_name FROM lc_payments lp LEFT JOIN import_orders io ON io.id=lp.import_order_id LEFT JOIN suppliers s ON s.id=lp.supplier_id ORDER BY lp.due_date DESC NULLS LAST`)).rows));
router.get('/shipments', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts','store'), async (_req, res) => ok(res,(await query(`SELECT sh.*,io.order_number FROM shipments sh LEFT JOIN import_orders io ON io.id=sh.import_order_id ORDER BY sh.eta DESC NULLS LAST`)).rows));
router.get('/landed-cost', requireAuth, requireNoForcedPasswordChange, requireRoles('owner','admin','manager','accounts','store'), async (_req, res) => ok(res,(await query(`SELECT l.*,io.order_number,s.name supplier_name FROM landed_cost_items l LEFT JOIN import_orders io ON io.id=l.import_order_id LEFT JOIN suppliers s ON s.id=l.supplier_id ORDER BY l.created_at DESC`)).rows));

router.get('/reports/:reportName', requireAuth, requireNoForcedPasswordChange, async (req, res) => {
  const name = String(req.params.reportName);
  const map: Record<string,string> = {
    sales: `SELECT i.invoice_date date,c.name customer,i.invoice_number reference,i.total_amount,i.paid_amount,i.due_amount,i.status FROM invoices i LEFT JOIN customers c ON c.id=i.customer_id ORDER BY i.invoice_date DESC NULLS LAST LIMIT 1000`,
    collection: `SELECT payment_date date,reference,payment_method,amount FROM payments ORDER BY payment_date DESC LIMIT 1000`,
    inventory: `SELECT sku,name,category_name,current_stock,reorder_level,purchase_cost,(current_stock*purchase_cost) stock_value FROM products ORDER BY stock_value DESC NULLS LAST LIMIT 1000`,
    service: `SELECT st.ticket_number,st.status,st.priority,c.name customer,e.name engineer,st.planned_at,st.completed_at FROM service_tickets st LEFT JOIN customers c ON c.id=st.customer_id LEFT JOIN engineers e ON e.id=st.assigned_engineer_id ORDER BY st.created_at DESC LIMIT 1000`,
    import: `SELECT io.order_number,io.status,io.currency,io.total_value,s.name supplier_name FROM import_orders io LEFT JOIN suppliers s ON s.id=io.supplier_id ORDER BY io.created_at DESC LIMIT 1000`,
    landed_cost: `SELECT sku,product_model,quantity,unit_usd,exchange_rate,shipping_allocated,customs_duty,cnf_allocated,transport_allocated,bank_charge_allocated,profit_pct,status FROM landed_cost_items ORDER BY created_at DESC LIMIT 1000`
  };
  if (!map[name]) return fail(res,404,'REPORT_NOT_FOUND','Report is not configured.');
  const { rows } = await query(map[name]);
  return ok(res, rows, { report:name, generatedAt:new Date().toISOString() });
});

router.post('/imports/rollback-last', requireAuth, requireNoForcedPasswordChange, requireRoles('owner'), async (req: AuthedRequest, res) => {
  if (!req.auth) return fail(res,401,'UNAUTHORIZED','Authentication required.');
  const { rows } = await query(`SELECT id FROM import_batches WHERE status='completed' ORDER BY completed_at DESC LIMIT 1`);
  if (!rows[0]) return fail(res,404,'NO_COMPLETED_IMPORT','No completed import batch found.');
  const batchId = rows[0].id;
  await withTransaction(async c => {
    await c.query(`UPDATE import_batches SET status='rolled_back', error_message='Rollback marker created. Physical deletion is intentionally blocked; use audit report for manual correction.' WHERE id=$1`,[batchId]);
    await recordActivity(c,{actorUserId:req.auth!.id,eventType:'import.rollback_marked',entityType:'import_batch',entityId:batchId,message:'Last import batch marked for rollback review. No data was silently deleted.'});
  });
  return ok(res,{batchId,status:'rollback_review_required'});
});

export default router;