<?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'] ?? $_POST['action'] ?? 'daily';

switch ($action) {
  case 'daily':
    $status = trim($_GET['status'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $color_id = isset($_GET['color_id']) && $_GET['color_id']!=='' ? (int)$_GET['color_id'] : null;

    $where=[]; $types=''; $params=[];

    // Branch filtering
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='f.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (isset($_GET['branch_id']) && $_GET['branch_id']!=='') {
      $where[]='f.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    if ($color_id!==null) { $where[]='r.color_id = ?'; $types.='i'; $params[]=$color_id; }
    
    // Polishing daily production
    $sql_polish = 'SELECT 
                    DATE(f.completed_at) AS production_date,
                    COUNT(*) AS polish_job_count,
                    ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                    ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft
                  FROM polishing_jobs j
                  JOIN raw_material_products r ON r.id=j.rmp_id
                  LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql_polish .= ' WHERE '.implode(' AND ',$where);
    $sql_polish .= ' GROUP BY DATE(f.completed_at) ORDER BY production_date DESC';
    
    if ($where){ $stmt=$mysqli->prepare($sql_polish); $stmt->bind_param($types, ...$params); $stmt->execute(); $res_polish=$stmt->get_result(); }
    else { $res_polish=$mysqli->query($sql_polish); }
    
    $polish_rows=[]; while($row=$res_polish->fetch_assoc()){ $polish_rows[]=$row; }
    
    // Cutting daily production
    $cut_where=[]; $cut_types=''; $cut_params=[];
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $cut_where[]='DATE(cj.job_date) >= ?'; $cut_types.='s'; $cut_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $cut_where[]='DATE(cj.job_date) <= ?'; $cut_types.='s'; $cut_params[]=$to; }
    if ($color_id!==null) { 
      $cut_where[]='r.color_id = ?'; $cut_types.='i'; $cut_params[]=$color_id; 
    }
    
    $sql_cut = 'SELECT 
                 DATE(cj.job_date) AS production_date,
                 COUNT(DISTINCT cj.id) AS cut_job_count,
                 ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
               FROM cutting_jobs cj
               JOIN cut_pieces cp ON cp.job_id = cj.id';
    if ($color_id!==null) {
      $sql_cut .= ' JOIN raw_material_products r ON r.id=cj.source_id';
    }
    if ($cut_where) {
      $sql_cut .= ' WHERE '.implode(' AND ',$cut_where);
    }
    $sql_cut .= ' GROUP BY DATE(cj.job_date) ORDER BY production_date DESC';
    
    if ($cut_where){ $stmt=$mysqli->prepare($sql_cut); $stmt->bind_param($cut_types, ...$cut_params); $stmt->execute(); $res_cut=$stmt->get_result(); }
    else { $res_cut=$mysqli->query($sql_cut); }
    
    $cut_rows=[]; while($row=$res_cut->fetch_assoc()){ $cut_rows[]=$row; }
    
    // Color-wise totals
    $color_where=[]; $color_types=''; $color_params=[];
    if ($color_id!==null) { $color_where[]='r.color_id = ?'; $color_types.='i'; $color_params[]=$color_id; }
    // For polishing date filter (using completed_at)
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $color_where[]='(DATE(f.completed_at) >= ? OR DATE(cj.job_date) >= ?)'; $color_types.='ss'; $color_params[]=$from; $color_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $color_where[]='(DATE(f.completed_at) <= ? OR DATE(cj.job_date) <= ?)'; $color_types.='ss'; $color_params[]=$to; $color_params[]=$to; }
    // For status filter on polishing
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $color_where[]='j.status=?'; $color_types.='s'; $color_params[]=$status; }
    
    $sql_color = 'SELECT 
                   c.id AS color_id,
                   COALESCE(c.name, "Unknown") AS color_name,
                   COUNT(DISTINCT j.id) AS polish_job_count,
                   ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                   ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft,
                   COUNT(DISTINCT cj.id) AS cut_job_count,
                   ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
                 FROM raw_material_products r
                 LEFT JOIN colors c ON c.id = r.color_id
                 LEFT JOIN polishing_jobs j ON j.rmp_id=r.id
                 LEFT JOIN final_products f ON f.polishing_job_id=j.id
                 LEFT JOIN cutting_jobs cj ON cj.source_id=r.id
                 LEFT JOIN cut_pieces cp ON cp.job_id = cj.id';
    
    if ($color_where) {
      $sql_color .= ' WHERE '.implode(' AND ',$color_where);
    }
    $sql_color .= ' GROUP BY c.id, c.name ORDER BY polish_pre_sq_ft DESC';
    
    if ($color_where){ $stmt=$mysqli->prepare($sql_color); $stmt->bind_param($color_types, ...$color_params); $stmt->execute(); $res_color=$stmt->get_result(); }
    else { $res_color=$mysqli->query($sql_color); }
    
    $color_rows=[]; while($row=$res_color->fetch_assoc()){ $color_rows[]=$row; }
    
    json_response([
      'polishing_daily' => $polish_rows,
      'cutting_daily' => $cut_rows,
      'color_totals' => $color_rows
    ]);
    break;

  case 'weekly':
    $status = trim($_GET['status'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $color_id = isset($_GET['color_id']) && $_GET['color_id']!=='' ? (int)$_GET['color_id'] : null;

    $where=[]; $types=''; $params=[];

    // Branch filtering
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='f.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (isset($_GET['branch_id']) && $_GET['branch_id']!=='') {
      $where[]='f.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    if ($color_id!==null) { $where[]='r.color_id = ?'; $types.='i'; $params[]=$color_id; }

    // Polishing weekly production
    $sql_polish = 'SELECT
                    YEARWEEK(DATE(f.completed_at), 1) AS week_number,
                    MIN(DATE(f.completed_at)) AS week_start,
                    MAX(DATE(f.completed_at)) AS week_end,
                    COUNT(*) AS polish_job_count,
                    ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                    ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft
                  FROM polishing_jobs j
                  JOIN raw_material_products r ON r.id=j.rmp_id
                  LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql_polish .= ' WHERE '.implode(' AND ',$where);
    $sql_polish .= ' GROUP BY YEARWEEK(DATE(f.completed_at), 1) ORDER BY week_number DESC';

    if ($where){ $stmt=$mysqli->prepare($sql_polish); $stmt->bind_param($types, ...$params); $stmt->execute(); $res_polish=$stmt->get_result(); }
    else { $res_polish=$mysqli->query($sql_polish); }

    $polish_rows=[]; while($row=$res_polish->fetch_assoc()){ $polish_rows[]=$row; }

    // Cutting weekly production
    $cut_where=[]; $cut_types=''; $cut_params=[];
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $cut_where[]='DATE(cj.job_date) >= ?'; $cut_types.='s'; $cut_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $cut_where[]='DATE(cj.job_date) <= ?'; $cut_types.='s'; $cut_params[]=$to; }
    if ($color_id!==null) {
      $cut_where[]='r.color_id = ?'; $cut_types.='i'; $cut_params[]=$color_id;
    }

    $sql_cut = 'SELECT
                 YEARWEEK(DATE(cj.job_date), 1) AS week_number,
                 MIN(DATE(cj.job_date)) AS week_start,
                 MAX(DATE(cj.job_date)) AS week_end,
                 COUNT(DISTINCT cj.id) AS cut_job_count,
                 ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
               FROM cutting_jobs cj
               JOIN cut_pieces cp ON cp.job_id = cj.id';
    if ($color_id!==null) {
      $sql_cut .= ' JOIN raw_material_products r ON r.id=cj.source_id';
    }
    if ($cut_where) {
      $sql_cut .= ' WHERE '.implode(' AND ',$cut_where);
    }
    $sql_cut .= ' GROUP BY YEARWEEK(DATE(cj.job_date), 1) ORDER BY week_number DESC';

    if ($cut_where){ $stmt=$mysqli->prepare($sql_cut); $stmt->bind_param($cut_types, ...$cut_params); $stmt->execute(); $res_cut=$stmt->get_result(); }
    else { $res_cut=$mysqli->query($sql_cut); }

    $cut_rows=[]; while($row=$res_cut->fetch_assoc()){ $cut_rows[]=$row; }

    // Color-wise totals (same as daily)
    $color_where=[]; $color_types=''; $color_params=[];
    if ($color_id!==null) { $color_where[]='r.color_id = ?'; $color_types.='i'; $color_params[]=$color_id; }
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $color_where[]='(DATE(f.completed_at) >= ? OR DATE(cj.job_date) >= ?)'; $color_types.='ss'; $color_params[]=$from; $color_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $color_where[]='(DATE(f.completed_at) <= ? OR DATE(cj.job_date) <= ?)'; $color_types.='ss'; $color_params[]=$to; $color_params[]=$to; }
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $color_where[]='j.status=?'; $color_types.='s'; $color_params[]=$status; }

    $sql_color = 'SELECT
                   c.id AS color_id,
                   COALESCE(c.name, "Unknown") AS color_name,
                   COUNT(DISTINCT j.id) AS polish_job_count,
                   ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                   ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft,
                   COUNT(DISTINCT cj.id) AS cut_job_count,
                   ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
                 FROM raw_material_products r
                 LEFT JOIN colors c ON c.id = r.color_id
                 LEFT JOIN polishing_jobs j ON j.rmp_id=r.id
                 LEFT JOIN final_products f ON f.polishing_job_id=j.id
                 LEFT JOIN cutting_jobs cj ON cj.source_id=r.id
                 LEFT JOIN cut_pieces cp ON cp.job_id = cj.id';

    if ($color_where) {
      $sql_color .= ' WHERE '.implode(' AND ',$color_where);
    }
    $sql_color .= ' GROUP BY c.id, c.name ORDER BY polish_pre_sq_ft DESC';

    if ($color_where){ $stmt=$mysqli->prepare($sql_color); $stmt->bind_param($color_types, ...$color_params); $stmt->execute(); $res_color=$stmt->get_result(); }
    else { $res_color=$mysqli->query($sql_color); }

    $color_rows=[]; while($row=$res_color->fetch_assoc()){ $color_rows[]=$row; }

    json_response([
      'polishing_weekly' => $polish_rows,
      'cutting_weekly' => $cut_rows,
      'color_totals' => $color_rows
    ]);
    break;

  case 'monthly':
    $status = trim($_GET['status'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $color_id = isset($_GET['color_id']) && $_GET['color_id']!=='' ? (int)$_GET['color_id'] : null;

    $where=[]; $types=''; $params=[];

    // Branch filtering
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='f.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (isset($_GET['branch_id']) && $_GET['branch_id']!=='') {
      $where[]='f.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    if ($color_id!==null) { $where[]='r.color_id = ?'; $types.='i'; $params[]=$color_id; }

    // Polishing monthly production
    $sql_polish = 'SELECT
                    DATE_FORMAT(DATE(f.completed_at), "%Y-%m") AS month_period,
                    MIN(DATE(f.completed_at)) AS month_start,
                    MAX(DATE(f.completed_at)) AS month_end,
                    COUNT(*) AS polish_job_count,
                    ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                    ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft
                  FROM polishing_jobs j
                  JOIN raw_material_products r ON r.id=j.rmp_id
                  LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql_polish .= ' WHERE '.implode(' AND ',$where);
    $sql_polish .= ' GROUP BY DATE_FORMAT(DATE(f.completed_at), "%Y-%m") ORDER BY month_period DESC';

    if ($where){ $stmt=$mysqli->prepare($sql_polish); $stmt->bind_param($types, ...$params); $stmt->execute(); $res_polish=$stmt->get_result(); }
    else { $res_polish=$mysqli->query($sql_polish); }

    $polish_rows=[]; while($row=$res_polish->fetch_assoc()){ $polish_rows[]=$row; }

    // Cutting monthly production
    $cut_where=[]; $cut_types=''; $cut_params=[];
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $cut_where[]='DATE(cj.job_date) >= ?'; $cut_types.='s'; $cut_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $cut_where[]='DATE(cj.job_date) <= ?'; $cut_types.='s'; $cut_params[]=$to; }
    if ($color_id!==null) {
      $cut_where[]='r.color_id = ?'; $cut_types.='i'; $cut_params[]=$color_id;
    }

    $sql_cut = 'SELECT
                 DATE_FORMAT(DATE(cj.job_date), "%Y-%m") AS month_period,
                 MIN(DATE(cj.job_date)) AS month_start,
                 MAX(DATE(cj.job_date)) AS month_end,
                 COUNT(DISTINCT cj.id) AS cut_job_count,
                 ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
               FROM cutting_jobs cj
               JOIN cut_pieces cp ON cp.job_id = cj.id';
    if ($color_id!==null) {
      $sql_cut .= ' JOIN raw_material_products r ON r.id=cj.source_id';
    }
    if ($cut_where) {
      $sql_cut .= ' WHERE '.implode(' AND ',$cut_where);
    }
    $sql_cut .= ' GROUP BY DATE_FORMAT(DATE(cj.job_date), "%Y-%m") ORDER BY month_period DESC';

    if ($cut_where){ $stmt=$mysqli->prepare($sql_cut); $stmt->bind_param($cut_types, ...$cut_params); $stmt->execute(); $res_cut=$stmt->get_result(); }
    else { $res_cut=$mysqli->query($sql_cut); }

    $cut_rows=[]; while($row=$res_cut->fetch_assoc()){ $cut_rows[]=$row; }

    // Color-wise totals (same as daily)
    $color_where=[]; $color_types=''; $color_params=[];
    if ($color_id!==null) { $color_where[]='r.color_id = ?'; $color_types.='i'; $color_params[]=$color_id; }
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $color_where[]='(DATE(f.completed_at) >= ? OR DATE(cj.job_date) >= ?)'; $color_types.='ss'; $color_params[]=$from; $color_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $color_where[]='(DATE(f.completed_at) <= ? OR DATE(cj.job_date) <= ?)'; $color_types.='ss'; $color_params[]=$to; $color_params[]=$to; }
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $color_where[]='j.status=?'; $color_types.='s'; $color_params[]=$status; }

    $sql_color = 'SELECT
                   c.id AS color_id,
                   COALESCE(c.name, "Unknown") AS color_name,
                   COUNT(DISTINCT j.id) AS polish_job_count,
                   ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                   ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft,
                   COUNT(DISTINCT cj.id) AS cut_job_count,
                   ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
                 FROM raw_material_products r
                 LEFT JOIN colors c ON c.id = r.color_id
                 LEFT JOIN polishing_jobs j ON j.rmp_id=r.id
                 LEFT JOIN final_products f ON f.polishing_job_id=j.id
                 LEFT JOIN cutting_jobs cj ON cj.source_id=r.id
                 LEFT JOIN cut_pieces cp ON cp.job_id = cj.id';

    if ($color_where) {
      $sql_color .= ' WHERE '.implode(' AND ',$color_where);
    }
    $sql_color .= ' GROUP BY c.id, c.name ORDER BY polish_pre_sq_ft DESC';

    if ($color_where){ $stmt=$mysqli->prepare($sql_color); $stmt->bind_param($color_types, ...$color_params); $stmt->execute(); $res_color=$stmt->get_result(); }
    else { $res_color=$mysqli->query($sql_color); }

    $color_rows=[]; while($row=$res_color->fetch_assoc()){ $color_rows[]=$row; }

    json_response([
      'polishing_monthly' => $polish_rows,
      'cutting_monthly' => $cut_rows,
      'color_totals' => $color_rows
    ]);
    break;

  case 'yearly':
    $status = trim($_GET['status'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $color_id = isset($_GET['color_id']) && $_GET['color_id']!=='' ? (int)$_GET['color_id'] : null;

    $where=[]; $types=''; $params=[];

    // Branch filtering
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='f.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (isset($_GET['branch_id']) && $_GET['branch_id']!=='') {
      $where[]='f.branch_id=?'; $types.='i'; $params[]=(int)$_GET['branch_id'];
    }

    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    if ($color_id!==null) { $where[]='r.color_id = ?'; $types.='i'; $params[]=$color_id; }

    // Polishing yearly production
    $sql_polish = 'SELECT
                    YEAR(DATE(f.completed_at)) AS year_period,
                    COUNT(*) AS polish_job_count,
                    ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                    ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft
                  FROM polishing_jobs j
                  JOIN raw_material_products r ON r.id=j.rmp_id
                  LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql_polish .= ' WHERE '.implode(' AND ',$where);
    $sql_polish .= ' GROUP BY YEAR(DATE(f.completed_at)) ORDER BY year_period DESC';

    if ($where){ $stmt=$mysqli->prepare($sql_polish); $stmt->bind_param($types, ...$params); $stmt->execute(); $res_polish=$stmt->get_result(); }
    else { $res_polish=$mysqli->query($sql_polish); }

    $polish_rows=[]; while($row=$res_polish->fetch_assoc()){ $polish_rows[]=$row; }

    // Cutting yearly production
    $cut_where=[]; $cut_types=''; $cut_params=[];
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $cut_where[]='DATE(cj.job_date) >= ?'; $cut_types.='s'; $cut_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $cut_where[]='DATE(cj.job_date) <= ?'; $cut_types.='s'; $cut_params[]=$to; }
    if ($color_id!==null) {
      $cut_where[]='r.color_id = ?'; $cut_types.='i'; $cut_params[]=$color_id;
    }

    $sql_cut = 'SELECT
                 YEAR(DATE(cj.job_date)) AS year_period,
                 COUNT(DISTINCT cj.id) AS cut_job_count,
                 ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
               FROM cutting_jobs cj
               JOIN cut_pieces cp ON cp.job_id = cj.id';
    if ($color_id!==null) {
      $sql_cut .= ' JOIN raw_material_products r ON r.id=cj.source_id';
    }
    if ($cut_where) {
      $sql_cut .= ' WHERE '.implode(' AND ',$cut_where);
    }
    $sql_cut .= ' GROUP BY YEAR(DATE(cj.job_date)) ORDER BY year_period DESC';

    if ($cut_where){ $stmt=$mysqli->prepare($sql_cut); $stmt->bind_param($cut_types, ...$cut_params); $stmt->execute(); $res_cut=$stmt->get_result(); }
    else { $res_cut=$mysqli->query($sql_cut); }

    $cut_rows=[]; while($row=$res_cut->fetch_assoc()){ $cut_rows[]=$row; }

    // Color-wise totals (same as daily)
    $color_where=[]; $color_types=''; $color_params=[];
    if ($color_id!==null) { $color_where[]='r.color_id = ?'; $color_types.='i'; $color_params[]=$color_id; }
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $color_where[]='(DATE(f.completed_at) >= ? OR DATE(cj.job_date) >= ?)'; $color_types.='ss'; $color_params[]=$from; $color_params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $color_where[]='(DATE(f.completed_at) <= ? OR DATE(cj.job_date) <= ?)'; $color_types.='ss'; $color_params[]=$to; $color_params[]=$to; }
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $color_where[]='j.status=?'; $color_types.='s'; $color_params[]=$status; }

    $sql_color = 'SELECT
                   c.id AS color_id,
                   COALESCE(c.name, "Unknown") AS color_name,
                   COUNT(DISTINCT j.id) AS polish_job_count,
                   ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS polish_pre_sq_ft,
                   ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS polish_final_sq_ft,
                   COUNT(DISTINCT cj.id) AS cut_job_count,
                   ROUND(SUM(cp.area_sq_ft), 3) AS cut_sq_ft
                 FROM raw_material_products r
                 LEFT JOIN colors c ON c.id = r.color_id
                 LEFT JOIN polishing_jobs j ON j.rmp_id=r.id
                 LEFT JOIN final_products f ON f.polishing_job_id=j.id
                 LEFT JOIN cutting_jobs cj ON cj.source_id=r.id
                 LEFT JOIN cut_pieces cp ON cp.job_id = cj.id';

    if ($color_where) {
      $sql_color .= ' WHERE '.implode(' AND ',$color_where);
    }
    $sql_color .= ' GROUP BY c.id, c.name ORDER BY polish_pre_sq_ft DESC';

    if ($color_where){ $stmt=$mysqli->prepare($sql_color); $stmt->bind_param($color_types, ...$color_params); $stmt->execute(); $res_color=$stmt->get_result(); }
    else { $res_color=$mysqli->query($sql_color); }

    $color_rows=[]; while($row=$res_color->fetch_assoc()){ $color_rows[]=$row; }

    json_response([
      'polishing_yearly' => $polish_rows,
      'cutting_yearly' => $cut_rows,
      'color_totals' => $color_rows
    ]);
    break;

  case 'options':
    // Load colors for filter
    $res = $mysqli->query('SELECT id, name FROM colors ORDER BY name ASC');
    $colors = [];
    while($row = $res->fetch_assoc()) { $colors[] = $row; }
    json_response(['colors' => $colors]);
    break;

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