<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();
header('Content-Type: application/json');
$mysqli = db();
$action = $_GET['action'] ?? 'kpis';

function ymd($s){ return preg_match('/^\d{4}-\d{2}-\d{2}$/',$s) ? $s : null; }

function branch_where(&$types, &$params){
  $w = [];
  if (!has_role(['admin','super_admin'])){
    $bid = (int)($_SESSION['branch_id'] ?? 0);
    if ($bid>0){ $w[] = 'i.branch_id=?'; $types.='i'; $params[]=$bid; }
  } else if (!empty($_GET['branch_id'])){
    $w[]='i.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
  }
  return $w;
}

function table_has_column($table, $column){
  global $mysqli;
  $table = $mysqli->real_escape_string($table);
  $column = $mysqli->real_escape_string($column);
  $res = $mysqli->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
  return $res && $res->num_rows > 0;
}

function inventory_branch_where($alias, &$types, &$params){
  $w = [];
  if (!table_has_column($alias['table'], 'branch_id')) return $w;
  $field = $alias['alias'] . '.branch_id';
  if (!has_role(['admin','super_admin'])){
    $bid = (int)($_SESSION['branch_id'] ?? 0);
    if ($bid>0){ $w[]="$field=?"; $types.='i'; $params[]=$bid; }
  } else if (!empty($_GET['branch_id'])){
    $w[]="$field=?"; $types.='i'; $params[]=(int)$_GET['branch_id'];
  }
  return $w;
}

