<?php
class Product {
    private $pdo; private static $hasStatus=false; private static $schemaChecked=false;
    public function __construct(){ $this->pdo=$GLOBALS['pdo']; }
    private function check(){ if(self::$schemaChecked) return; try { $cols=$this->pdo->query("DESCRIBE products")->fetchAll(PDO::FETCH_COLUMN); self::$hasStatus=in_array('status',$cols,true); } catch(Exception $e) { self::$hasStatus=false; } self::$schemaChecked=true; }
    public function getColumns(){ try { return $this->pdo->query("DESCRIBE products")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ return []; } }
    public function search($q='', $category=null, $status='all', $limit=50, $offset=0, $sort='name', $dir='asc'){
        $this->check(); $where=[]; $params=[]; if($q!==''){ $where[]='(p.name LIKE ? OR p.code LIKE ?)'; $params[]="%$q%"; $params[]="%$q%"; }
        if($category){ $where[]='p.category_id=?'; $params[]=$category; }
        if($status!=='all' && self::$hasStatus){ $where[]='p.status=?'; $params[]=$status; }
        $ws=$where?('WHERE '.implode(' AND ',$where)) : '';
        $allowed=['code'=>'p.code','name'=>'p.name','price'=>'p.price','status'=>'p.status','sort_order'=>'p.sort_order','category'=>'c.name'];
        $orderCol = $allowed[$sort] ?? 'p.name'; $dir = strtolower($dir)==='desc'?'DESC':'ASC';
        $sql="SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id=c.id $ws ORDER BY $orderCol $dir, p.id ASC LIMIT $limit OFFSET $offset";
        $st=$this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll();
    }
    public function countAll($q='', $category=null, $status='all'){
        $this->check(); $where=[]; $params=[]; if($q!==''){ $where[]='(name LIKE ? OR code LIKE ?)'; $params[]="%$q%"; $params[]="%$q%"; }
        if($category){ $where[]='category_id=?'; $params[]=$category; }
        if($status!=='all' && self::$hasStatus){ $where[]='status=?'; $params[]=$status; }
        $ws=$where?('WHERE '.implode(' AND ',$where)) : '';
        $st=$this->pdo->prepare("SELECT COUNT(*) FROM products $ws"); $st->execute($params); return (int)$st->fetchColumn();
    }
    public function updateSortOrderBulk($orders){
        $st=$this->pdo->prepare("UPDATE products SET sort_order=?, updated_at=NOW() WHERE id=?");
        $this->pdo->beginTransaction();
        try { foreach($orders as $id=>$ord){ $st->execute([$ord,$id]); } $this->pdo->commit(); return true; } catch(Exception $e){ $this->pdo->rollBack(); return false; }
    }
    public function codeExists($code,$excludeId=null){ $sql="SELECT id FROM products WHERE code=?"; $params=[$code]; if($excludeId){ $sql.=" AND id<>?"; $params[]=$excludeId; } $st=$this->pdo->prepare($sql); $st->execute($params); return (bool)$st->fetchColumn(); }
    public function updatePartial($id,$data){
        if(!$data) return false; $cols=[]; $params=[]; foreach($data as $k=>$v){ $cols[]="$k=?"; $params[]=$v; }
        $params[]=$id; $sql="UPDATE products SET ".implode(',', $cols).", updated_at=NOW() WHERE id=?"; $st=$this->pdo->prepare($sql); return $st->execute($params);
    }
    public function find($id){ $st=$this->pdo->prepare("SELECT * FROM products WHERE id=?"); $st->execute([$id]); return $st->fetch(); }
    public function create($data){ $this->check(); $st=$this->pdo->prepare("INSERT INTO products (code,name,description,category_id,price,price_unit,notes,production_site,factor,production_unit,report_flags,sort_order,status,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())"); return $st->execute([
        $data['code'],$data['name'],$data['description']??'', $data['category_id']??null, $data['price']??null, $data['price_unit']??null, $data['notes']??'', $data['production_site']??null, $data['factor']??null, $data['production_unit']??null, json_encode($data['report_flags']??[]), $data['sort_order']??0, $data['status']??'active'
    ]); }
    public function update($id,$data){ $this->check(); $st=$this->pdo->prepare("UPDATE products SET code=?, name=?, description=?, category_id=?, price=?, price_unit=?, notes=?, production_site=?, factor=?, production_unit=?, report_flags=?, sort_order=?, status=?, updated_at=NOW() WHERE id=?"); return $st->execute([
        $data['code'],$data['name'],$data['description']??'', $data['category_id']??null, $data['price']??null, $data['price_unit']??null, $data['notes']??'', $data['production_site']??null, $data['factor']??null, $data['production_unit']??null, json_encode($data['report_flags']??[]), $data['sort_order']??0, $data['status']??'active', $id
    ]); }
    public function delete($id){ $st=$this->pdo->prepare("DELETE FROM products WHERE id=?"); return $st->execute([$id]); }

    // Find by unique code
    public function findByCode($code){ $st=$this->pdo->prepare("SELECT * FROM products WHERE code=? LIMIT 1"); $st->execute([$code]); return $st->fetch(); }

    // Upsert by code (update existing, else create). Returns id.
    public function upsertByCode($code,$data){
        $existing = $this->findByCode($code);
        if($existing){
            $this->update($existing['id'],$data);
            return $existing['id'];
        }
        $this->create($data);
        return $this->pdo->lastInsertId();
    }
}

