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

if (!function_exists('json_response')) {
  function json_response($data,$code=200){ http_response_code($code); echo json_encode($data); exit; }
}
if (!function_exists('ensure_admin_prod')) {
  function ensure_admin_prod(){ if (!has_role(['admin','super_admin'])) json_response(['message'=>'Forbidden'], 403); }
}

// Branch helpers
if (!function_exists('sess_branch_id')) {
  function sess_branch_id(){ return $_SESSION['branch_id'] ?? null; }
}
if (!function_exists('is_admin_user')) {
  function is_admin_user(){ return has_role(['admin','super_admin']); }
}

function ensure_prod_schema(mysqli $db){
  // types
  $db->query("CREATE TABLE IF NOT EXISTS production_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  // qualities
  $db->query("CREATE TABLE IF NOT EXISTS production_qualities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  // items (fresh create references product_types for type FK)
  $db->query("CREATE TABLE IF NOT EXISTS production_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_id INT NOT NULL,
    quality_id INT NULL,
    name VARCHAR(150) NULL,
    color_id INT NULL,
    finish_id INT NULL,
    length DECIMAL(10,2) NOT NULL,
    width DECIMAL(10,2) NOT NULL,
    thickness DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    availability ENUM('available','reserved','sold') NOT NULL DEFAULT 'available',
    notes VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pi_type FOREIGN KEY (type_id) REFERENCES product_types(id),
    CONSTRAINT fk_pi_quality FOREIGN KEY (quality_id) REFERENCES production_qualities(id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  // Add missing columns in legacy DBs (without IF NOT EXISTS for MySQL 5.7 compatibility)
  $resN = $db->query("SHOW COLUMNS FROM production_items LIKE 'name'");
  if (!$resN || !$resN->fetch_assoc()) { @$db->query("ALTER TABLE production_items ADD COLUMN name VARCHAR(150) NULL AFTER quality_id"); }
  $resC = $db->query("SHOW COLUMNS FROM production_items LIKE 'color_id'");
  if (!$resC || !$resC->fetch_assoc()) { @$db->query("ALTER TABLE production_items ADD COLUMN color_id INT NULL AFTER name"); }
  $resF = $db->query("SHOW COLUMNS FROM production_items LIKE 'finish_id'");
  if (!$resF || !$resF->fetch_assoc()) { @$db->query("ALTER TABLE production_items ADD COLUMN finish_id INT NULL AFTER color_id"); }
  // Ensure quality_id is nullable
  @$db->query("ALTER TABLE production_items MODIFY COLUMN quality_id INT NULL");
  // Ensure branch_id column exists
  $resB = $db->query("SHOW COLUMNS FROM production_items LIKE 'branch_id'");
  if (!$resB || !$resB->fetch_assoc()) { @$db->query("ALTER TABLE production_items ADD COLUMN branch_id INT NULL AFTER notes"); }
  // Make sure type_id references product_types table
  // Check if FK fk_pi_type exists
  $dbNameRes = $db->query("SELECT DATABASE() AS db");
  $dbRow = $dbNameRes ? $dbNameRes->fetch_assoc() : null;
  $dbName = $dbRow ? $dbRow['db'] : '';
  $fkRow = $db->query(
  "SELECT 1 FROM information_schema.TABLE_CONSTRAINTS 
      WHERE CONSTRAINT_SCHEMA='".$db->real_escape_string($dbName)."' 
        AND TABLE_NAME='production_items' 
        AND CONSTRAINT_NAME='fk_pi_type' 
        AND CONSTRAINT_TYPE='FOREIGN KEY'"
  );
  $fkExists = $fkRow && $fkRow->fetch_assoc();
  if (!$fkExists) {
    // Add FK only if missing
    @$db->query("ALTER TABLE production_items ADD CONSTRAINT fk_pi_type FOREIGN KEY (type_id) REFERENCES product_types(id)");
  }
}

ensure_prod_schema($mysqli);

switch ($action) {
  case 'list': {
    $q = trim($_GET['q'] ?? '');
    $id = (int)($_GET['id'] ?? 0);
    $type_id = (int)($_GET['type_id'] ?? 0);
    $quality_id = (int)($_GET['quality_id'] ?? 0);
    $color_id = (int)($_GET['color_id'] ?? 0);
    $finish_id = (int)($_GET['finish_id'] ?? 0);
    $availability = trim($_GET['availability'] ?? '');
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;
    $where=[]; $types=''; $params=[];
    if ($id){ $where[]='pi.id=?'; $types.='i'; $params[]=$id; }
    if ($q !== ''){ $where[]='(pi.notes LIKE CONCAT("%", ?, "%") OR pi.name LIKE CONCAT("%", ?, "%"))'; $types.='ss'; $params[]=$q; $params[]=$q; }
    if ($type_id){ $where[]='pi.type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($quality_id){ $where[]='pi.quality_id=?'; $types.='i'; $params[]=$quality_id; }
    if ($color_id){ $where[]='pi.color_id=?'; $types.='i'; $params[]=$color_id; }
    if ($finish_id){ $where[]='pi.finish_id=?'; $types.='i'; $params[]=$finish_id; }
    if ($availability !== '' && in_array($availability,['available','reserved','sold'],true)){ $where[]='pi.availability=?'; $types.='s'; $params[]=$availability; }
    // Branch scope
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0); $where[]='(pi.branch_id = ? OR pi.branch_id IS NULL)'; $types.='i'; $params[]=$bid;
    } else if (!empty($branch_id)) { $where[]='pi.branch_id = ?'; $types.='i'; $params[]=$branch_id; }
    $sql = 'SELECT pi.*, pt.name AS type_name, pq.name AS quality_name, c.name AS color_name, f.name AS finish_name, b.name AS branch_name
            FROM production_items pi
            LEFT JOIN product_types pt ON pt.id=pi.type_id
            LEFT JOIN production_qualities pq ON pq.id=pi.quality_id
            LEFT JOIN colors c ON c.id=pi.color_id
            LEFT JOIN finishes f ON f.id=pi.finish_id
            LEFT JOIN branches b ON b.id=pi.branch_id';
    if ($where) $sql .= ' WHERE '.implode(' AND ',$where);
    $sql .= ' ORDER BY pi.id DESC';
    if ($where){ $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 'lookups': {
    // Only return qualities/colors/finishes for Production page filters.
    $quals=[]; if ($res2=$mysqli->query('SELECT id,name FROM production_qualities ORDER BY name')){ while($r2=$res2->fetch_assoc()){ $quals[]=$r2; } }
    $colors=[]; if ($res3=$mysqli->query('SELECT id,name FROM colors ORDER BY name')){ while($r3=$res3->fetch_assoc()){ $colors[]=$r3; } }
    $finishes=[]; if ($res4=$mysqli->query('SELECT id,name FROM finishes ORDER BY name')){ while($r4=$res4->fetch_assoc()){ $finishes[]=$r4; } }
    json_response(['qualities'=>$quals,'colors'=>$colors,'finishes'=>$finishes]);
  } break;

  case 'create': {
    ensure_admin_prod();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $type_id=(int)($_POST['type_id'] ?? 0);
    $quality_id=(int)($_POST['quality_id'] ?? 0);
    $name=trim($_POST['name'] ?? '');
    $color_id = (int)($_POST['color_id'] ?? 0);
    $finish_id = (int)($_POST['finish_id'] ?? 0);
    $length=(float)($_POST['length'] ?? 0);
    $width=(float)($_POST['width'] ?? 0);
    $thickness=(float)($_POST['thickness'] ?? 0);
    $quantity=(int)($_POST['quantity'] ?? 0);
    $availability=trim($_POST['availability'] ?? 'available');
    $notes=trim($_POST['notes'] ?? '');
    if (!$type_id || $length<=0 || $width<=0 || $thickness<=0 || $quantity<=0) json_response(['message'=>'Invalid input'],400);
    // Branch assignment: admin can specify branch_id, else use current session
    $branch_id = isset($_POST['branch_id']) && is_admin_user() ? (int)$_POST['branch_id'] : (int)(sess_branch_id() ?? 0);
    $stmt=$mysqli->prepare('INSERT INTO production_items (type_id,quality_id,name,color_id,finish_id,length,width,thickness,quantity,availability,notes,branch_id) VALUES (?,NULLIF(?,0),?,?,?,?,?,?,?,?,?,?)');
    $stmt->bind_param('iisiidddissi',$type_id,$quality_id,$name,$color_id,$finish_id,$length,$width,$thickness,$quantity,$availability,$notes,$branch_id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Created','id'=>$mysqli->insert_id]);
  } break;

  case 'update': {
    ensure_admin_prod();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0);
    $type_id=(int)($_POST['type_id'] ?? 0);
    $quality_id=(int)($_POST['quality_id'] ?? 0);
    $name=trim($_POST['name'] ?? '');
    $color_id = (int)($_POST['color_id'] ?? 0);
    $finish_id = (int)($_POST['finish_id'] ?? 0);
    $length=(float)($_POST['length'] ?? 0);
    $width=(float)($_POST['width'] ?? 0);
    $thickness=(float)($_POST['thickness'] ?? 0);
    $quantity=(int)($_POST['quantity'] ?? 0);
    $availability=trim($_POST['availability'] ?? 'available');
    $notes=trim($_POST['notes'] ?? '');
    $branch_id = isset($_POST['branch_id']) && is_admin_user() ? (int)$_POST['branch_id'] : (int)(sess_branch_id() ?? 0);
    if(!$id) json_response(['message'=>'Missing id'],400);
    if (!$type_id || $length<=0 || $width<=0 || $thickness<=0 || $quantity<=0) json_response(['message'=>'Invalid input'],400);
    $stmt=$mysqli->prepare('UPDATE production_items SET type_id=?, quality_id=NULLIF(?,0), name=?, color_id=?, finish_id=?, length=?, width=?, thickness=?, quantity=?, availability=?, notes=?, branch_id=? WHERE id=?');
    $stmt->bind_param('iisiidddissii',$type_id,$quality_id,$name,$color_id,$finish_id,$length,$width,$thickness,$quantity,$availability,$notes,$branch_id,$id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Updated']);
  } break;

  case 'delete': {
    ensure_admin_prod();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) json_response(['message'=>'Missing id'],400);
    $mysqli->query('DELETE FROM production_items WHERE id='.(int)$id);
    json_response(['message'=>'Deleted']);
  } break;

  case 'set_availability': {
    ensure_admin_prod();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); $availability=trim($_POST['availability'] ?? 'available');
    if(!$id || !in_array($availability,['available','reserved','sold'],true)) json_response(['message'=>'Invalid input'],400);
    $stmt=$mysqli->prepare('UPDATE production_items SET availability=? WHERE id=?');
    $stmt->bind_param('si',$availability,$id); $stmt->execute();
    json_response(['message'=>'Updated']);
  } break;

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