switch ($action) {
  case 'kpis': {
    $today = date('Y-m-d');
    $monthStart = date('Y-m-01');
    $monthEnd = date('Y-m-t');

    // Revenue today, month; outstanding balance; invoice count
    $types=''; $params=[]; $w = branch_where($types,$params);

    // Today revenue (Paid Amount)
    $sqlT = "SELECT COALESCE(SUM(COALESCE(p.paid,0)),0) v
             FROM invoices i
             LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id
             WHERE i.status IN ('issued','paid') AND i.invoice_date=?";
    $typesT='s'.$types; $paramsT=array_merge([$today],$params);
    if ($w) $sqlT .= ' AND '.implode(' AND ',$w);
    $st=$mysqli->prepare($sqlT); $st->bind_param($typesT, ...$paramsT); $st->execute(); $revToday=(float)($st->get_result()->fetch_row()[0]??0);

    // Month total sales (invoice value) and paid amount
    $sqlM = "SELECT COALESCE(SUM(i.grand_total),0) sales_value, COALESCE(SUM(COALESCE(p.paid,0)),0) paid_value
             FROM invoices i
             LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id
             WHERE i.status IN ('issued','paid') AND i.invoice_date BETWEEN ? AND ?";
    $typesM='ss'.$types; $paramsM=array_merge([$monthStart,$monthEnd],$params);
    if ($w) $sqlM .= ' AND '.implode(' AND ',$w);
    $sm=$mysqli->prepare($sqlM); $sm->bind_param($typesM, ...$paramsM); $sm->execute(); 
    $rowM = $sm->get_result()->fetch_row();
    $salesMonth=(float)($rowM[0]??0);
    $paidMonth=(float)($rowM[1]??0);

    // Outstanding
    $typesO=$types; $paramsO=$params;
    $dueDaysExpr = "CASE WHEN i.payment_term='Credit' THEN 30 ELSE 0 END";
    $dueDateExpr = "DATE_ADD(i.invoice_date, INTERVAL $dueDaysExpr DAY)";
    $sqlO = "SELECT SUM(GREATEST(i.grand_total-COALESCE(p.paid,0),0)) bal
             FROM invoices i LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id
             WHERE i.status IN ('issued','paid')";
    if ($w) $sqlO .= ' AND '.implode(' AND ',$w);
    $so=$mysqli->prepare($sqlO); if($typesO){ $so->bind_param($typesO, ...$paramsO);} $so->execute(); $outBal=(float)($so->get_result()->fetch_row()[0]??0);

    // Customers count (all active)
    $cust = $mysqli->query("SELECT COUNT(*) FROM customers")->fetch_row()[0] ?? 0;

    json_response(['data'=>[
      'revenue_today'=>$revToday,
      'sales_month'=>$salesMonth,
      'paid_month'=>$paidMonth,
      'outstanding'=>$outBal,
      'customers'=>(int)$cust,
    ]]);
    break; }

  case 'sales_trend': {
    // Last 6 months revenue
    $types=''; $params=[]; $w = branch_where($types,$params);
    $start = date('Y-m-01', strtotime('-5 months'));
    $end = date('Y-m-t');

    $where = ["i.status IN ('issued','paid')", 'i.invoice_date BETWEEN ? AND ?'];
    $types2 = 'ss' . $types;
    $params2 = array_merge([$start, $end], $params);
    if ($w) { $where = array_merge($where, $w); }

    $sql = "SELECT
              DATE_FORMAT(i.invoice_date,'%Y-%m') ym,
              ROUND(COALESCE(SUM(i.grand_total),0), 2) AS sales_value,
              ROUND(COALESCE(SUM(COALESCE(p.paid,0)),0), 2) AS paid_value
            FROM invoices i
            LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id
            WHERE " . implode(' AND ', $where) . "
            GROUP BY ym
            ORDER BY ym ASC";

    $st=$mysqli->prepare($sql);
    $st->bind_param($types2, ...$params2);
    $st->execute();
    $res=$st->get_result();

    $byYm = [];
    while($r=$res->fetch_assoc()){
      $byYm[$r['ym']] = [
        'sales_value'=>(float)($r['sales_value'] ?? 0),
        'paid_value'=>(float)($r['paid_value'] ?? 0)
      ];
    }

    $rows = [];
    for ($i=5; $i>=0; $i--) {
      $ym = date('Y-m', strtotime("-$i months"));
      $rows[] = [
        'ym'=>$ym,
        'sales_value'=>($byYm[$ym]['sales_value'] ?? 0),
        'paid_value'=>($byYm[$ym]['paid_value'] ?? 0),
      ];
    }

    json_response(['data'=>$rows]);
    break; }

  case 'top_finishes': {
    // Available final product distribution by color (percentage is calculated client-side)
    $types='';
    $params=[];
    $w=[];
    if (!has_role(['admin','super_admin'])){
      $bid = (int)($_SESSION['branch_id'] ?? 0);
      if ($bid>0){ $w[]='f.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (!empty($_GET['branch_id'])){
      $w[]='f.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    $sql = "SELECT COALESCE(c.name,'Unknown') name, ROUND(COALESCE(SUM(f.final_sq_ft),0), 3) total
            FROM final_products f
            LEFT JOIN colors c ON c.id=f.color_id
            WHERE f.sale_status='available'";
    if ($w) { $sql .= ' AND ' . implode(' AND ', $w); }
    $sql .= ' GROUP BY f.color_id, c.name ORDER BY total DESC LIMIT 10';

    $st=$mysqli->prepare($sql);
    if (!$st) json_response(['message'=>'SQL prepare failed (top_finishes): '.$mysqli->error], 500);
    if ($types) { $st->bind_param($types, ...$params); }
    $st->execute();
    $rows=[];
    $res=$st->get_result();
    while($r=$res->fetch_assoc()) $rows[]=$r;
    json_response(['data'=>$rows]);
    break; }

  case 'outstanding_age': {
    $types=''; $params=[]; $w = branch_where($types,$params);
    $sql = "SELECT 
              SUM(CASE WHEN DATEDIFF(CURDATE(), x.invoice_date) <= 7 THEN bal ELSE 0 END) AS d0_7,
              SUM(CASE WHEN DATEDIFF(CURDATE(), x.invoice_date) BETWEEN 8 AND 30 THEN bal ELSE 0 END) AS d8_30,
              SUM(CASE WHEN DATEDIFF(CURDATE(), x.invoice_date) BETWEEN 31 AND 60 THEN bal ELSE 0 END) AS d31_60,
              SUM(CASE WHEN DATEDIFF(CURDATE(), x.invoice_date) > 60 THEN bal ELSE 0 END) AS d60p
            FROM (
              SELECT i.*, GREATEST(i.grand_total-COALESCE(p.paid,0),0) bal
              FROM invoices i LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id
              WHERE i.status IN ('issued','paid')";
    if ($w) { $sql .= ' AND '.implode(' AND ', $w); }
    $sql .= ") x";
    $st=$mysqli->prepare($sql); if($types){ $st->bind_param($types, ...$params);} $st->execute();
    $row=$st->get_result()->fetch_assoc() ?: ['d0_7'=>0,'d8_30'=>0,'d31_60'=>0,'d60p'=>0];
    json_response(['data'=>$row]); break; }

  case 'raw_inventory': {
    // Summary for raw_material_products
    $rows = [
      'total' => 0,
      'received' => 0,
      'in_polishing' => 0,
      'polished' => 0,
      'finished' => 0,
      'value' => 0.0,
      'sq_m2' => 0.0,
      'sq_ft' => 0.0,
      'received_sq_m2' => 0.0,
      'received_sq_ft' => 0.0,
      'in_polishing_sq_m2' => 0.0,
      'in_polishing_sq_ft' => 0.0,
      'finished_sq_m2' => 0.0,
      'finished_sq_ft' => 0.0,
    ];
    $types=''; $params=[]; $w = inventory_branch_where(['table'=>'raw_material_products','alias'=>'r'], $types, $params);
    $sql = "SELECT COUNT(*) total, 
        SUM(status='received') received,
        SUM(status='in_polishing') in_polishing,
        SUM(status='polished') polished,
        SUM(status='finished') finished,
        COALESCE(SUM(value),0) value,
        COALESCE(SUM(sq_m2),0) sq_m2,
        COALESCE(SUM(CASE WHEN status='received' THEN sq_m2 ELSE 0 END),0) received_sq_m2,
        COALESCE(SUM(CASE WHEN status='in_polishing' THEN sq_m2 ELSE 0 END),0) in_polishing_sq_m2,
        COALESCE(SUM(CASE WHEN status='finished' THEN sq_m2 ELSE 0 END),0) finished_sq_m2
      FROM raw_material_products r";
    if ($w) $sql .= ' WHERE '.implode(' AND ', $w);
    $st = $mysqli->prepare($sql);
    if ($types) $st->bind_param($types, ...$params);
    $st->execute();
    $res = $st->get_result();
    if ($res) {
      $r=$res->fetch_assoc();
      if($r){
        $rows['total']=(int)$r['total'];
        $rows['received']=(int)$r['received'];
        $rows['in_polishing']=(int)$r['in_polishing'];
        $rows['polished']=(int)$r['polished'];
        $rows['finished']=(int)$r['finished'];
        $rows['value']=(float)$r['value'];
        $rows['sq_m2']=(float)$r['sq_m2'];
        $rows['sq_ft']=$rows['sq_m2']*10.76391041671;

        $rows['received_sq_m2']=(float)$r['received_sq_m2'];
        $rows['received_sq_ft']=$rows['received_sq_m2']*10.76391041671;
        $rows['in_polishing_sq_m2']=(float)$r['in_polishing_sq_m2'];
        $rows['in_polishing_sq_ft']=$rows['in_polishing_sq_m2']*10.76391041671;
        $rows['finished_sq_m2']=(float)$r['finished_sq_m2'];
        $rows['finished_sq_ft']=$rows['finished_sq_m2']*10.76391041671;
      }
    }
    json_response(['data'=>$rows]); break; }

  case 'final_inventory': {
    // Summary for final_products
    $types=''; $params=[]; $w=[];
    if (!has_role(['admin','super_admin'])){ $bid=(int)($_SESSION['branch_id'] ?? 0); if($bid>0){ $w[]='branch_id=?'; $types.='i'; $params[]=$bid; } }
    $sql = 'SELECT COUNT(*) total, SUM(sale_status=\'available\') available, SUM(sale_status=\'reserved\') reserved, SUM(sale_status=\'sold\') sold FROM final_products';
    if ($w) $sql .= ' WHERE '.implode(' AND ', $w);
    $st=$mysqli->prepare($sql); if($types){ $st->bind_param($types, ...$params);} $st->execute();
    $r=$st->get_result()->fetch_assoc() ?: ['total'=>0,'available'=>0,'reserved'=>0,'sold'=>0];
    json_response(['data'=>['total'=>(int)$r['total'],'available'=>(int)$r['available'],'reserved'=>(int)$r['reserved'],'sold'=>(int)$r['sold']]]); break; }

  case 'cut_inventory': {
    // Summary for cut_pieces
    $types=''; $params=[]; $w = inventory_branch_where(['table'=>'cut_pieces','alias'=>'c'], $types, $params);
    $sql = "SELECT COUNT(*) total, SUM(is_available=1) available, SUM(status='usable') usable, SUM(status='unusable') unusable, COALESCE(SUM(area_sq_ft),0) area_sq_ft FROM cut_pieces c";
    if ($w) $sql .= ' WHERE '.implode(' AND ', $w);
    $st=$mysqli->prepare($sql); if($types){ $st->bind_param($types, ...$params);} $st->execute();
    $r=$st->get_result()->fetch_assoc(); if(!$r){ $r=['total'=>0,'available'=>0,'usable'=>0,'unusable'=>0,'area_sq_ft'=>0]; }
    json_response(['data'=>['total'=>(int)$r['total'],'available'=>(int)$r['available'],'usable'=>(int)$r['usable'],'unusable'=>(int)$r['unusable'],'area_sq_ft'=>(float)$r['area_sq_ft']]]); break; }

  case 'production_inventory': {
    // Summary for production_items (basic counts)
    $res = $mysqli->query("SELECT COUNT(*) total FROM production_items");
    $total = $res ? (int)($res->fetch_row()[0]??0) : 0;
    json_response(['data'=>['total'=>$total]]); break; }

  case 'recent_expenditures': {
    // Current month expenditures (latest 5)
    $first = date('Y-m-01');
    $today = date('Y-m-d');

    $types='ss';
    $params=[$first,$today];
    $w=[];

    // Branch scope
    if (!has_role(['admin','super_admin'])){
      $bid = (int)($_SESSION['branch_id'] ?? 0);
      if ($bid>0){ $w[]='e.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (!empty($_GET['branch_id'])){
      $w[]='e.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    $sql = "SELECT e.id,
                   e.expense_date AS exp_date,
                   e.description,
                   e.amount,
                   COALESCE(c.name,'') AS category
            FROM expenditures e
            LEFT JOIN expenditure_categories c ON c.id=e.category_id
            WHERE e.expense_date BETWEEN ? AND ?";
    if ($w) { $sql .= ' AND '.implode(' AND ',$w); }
    $sql .= ' ORDER BY e.expense_date DESC, e.id DESC LIMIT 5';

    $st = $mysqli->prepare($sql);
    if (!$st){ json_response(['message'=>'SQL prepare failed (recent_expenditures list): '.$mysqli->error],500); }
    $st->bind_param($types, ...$params);
    $st->execute();
    $res = $st->get_result();
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r;

    // Month total
    $sqlT = "SELECT COALESCE(SUM(e.amount),0)
             FROM expenditures e
             WHERE e.expense_date BETWEEN ? AND ?";
    if ($w) { $sqlT .= ' AND '.implode(' AND ',$w); }
    $st2 = $mysqli->prepare($sqlT);
    if (!$st2){ json_response(['message'=>'SQL prepare failed (recent_expenditures total): '.$mysqli->error],500); }
    $st2->bind_param($types, ...$params);
    $st2->execute();
    $mt=(float)($st2->get_result()->fetch_row()[0]??0);

    json_response(['data'=>$rows,'month_total'=>$mt]); break; }

  case 'recent_payroll': {
    // Latest 5 payroll entries
    $res = $mysqli->query("SELECT p.id, p.period_month, p.period_year, e.full_name AS employee_name, p.net_salary, p.paid_on FROM salary_payroll p LEFT JOIN employees e ON e.id=p.employee_id ORDER BY p.created_at DESC, p.id DESC LIMIT 5");
    $rows=[]; if($res){ while($r=$res->fetch_assoc()) $rows[]=$r; }
    json_response(['data'=>$rows]); break; }

  default: json_response(['message'=>'Invalid action'],400);
}
