<?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'] ?? 'list_companies';

function ca_ok($d){ echo json_encode($d); exit; }
function ca_err($m,$c=400){ http_response_code($c); echo json_encode(['message'=>$m]); exit; }

if (!function_exists('json_response')) {
  function json_response($d,$c=200){ http_response_code($c); echo json_encode($d); exit; }
}

switch ($action) {
  case 'list_companies': {
    $q = trim($_GET['q'] ?? '');
    $where=[]; $types=''; $params=[];
    if ($q !== '') {
      $where[] = '(company_name LIKE CONCAT("%", ?, "%") OR contact_person LIKE CONCAT("%", ?, "%") OR contact_number LIKE CONCAT("%", ?, "%"))';
      $types.='sss'; $params[]=$q; $params[]=$q; $params[]=$q;
    }
    $sql = 'SELECT id, company_name, contact_person, contact_number, address, remarks FROM competitor_companies';
    if ($where) $sql .= ' WHERE '.implode(' AND ', $where);
    $sql .= ' ORDER BY company_name ASC';
    if ($where) {
      $stmt = $mysqli->prepare($sql);
      $stmt->bind_param($types, ...$params);
      $stmt->execute();
      $res = $stmt->get_result();
    } else {
      $res = $mysqli->query($sql);
    }
    $rows=[]; if ($res) while($r=$res->fetch_assoc()) $rows[]=$r;
    ca_ok(['data'=>$rows]);
  } break;

  case 'get_company': {
    $id = (int)($_GET['id'] ?? 0); if(!$id) ca_err('Missing id');
    $stmt = $mysqli->prepare('SELECT id, company_name, contact_person, contact_number, address, remarks FROM competitor_companies WHERE id=?');
    $stmt->bind_param('i',$id); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc();
    if(!$row) ca_err('Not found',404);
    ca_ok(['data'=>$row]);
  } break;

  case 'save_company': {
    if (!validate_csrf($_POST['csrf'] ?? '')) ca_err('Invalid CSRF',400);
    $id = (int)($_POST['id'] ?? 0);
    $company_name = trim($_POST['company_name'] ?? '');
    if ($company_name==='') ca_err('Company Name required');
    $contact_person = trim($_POST['contact_person'] ?? '');
    $contact_number = trim($_POST['contact_number'] ?? '');
    $address = trim($_POST['address'] ?? '');
    $remarks = trim($_POST['remarks'] ?? '');
    if ($id) {
      $stmt=$mysqli->prepare('UPDATE competitor_companies SET company_name=?, contact_person=?, contact_number=?, address=?, remarks=? WHERE id=?');
      $stmt->bind_param('sssssi',$company_name,$contact_person,$contact_number,$address,$remarks,$id);
      if(!$stmt->execute()) ca_err($stmt->error,500);
      ca_ok(['message'=>'Updated','id'=>$id]);
    } else {
      $stmt=$mysqli->prepare('INSERT INTO competitor_companies(company_name,contact_person,contact_number,address,remarks) VALUES (?,?,?,?,?)');
      $stmt->bind_param('sssss',$company_name,$contact_person,$contact_number,$address,$remarks);
      if(!$stmt->execute()) ca_err($stmt->error,500);
      ca_ok(['message'=>'Created','id'=>$mysqli->insert_id]);
    }
  } break;

  case 'delete_company': {
    if (!validate_csrf($_POST['csrf'] ?? '')) ca_err('Invalid CSRF',400);
    $id = (int)($_POST['id'] ?? 0); if(!$id) ca_err('Missing id');
    // Optionally also delete items
    $mysqli->query('DELETE FROM competitor_items WHERE company_id='.(int)$id);
    $stmt=$mysqli->prepare('DELETE FROM competitor_companies WHERE id=?');
    $stmt->bind_param('i',$id);
    if(!$stmt->execute()) ca_err($stmt->error,500);
    ca_ok(['message'=>'Deleted']);
  } break;

  case 'list_items': {
    $company_id = (int)($_GET['company_id'] ?? 0); if(!$company_id) ca_err('Missing company_id');
    $sql = 'SELECT i.id, i.company_id, i.analysis_date, i.item, i.price_per_sqf, i.remarks,
                   i.color_id, i.finish_id,
                   c.name AS color_name, f.name AS finish_name
            FROM competitor_items i
            LEFT JOIN colors c ON c.id = i.color_id
            LEFT JOIN finishes f ON f.id = i.finish_id
            WHERE i.company_id=?
            ORDER BY i.analysis_date DESC, c.name ASC, f.name ASC';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param('i',$company_id); $stmt->execute(); $res=$stmt->get_result();
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r;
    ca_ok(['data'=>$rows]);
  } break;

  case 'save_item': {
    if (!validate_csrf($_POST['csrf'] ?? '')) ca_err('Invalid CSRF',400);
    $id = (int)($_POST['id'] ?? 0);
    $company_id = (int)($_POST['company_id'] ?? 0); if(!$company_id) ca_err('Missing company_id');
    $analysis_date = trim($_POST['analysis_date'] ?? '');
    if ($analysis_date==='') $analysis_date = date('Y-m-d');
    $color_id = isset($_POST['color_id']) && $_POST['color_id']!=='' ? (int)$_POST['color_id'] : null;
    $finish_id = isset($_POST['finish_id']) && $_POST['finish_id']!=='' ? (int)$_POST['finish_id'] : null;
    $price_per_sqf = ($_POST['price_per_sqf'] ?? '') !== '' ? (float)$_POST['price_per_sqf'] : null;
    $remarks = trim($_POST['remarks'] ?? '');
    // optional free-text label, kept for backward compatibility
    $item = trim($_POST['item'] ?? '');
    if ($id) {
      $stmt=$mysqli->prepare('UPDATE competitor_items SET analysis_date=?, item=?, price_per_sqf=?, remarks=?, color_id=?, finish_id=? WHERE id=? AND company_id=?');
      $stmt->bind_param('ssdsiiii',$analysis_date,$item,$price_per_sqf,$remarks,$color_id,$finish_id,$id,$company_id);
      if(!$stmt->execute()) ca_err($stmt->error,500);
      ca_ok(['message'=>'Updated','id'=>$id]);
    } else {
      $stmt=$mysqli->prepare('INSERT INTO competitor_items(company_id,analysis_date,item,price_per_sqf,remarks,color_id,finish_id) VALUES (?,?,?,?,?,?,?)');
      $stmt->bind_param('issdsii',$company_id,$analysis_date,$item,$price_per_sqf,$remarks,$color_id,$finish_id);
      if(!$stmt->execute()) ca_err($stmt->error,500);
      ca_ok(['message'=>'Created','id'=>$mysqli->insert_id]);
    }
  } break;

  case 'delete_item': {
    if (!validate_csrf($_POST['csrf'] ?? '')) ca_err('Invalid CSRF',400);
    $id = (int)($_POST['id'] ?? 0); if(!$id) ca_err('Missing id');
    $stmt=$mysqli->prepare('DELETE FROM competitor_items WHERE id=?');
    $stmt->bind_param('i',$id);
    if(!$stmt->execute()) ca_err($stmt->error,500);
    ca_ok(['message'=>'Deleted']);
  } break;

  case 'monthly_report': {
    $year = (int)($_GET['year'] ?? 0);
    $month = (int)($_GET['month'] ?? 0);
    if ($year<=0 || $month<=0 || $month>12) ca_err('Invalid year/month');

    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : 0;
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : 0;

    $sql = "SELECT c.id AS company_id, c.company_name, c.contact_person, c.contact_number, c.address,
                   i.id AS item_id, i.analysis_date, i.item, i.price_per_sqf, i.remarks,
                   i.color_id, i.finish_id, col.name AS color_name, fin.name AS finish_name
            FROM competitor_companies c
            LEFT JOIN competitor_items i
              ON i.company_id = c.id
             AND YEAR(i.analysis_date) = ?
             AND MONTH(i.analysis_date) = ?
            LEFT JOIN colors col ON col.id = i.color_id
            LEFT JOIN finishes fin ON fin.id = i.finish_id
            WHERE (? = 0 OR col.id = ?)
              AND (? = 0 OR fin.id = ?)
            ORDER BY c.company_name ASC, i.analysis_date ASC, col.name ASC, fin.name ASC";
    $stmt=$mysqli->prepare($sql);
    $stmt->bind_param('iiiiii',$year,$month,$color_id,$color_id,$finish_id,$finish_id);
    $stmt->execute();
    $res=$stmt->get_result();
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r;
    ca_ok(['data'=>$rows]);
  } break;

  case 'yearly_chart': {
    $year = (int)($_GET['year'] ?? 0);
    if ($year <= 0) ca_err('Invalid year');
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : 0;

    $sql = "SELECT
              YEAR(i.analysis_date) AS y,
              MONTH(i.analysis_date) AS m,
              i.color_id,
              i.finish_id,
              col.name AS color_name,
              fin.name AS finish_name,
              AVG(i.price_per_sqf) AS avg_price
            FROM competitor_items i
            LEFT JOIN colors col ON col.id = i.color_id
            LEFT JOIN finishes fin ON fin.id = i.finish_id
            WHERE YEAR(i.analysis_date) = ?
              AND i.price_per_sqf IS NOT NULL
              AND (? = 0 OR i.finish_id = ?)
            GROUP BY y, m, i.color_id, i.finish_id, col.name, fin.name
            ORDER BY m ASC, col.name ASC, fin.name ASC";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('iii', $year, $finish_id, $finish_id);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($r = $res->fetch_assoc()) $rows[] = $r;
    ca_ok(['data' => $rows]);
  } break;

  default:
    ca_err('Invalid action',400);
}
