<?php
class Order {
    private $pdo; public function __construct(){ $this->pdo=$GLOBALS['pdo']; }
    public function all(){ $sql="SELECT o.*, c.name as customer_name, u.name as assigned_user_name, COALESCE(items.items_count, 0) as items_count FROM orders o JOIN customers c ON c.id=o.customer_id LEFT JOIN users u ON u.id=o.assigned_user_id LEFT JOIN (SELECT order_id, COUNT(DISTINCT product_id) as items_count FROM order_items GROUP BY order_id) items ON items.order_id = o.id ORDER BY o.created_at DESC"; return $this->pdo->query($sql)->fetchAll(); }
    public function filter($f){
        $where=[]; $params=[];
        if(!empty($f['date_from'])){ $where[]='DATE(o.order_date) >= ?'; $params[]=$f['date_from']; }
        if(!empty($f['date_to'])){ $where[]='DATE(o.order_date) <= ?'; $params[]=$f['date_to']; }
        if(!empty($f['customer_id'])){ $where[]='o.customer_id = ?'; $params[]=$f['customer_id']; }
        if(!empty($f['status'])){ $where[]='o.status = ?'; $params[]=$f['status']; }
        if(!empty($f['order_type'])){ $where[]='o.order_type = ?'; $params[]=$f['order_type']; }
        if(isset($f['verified']) && $f['verified']!==''){
            if($f['verified']==='1'){ $where[]='o.confirmed=1'; } else { $where[]='(o.confirmed=0 OR o.confirmed IS NULL)'; }
        }
        $sql="SELECT o.*, c.name as customer_name, u.name as assigned_user_name, COALESCE(items.items_count, 0) as items_count FROM orders o JOIN customers c ON c.id=o.customer_id LEFT JOIN users u ON u.id=o.assigned_user_id LEFT JOIN (SELECT order_id, COUNT(DISTINCT product_id) as items_count FROM order_items GROUP BY order_id) items ON items.order_id = o.id";
        if($where) $sql.=' WHERE '.implode(' AND ',$where);
        $sql.=' ORDER BY o.order_date DESC, o.id DESC';
        $st=$this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll();
    }
    public function find($id){ $st=$this->pdo->prepare("SELECT o.*, c.name as customer_name, u.name as assigned_user_name FROM orders o JOIN customers c ON c.id=o.customer_id LEFT JOIN users u ON u.id=o.assigned_user_id WHERE o.id=?"); $st->execute([$id]); return $st->fetch(); }
    public function items($orderId){ $st=$this->pdo->prepare("SELECT oi.*, p.code, p.name, p.price_unit, p.production_unit FROM order_items oi LEFT JOIN products p ON p.id=oi.product_id WHERE oi.order_id=? ORDER BY oi.id"); $st->execute([$orderId]); return $st->fetchAll(); }
    public function create($data){ $st=$this->pdo->prepare("INSERT INTO orders (customer_id,order_date,delivery_date,order_type,status,notes,signature,assigned_user_id,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,NOW())"); $ok=$st->execute([$data['customer_id'],$data['order_date'],$data['delivery_date']??null,$data['order_type']??'daily',$data['status']??'pending',$data['notes']??null,$data['signature']??null,$data['assigned_user_id']??null,$data['created_by']??null]); if(!$ok) return false; return $this->pdo->lastInsertId(); }
    public function update($id,$data){ $st=$this->pdo->prepare("UPDATE orders SET customer_id=?,order_date=?,delivery_date=?,order_type=?,status=?,notes=?,signature=?,assigned_user_id=?,updated_at=NOW() WHERE id=?"); return $st->execute([$data['customer_id'],$data['order_date'],$data['delivery_date']??null,$data['order_type']??'daily',$data['status']??'pending',$data['notes']??null,$data['signature']??null,$data['assigned_user_id']??null,$id]); }
    // Confirmation now only records audit fields; it no longer auto sets status to 'completed'
    public function confirm($id,$userId){ $st=$this->pdo->prepare("UPDATE orders SET confirmed=1, confirm_user_id=?, confirmed_at=NOW() WHERE id=?"); return $st->execute([$userId,$id]); }
    public function unconfirm($id){ $st=$this->pdo->prepare("UPDATE orders SET confirmed=0, confirm_user_id=NULL, confirmed_at=NULL WHERE id=?"); return $st->execute([$id]); }
    public function delete($id){ $st=$this->pdo->prepare("DELETE FROM orders WHERE id=?"); return $st->execute([$id]); }
    public function replaceItems($orderId,$items){ $this->pdo->beginTransaction(); try { $this->pdo->prepare("DELETE FROM order_items WHERE order_id=?")->execute([$orderId]); if($items){ $ins=$this->pdo->prepare("INSERT INTO order_items (order_id,product_id,quantity,unit_price,product_name_snapshot,product_code_snapshot) VALUES (?,?,?,?,?,?)"); foreach($items as $it){ $ins->execute([$orderId,$it['product_id'],$it['quantity'],$it['unit_price'],$it['product_name_snapshot'],$it['product_code_snapshot']]); } } $this->pdo->commit(); return true; } catch(Exception $e){ $this->pdo->rollBack(); return false; } }
    public function getDistinctOrderTypes(){ $sql="SELECT DISTINCT order_type FROM orders WHERE order_type IS NOT NULL AND order_type != '' ORDER BY order_type"; return $this->pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN); }
    public function getLatest($limit = 5) {
        $limit = max(1, min(50, (int)$limit));
        $sql = "SELECT o.id, o.order_date, o.delivery_date, o.order_type, o.status, c.name as customer_name, u.name as assigned_user_name, 
                COALESCE(items.items_count, 0) as items_count
                FROM orders o 
                JOIN customers c ON c.id = o.customer_id 
                LEFT JOIN users u ON u.id = o.assigned_user_id 
                LEFT JOIN (SELECT order_id, COUNT(DISTINCT product_id) as items_count FROM order_items GROUP BY order_id) items ON items.order_id = o.id 
                ORDER BY o.created_at DESC 
                LIMIT ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
}
