<?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 = $_REQUEST['action'] ?? 'list';

function table_has_column(mysqli $mysqli, string $table, string $column): bool {
  static $cache = [];
  $key = $table . '.' . $column;
  if (array_key_exists($key, $cache)) return $cache[$key];
  $sql = 'SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1';
  $stmt = $mysqli->prepare($sql);
  $stmt->bind_param('ss', $table, $column);
  $stmt->execute();
  $exists = (bool)$stmt->get_result()->fetch_row();
  $cache[$key] = $exists;
  return $exists;
}

function ensure_admin_fp(){ if (!has_role(['admin','super_admin'])) json_response(['message'=>'Forbidden'], 403); }

switch ($action) {
  case 'stats': {
    // KPIs for finished products (supports filters so top badges can reflect current results)
    $params=[]; $types=''; $wheres=[];
    $min_h = $_GET['min_h'] ?? ''; $max_h = $_GET['max_h'] ?? '';
    $min_l = $_GET['min_l'] ?? ''; $max_l = $_GET['max_l'] ?? '';
    $min_sqf = $_GET['min_sqf'] ?? ''; $max_sqf = $_GET['max_sqf'] ?? '';
    $type_id = (int)($_GET['type_id'] ?? 0); $color_id=(int)($_GET['color_id'] ?? 0);
    $finish_id = (int)($_GET['finish_id'] ?? 0);
    $status = trim($_GET['status'] ?? '');
    $po_no = trim($_GET['po_no'] ?? '');
    $slab_no = trim($_GET['slab_no'] ?? '');
    $branch_id=(int)($_GET['branch_id'] ?? 0); $store_id=(int)($_GET['store_location_id'] ?? 0);
    if ($min_h!==''){ $wheres[]='f.final_height>=?'; $types.='d'; $params[]=$min_h; }
    if ($max_h!==''){ $wheres[]='f.final_height<=?'; $types.='d'; $params[]=$max_h; }
    if ($min_l!==''){ $wheres[]='f.final_length>=?'; $types.='d'; $params[]=$min_l; }
    if ($max_l!==''){ $wheres[]='f.final_length<=?'; $types.='d'; $params[]=$max_l; }
    if ($min_sqf!==''){ $wheres[]='f.final_sq_ft>=?'; $types.='d'; $params[]=$min_sqf; }
    if ($max_sqf!==''){ $wheres[]='f.final_sq_ft<=?'; $types.='d'; $params[]=$max_sqf; }
    if ($type_id){ $wheres[]='f.type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($color_id){ $wheres[]='f.color_id=?'; $types.='i'; $params[]=$color_id; }
    if ($finish_id){ $wheres[]='f.finish_id=?'; $types.='i'; $params[]=$finish_id; }
    if ($status !== ''){ $wheres[]='f.sale_status=?'; $types.='s'; $params[]=$status; }
    if ($po_no !== ''){ $wheres[]='p.order_no LIKE ?'; $types.='s'; $params[]='%'.$po_no.'%'; }
    if ($slab_no !== ''){ $wheres[]='r.slab_no LIKE ?'; $types.='s'; $params[]='%'.$slab_no.'%'; }
    if ($branch_id){ $wheres[]='f.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($store_id){ $wheres[]='f.store_location_id=?'; $types.='i'; $params[]=$store_id; }

    // Branch scoping for non-admins (include legacy rows with NULL branch_id)
    $cu = current_user();
    if ($cu && !has_role(['admin','super_admin']) && !empty($cu['branch_id'])) {
      $wheres[]='(f.branch_id=? OR f.branch_id IS NULL)';
      $types.='i';
      $params[]=(int)$cu['branch_id'];
    }

    $sql = 'SELECT 
              COUNT(*) AS total,
              SUM(CASE WHEN f.sale_status="available" THEN 1 ELSE 0 END) AS available,
              SUM(CASE WHEN f.sale_status="reserved" THEN 1 ELSE 0 END) AS reserved,
              SUM(CASE WHEN f.sale_status="sold" THEN 1 ELSE 0 END) AS sold,
              ROUND(SUM(COALESCE(f.final_sq_ft,0)), 3) AS total_sq_ft,
              ROUND(SUM(COALESCE(f.final_sq_m2,0)), 3) AS total_sq_m
            FROM final_products f
            LEFT JOIN raw_material_products r ON r.id=f.rmp_id
            LEFT JOIN purchase_orders p ON p.id=f.po_id';
    if ($wheres) { $sql .= ' WHERE ' . implode(' AND ', $wheres); }

    if ($wheres){
      $stmt=$mysqli->prepare($sql);
      if (!$stmt) json_response(['message'=>'SQL prepare failed (stats): '.$mysqli->error], 500);
      $stmt->bind_param($types, ...$params);
      $stmt->execute();
      $res=$stmt->get_result();
    } else {
      $res=$mysqli->query($sql);
    }

    $row = $res->fetch_assoc() ?: [
      'total'=>0,'available'=>0,'reserved'=>0,'sold'=>0,'total_sq_ft'=>0,'total_sq_m'=>0
    ];
    json_response($row);
    }
    break;

  case 'by_finish': {
    // Aggregate finished products by finish and color
    $wheres = [];
    $types=''; $params=[];
    // Optional filters (mirror list filters lightly)
    $type_id = (int)($_GET['type_id'] ?? 0);
    $color_id = (int)($_GET['color_id'] ?? 0);
    $branch_id = (int)($_GET['branch_id'] ?? 0);
    $status = trim($_GET['status'] ?? '');
    if ($type_id){ $wheres[]='f.type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($color_id){ $wheres[]='f.color_id=?'; $types.='i'; $params[]=$color_id; }
    if ($branch_id){ $wheres[]='f.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($status !== ''){ $wheres[]='f.sale_status=?'; $types.='s'; $params[]=$status; }
    // Branch scoping for non-admins
    $cu = current_user();
    if ($cu && !has_role(['admin','super_admin']) && !empty($cu['branch_id'])) {
      $wheres[]='(f.branch_id=? OR f.branch_id IS NULL)'; $types.='i'; $params[]=(int)$cu['branch_id'];
    }
    $sql = 'SELECT 
              f.finish_id,
              fn.name AS finish_name,
              f.color_id,
              c.name AS color_name,
              COUNT(*) AS item_count,
              ROUND(SUM(COALESCE(f.final_sq_ft,0)), 3) AS total_sq_ft,
              ROUND(SUM(COALESCE(f.final_sq_m2,0)), 3) AS total_sq_m
            FROM final_products f
            LEFT JOIN finishes fn ON fn.id=f.finish_id
            LEFT JOIN colors c ON c.id=f.color_id';
    if ($wheres) { $sql .= ' WHERE ' . implode(' AND ', $wheres); }
    $sql .= ' GROUP BY f.finish_id, fn.name, f.color_id, c.name ORDER BY fn.name, c.name';
    if ($wheres){ $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res=$mysqli->query($sql); }
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r; json_response(['data'=>$rows]);
    }
    break;

  case 'by_color': {
    $wheres = [];
    $types=''; $params=[];
    $type_id = (int)($_GET['type_id'] ?? 0);
    $finish_id = (int)($_GET['finish_id'] ?? 0);
    $branch_id = (int)($_GET['branch_id'] ?? 0);
    $status = trim($_GET['status'] ?? '');
    if ($type_id){ $wheres[]='f.type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($finish_id){ $wheres[]='f.finish_id=?'; $types.='i'; $params[]=$finish_id; }
    if ($branch_id){ $wheres[]='f.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($status !== ''){ $wheres[]='f.sale_status=?'; $types.='s'; $params[]=$status; }
    $cu = current_user();
    if ($cu && !has_role(['admin','super_admin']) && !empty($cu['branch_id'])) {
      $wheres[]='(f.branch_id=? OR f.branch_id IS NULL)'; $types.='i'; $params[]=(int)$cu['branch_id'];
    }
    $sql = 'SELECT 
              f.color_id,
              c.name AS color_name,
              COUNT(*) AS item_count,
              ROUND(SUM(COALESCE(f.final_sq_ft,0)), 3) AS total_sq_ft,
              ROUND(SUM(COALESCE(f.final_sq_m2,0)), 3) AS total_sq_m
            FROM final_products f
            LEFT JOIN colors c ON c.id=f.color_id';
    if ($wheres) { $sql .= ' WHERE ' . implode(' AND ', $wheres); }
    $sql .= ' GROUP BY f.color_id, c.name ORDER BY c.name';
    if ($wheres){ $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res=$mysqli->query($sql); }
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r; json_response(['data'=>$rows]);
    }
    break;

  case 'list': {
    $params=[]; $types=''; $wheres=[];
    $min_h = $_GET['min_h'] ?? ''; $max_h = $_GET['max_h'] ?? '';
    $min_l = $_GET['min_l'] ?? ''; $max_l = $_GET['max_l'] ?? '';
    $min_sqf = $_GET['min_sqf'] ?? ''; $max_sqf = $_GET['max_sqf'] ?? '';
    $type_id = (int)($_GET['type_id'] ?? 0); $color_id=(int)($_GET['color_id'] ?? 0);
    $finish_id = (int)($_GET['finish_id'] ?? 0);
    $status = trim($_GET['status'] ?? '');
    $po_no = trim($_GET['po_no'] ?? '');
    $slab_no = trim($_GET['slab_no'] ?? '');
    $branch_id=(int)($_GET['branch_id'] ?? 0); $store_id=(int)($_GET['store_location_id'] ?? 0);
    if ($min_h!==''){ $wheres[]='f.final_height>=?'; $types.='d'; $params[]=$min_h; }
    if ($max_h!==''){ $wheres[]='f.final_height<=?'; $types.='d'; $params[]=$max_h; }
    if ($min_l!==''){ $wheres[]='f.final_length>=?'; $types.='d'; $params[]=$min_l; }
    if ($max_l!==''){ $wheres[]='f.final_length<=?'; $types.='d'; $params[]=$max_l; }
    if ($min_sqf!==''){ $wheres[]='f.final_sq_ft>=?'; $types.='d'; $params[]=$min_sqf; }
    if ($max_sqf!==''){ $wheres[]='f.final_sq_ft<=?'; $types.='d'; $params[]=$max_sqf; }
    if ($type_id){ $wheres[]='f.type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($color_id){ $wheres[]='f.color_id=?'; $types.='i'; $params[]=$color_id; }
    if ($finish_id){ $wheres[]='f.finish_id=?'; $types.='i'; $params[]=$finish_id; }
    if ($status !== ''){ $wheres[]='f.sale_status=?'; $types.='s'; $params[]=$status; }
    if ($po_no !== ''){ $wheres[]='p.order_no LIKE ?'; $types.='s'; $params[]='%'.$po_no.'%'; }
    if ($slab_no !== ''){ $wheres[]='r.slab_no LIKE ?'; $types.='s'; $params[]='%'.$slab_no.'%'; }
    if ($branch_id){ $wheres[]='f.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($store_id){ $wheres[]='f.store_location_id=?'; $types.='i'; $params[]=$store_id; }

    $sql = 'SELECT f.*, r.slab_no, p.order_no, fn.name AS finish_name, pt.name AS type_name, c.name AS color_name,
                   b.name AS branch_name, s.name AS store_name
            FROM final_products f
            LEFT JOIN raw_material_products r ON r.id=f.rmp_id
            LEFT JOIN purchase_orders p ON p.id=f.po_id
            LEFT JOIN finishes fn ON fn.id=f.finish_id
            LEFT JOIN product_types pt ON pt.id=f.type_id
            LEFT JOIN colors c ON c.id=f.color_id
            LEFT JOIN branches b ON b.id=f.branch_id
            LEFT JOIN store_locations s ON s.id=f.store_location_id';
    // By default, exclude 'cut' status unless specifically requested
    if ($status === '') {
      $wheres[] = 'f.sale_status <> \'cut\'';
    }
    // Branch scoping: non-admin users see their branch; include legacy rows with NULL branch_id
    $cu = current_user();
    if ($cu && !has_role(['admin','super_admin']) && !empty($cu['branch_id'])) {
      $wheres[] = '(f.branch_id=? OR f.branch_id IS NULL)'; $types.='i'; $params[] = (int)$cu['branch_id'];
    }
    if ($wheres) $sql .= ' WHERE ' . implode(' AND ', $wheres);
    $sql .= ' ORDER BY f.id DESC';
    if ($wheres){ $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res=$mysqli->query($sql); }
    $rows=[]; while($row=$res->fetch_assoc()) $rows[]=$row; json_response(['data'=>$rows]);
    }
    break;

  case 'get': {
    $id=(int)($_GET['id']??0); if(!$id) json_response(['message'=>'ID required'],422);
    // Return type_id/color_id falling back to raw material when missing on final_products
    $sql='SELECT 
            f.*,
            COALESCE(f.type_id, r.product_type_id) AS type_id,
            COALESCE(f.color_id, r.color_id) AS color_id
          FROM final_products f
          LEFT JOIN raw_material_products r ON r.id=f.rmp_id
          WHERE f.id=?';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param('i',$id); $stmt->execute();
    $row=$stmt->get_result()->fetch_assoc(); json_response(['data'=>$row]);
    }
    break;

  case 'detail': {
    $id=(int)($_GET['id']??0); if(!$id) json_response(['message'=>'ID required'],422);
    $sql='SELECT 
            f.*, 
            r.id AS rmp_internal_id, r.slab_no, r.height AS raw_height, r.length AS raw_length, r.sq_ft AS raw_sq_ft, r.quality AS raw_quality,
            p.order_no, p.order_date, p.ship_mode, p.delivery_term, p.payment_term,
            sup.name AS supplier_name,
            fn.name AS finish_name,
            pt.name AS type_name,
            c.name AS color_name,
            b.name AS branch_name,
            s.name AS store_name,
            pj.pre_height, pj.pre_length, pj.worker_name, pj.start_at, pj.end_at,
            i.invoice_no, i.id AS invoice_id
          FROM final_products f
          LEFT JOIN raw_material_products r ON r.id=f.rmp_id
          LEFT JOIN purchase_orders p ON p.id=f.po_id
          LEFT JOIN suppliers sup ON sup.id=p.supplier_id
          LEFT JOIN finishes fn ON fn.id=f.finish_id
          LEFT JOIN product_types pt ON pt.id=f.type_id
          LEFT JOIN colors c ON c.id=f.color_id
          LEFT JOIN branches b ON b.id=f.branch_id
          LEFT JOIN store_locations s ON s.id=f.store_location_id
          LEFT JOIN polishing_jobs pj ON pj.id=f.polishing_job_id
          LEFT JOIN invoice_items ii ON ii.final_product_id=f.id
          LEFT JOIN invoices i ON i.id=ii.invoice_id
          WHERE f.id=?';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param('i',$id); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc();
    if(!$row) json_response(['message'=>'Not found'],404);
    json_response(['data'=>$row]);
    }
    break;

  case 'create':
    ensure_admin_fp();
    $stmt=$mysqli->prepare('INSERT INTO final_products (rmp_id, po_id, polishing_job_id, final_height, final_length, final_sq_ft, final_sq_m2, finish_id, quality, sale_status, branch_id, store_location_id, type_id, color_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
    $stmt->bind_param('iiidd dddis s i i i i', $rmp_id, $po_id, $polishing_job_id, $final_height, $final_length, $final_sq_ft, $final_sq_m2, $finish_id, $quality, $sale_status, $branch_id, $store_location_id, $type_id, $color_id);
    json_response(['message'=>'Not implemented'],501);
    break;

  case 'update':
    ensure_admin_fp();
    $id = (int)($_POST['id'] ?? 0);
    if (!$id) json_response(['message' => 'ID required'], 422);

    // Read inputs (UI provides dimensions in inches, like polishing.php)
    $final_height = ($_POST['final_height'] === '' ? null : (float)$_POST['final_height']);
    $final_length = ($_POST['final_length'] === '' ? null : (float)$_POST['final_length']);
    $finish_id    = ($_POST['finish_id'] === '' ? null : (int)$_POST['finish_id']);
    $quality      = isset($_POST['quality']) && $_POST['quality'] !== '' ? (string)$_POST['quality'] : null;
    $remarks      = isset($_POST['remarks']) && $_POST['remarks'] !== '' ? (string)$_POST['remarks'] : null;
    $sale_status  = isset($_POST['sale_status']) && $_POST['sale_status'] !== '' ? (string)$_POST['sale_status'] : null;
    $branch_id    = ($_POST['branch_id'] === '' ? null : (int)$_POST['branch_id']);
    $store_id     = ($_POST['store_location_id'] === '' ? null : (int)$_POST['store_location_id']);
    $type_id      = ($_POST['type_id'] === '' ? null : (int)$_POST['type_id']);
    $color_id     = ($_POST['color_id'] === '' ? null : (int)$_POST['color_id']);

    // Recalculate area from H and L (inches -> feet) instead of trusting posted final_sq_ft
    $final_sq_ft = null;
    $final_sq_m2 = null;
    if ($final_height !== null && $final_length !== null && $final_height > 0 && $final_length > 0) {
      $h_ft = $final_height / 12.0;
      $l_ft = $final_length / 12.0;
      $final_sq_ft = round($h_ft * $l_ft, 3);
      $final_sq_m2 = round($final_sq_ft * 0.092903, 3);
    }

    $hasRemarksCol = table_has_column($mysqli, 'final_products', 'remarks');
    $sql = 'UPDATE final_products
            SET final_height = ?,
                final_length = ?,
                final_sq_ft = ?,
                final_sq_m2 = ?,
                finish_id = ?,
                quality = ?,';
    if ($hasRemarksCol) {
      $sql .= ' remarks = ?,';
    }
    $sql .= ' sale_status = ?,
                branch_id = ?,
                store_location_id = ?,
                type_id = ?,
                color_id = ?
            WHERE id = ?';

    $stmt = $mysqli->prepare($sql);
    if (!$stmt) {
      json_response(['message' => $mysqli->error], 500);
    }
    if ($hasRemarksCol) {
      // Types: dddd (heights/lengths/areas), i (finish_id), s (quality), s (remarks), s (sale_status), i (branch_id), i (store_id), i (type_id), i (color_id), i (id)
      $stmt->bind_param(
        'ddddisssiiiii',
        $final_height,
        $final_length,
        $final_sq_ft,
        $final_sq_m2,
        $finish_id,
        $quality,
        $remarks,
        $sale_status,
        $branch_id,
        $store_id,
        $type_id,
        $color_id,
        $id
      );
    } else {
      // Types: dddd (heights/lengths/areas), i (finish_id), s (quality), s (sale_status), i (branch_id), i (store_id), i (type_id), i (color_id), i (id)
      $stmt->bind_param(
        'ddddissiiiii',
        $final_height,
        $final_length,
        $final_sq_ft,
        $final_sq_m2,
        $finish_id,
        $quality,
        $sale_status,
        $branch_id,
        $store_id,
        $type_id,
        $color_id,
        $id
      );
    }
    if (!$stmt->execute()) {
      json_response(['message' => $stmt->error], 500);
    }
    json_response(['ok' => true]);
    break;

  case 'delete':
    ensure_admin_fp();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    if(!$id) json_response(['message' => 'ID required'], 422);

    // Start transaction
    $mysqli->begin_transaction();

    try {
        // First get the raw material ID and polishing job ID
        $stmt = $mysqli->prepare('SELECT rmp_id, polishing_job_id FROM final_products WHERE id = ?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();

        // Delete the finished product
        $deleteStmt = $mysqli->prepare('DELETE FROM final_products WHERE id = ?');
        $deleteStmt->bind_param('i', $id);
        $deleteStmt->execute();

        // If there was a related polishing job, delete it
        if ($row && !empty($row['polishing_job_id'])) {
            $deletePolishStmt = $mysqli->prepare('DELETE FROM polishing_jobs WHERE id = ?');
            $deletePolishStmt->bind_param('i', $row['polishing_job_id']);
            $deletePolishStmt->execute();
        }

        // If there was a related raw material, update its status back to 'received'
        if ($row && !empty($row['rmp_id'])) {
            $updateStmt = $mysqli->prepare('UPDATE raw_material_products SET status = ? WHERE id = ?');
            $newStatus = 'received';
            $updateStmt->bind_param('si', $newStatus, $row['rmp_id']);
            $updateStmt->execute();
        }

        // Commit the transaction
        $mysqli->commit();
        json_response(['ok' => true]);

    } catch (Exception $e) {
        // Rollback on error
        $mysqli->rollback();
        error_log("Error in finished_products.php delete: " . $e->getMessage());
        json_response(['message' => 'Error deleting finished product: ' . $e->getMessage()], 500);
    }
    break;

  case 'options': { // for filters selects
    $types=[]; $res=$mysqli->query('SELECT id, name FROM product_types ORDER BY name'); while($r=$res->fetch_assoc()) $types[]=$r;
    $colors=[]; $res=$mysqli->query('SELECT id, name FROM colors ORDER BY name'); while($r=$res->fetch_assoc()) $colors[]=$r;
    $branches=[]; $res=$mysqli->query('SELECT id, name FROM branches WHERE is_active=1 ORDER BY name'); while($r=$res->fetch_assoc()) $branches[]=$r;
    // Include branch_id with stores so UI can filter stores by selected branch
    $stores=[]; $res=$mysqli->query('SELECT id, name, branch_id FROM store_locations WHERE is_active=1 ORDER BY name'); while($r=$res->fetch_assoc()) $stores[]=$r;
    json_response(['types'=>$types,'colors'=>$colors,'branches'=>$branches,'stores'=>$stores]);
    }
    break;

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