<?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)'; }
        }
        if(!empty($f['customer_type'])){ $where[]='c.type = ?'; $params[]=$f['customer_type']; }
        $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 filterPaginated($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)'; }
        }
        if(!empty($f['customer_type'])){ $where[]='c.type = ?'; $params[]=$f['customer_type']; }
        $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';
        
        // Add pagination
        if (isset($f['per_page']) && isset($f['page'])) {
            $offset = ($f['page'] - 1) * $f['per_page'];
            $sql .= ' LIMIT ? OFFSET ?';
            $params[] = $f['per_page'];
            $params[] = $offset;
        }
        
        $st=$this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll();
    }
    
    public function getFilterCount($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)'; }
        }
        if(!empty($f['customer_type'])){ $where[]='c.type = ?'; $params[]=$f['customer_type']; }
        $sql="SELECT COUNT(DISTINCT o.id) as count FROM orders o JOIN customers c ON c.id=o.customer_id LEFT JOIN users u ON u.id=o.assigned_user_id";
        if($where) $sql.=' WHERE '.implode(' AND ',$where);
        $st=$this->pdo->prepare($sql); $st->execute($params); 
        $result = $st->fetch();
        return (int)$result['count'];
    }
    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){ 
        // Handle cake orders
        if (!empty($data['is_cake_order'])) {
            $st = $this->pdo->prepare("INSERT INTO orders (customer_id, order_date, is_cake_order, pickup_date, pickup_time, pickup_location_id, delivery_address, payment_method, deposit_amount, status, created_by, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW())"); 
            $ok = $st->execute([
                $data['customer_id'],
                $data['pickup_date'] ?? date('Y-m-d'), // Use pickup_date as order_date
                1,
                $data['pickup_date'] ?? null,
                $data['pickup_time'] ?? null,
                $data['pickup_location_id'] ?? null,
                $data['delivery_address'] ?? null,
                $data['payment_method'] ?? 'on_pickup',
                $data['deposit_amount'] ?? null,
                $data['status'] ?? 'pending',
                $data['created_by'] ?? null
            ]);
        } else {
            // Regular orders
            $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'] ?? date('Y-m-d'),
                $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){ 
        // Handle cake orders
        if (isset($data['pickup_date']) || isset($data['is_cake_order'])) {
            $st = $this->pdo->prepare("UPDATE orders SET customer_id=?, order_date=?, pickup_date=?, pickup_time=?, pickup_location_id=?, delivery_address=?, payment_method=?, deposit_amount=?, status=?, updated_at=NOW() WHERE id=?"); 
            return $st->execute([
                $data['customer_id'],
                $data['pickup_date'] ?? date('Y-m-d'), // Use pickup_date as order_date
                $data['pickup_date'] ?? null,
                $data['pickup_time'] ?? null,
                $data['pickup_location_id'] ?? null,
                $data['delivery_address'] ?? null,
                $data['payment_method'] ?? 'on_pickup',
                $data['deposit_amount'] ?? null,
                $data['status'] ?? 'pending',
                $id
            ]);
        } else {
            // Regular orders
            $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'] ?? date('Y-m-d'),
                $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();
    }
    
    public function getCakeOrders($filters = []) {
        $where = ['o.is_cake_order = 1'];
        $params = [];
        
        if (!empty($filters['status'])) {
            $where[] = 'o.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'o.pickup_date >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'o.pickup_date <= ?';
            $params[] = $filters['date_to'];
        }
        
        try {
            $sql = "SELECT o.*, 
                    c.name as customer_name, 
                    c.phone as customer_phone,
                    cd.flavor_id, cd.flavor_custom,
                    cd.size_id, cd.size_custom,
                    cf.name as flavor_name,
                    cs.name as size_name
                    FROM orders o
                    JOIN customers c ON c.id = o.customer_id
                    LEFT JOIN cake_order_details cd ON cd.order_id = o.id
                    LEFT JOIN cake_flavors cf ON cf.id = cd.flavor_id
                    LEFT JOIN cake_sizes cs ON cs.id = cd.size_id
                    WHERE " . implode(' AND ', $where) . "
                    ORDER BY o.pickup_date DESC, o.pickup_time DESC, o.created_at DESC";
            
            // Add pagination
            if (isset($filters['limit'])) {
                $sql .= " LIMIT " . (int)$filters['limit'];
                if (isset($filters['offset'])) {
                    $sql .= " OFFSET " . (int)$filters['offset'];
                }
            }
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            // If table doesn't exist, return empty array
            error_log("getCakeOrders error: " . $e->getMessage());
            return [];
        }
    }
    
    public function getCakeOrdersCount($filters = []) {
        $where = ['o.is_cake_order = 1'];
        $params = [];
        
        if (!empty($filters['status'])) {
            $where[] = 'o.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'o.pickup_date >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'o.pickup_date <= ?';
            $params[] = $filters['date_to'];
        }
        
        try {
            $sql = "SELECT COUNT(DISTINCT o.id) as count
                    FROM orders o
                    WHERE " . implode(' AND ', $where);
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            $result = $stmt->fetch();
            return (int)$result['count'];
        } catch (PDOException $e) {
            error_log("getCakeOrdersCount error: " . $e->getMessage());
            return 0;
        }
    }
    
    public function getCakeOrderComplete($id) {
        try {
            $query = "
                SELECT 
                    o.*,
                    c.name as customer_name,
                    c.phone as customer_phone,
                    c.email as customer_email,
                    cod.flavor_id,
                    cod.flavor_custom,
                    cod.size_id,
                    cod.size_custom,
                    cod.cake_type_ids,
                    cod.cake_type_custom,
                    cod.has_inscription,
                    cod.inscription_text as inscription,
                    cod.has_candles,
                    cod.candles_count,
                    cod.has_number_candle,
                    cod.number_candle_value,
                    cod.has_sparkler,
                    cod.has_wafer_photo,
                    cod.wafer_file as wafer_image,
                    cod.has_card,
                    cod.card_text,
                    cod.card_pattern,
                    cod.has_figurine,
                    cod.figurine_description,
                    cod.reference_photo as cake_image,
                    cod.notes,
                    cod.base_price,
                    cod.extras_price,
                    cod.total_price,
                    cf.name as flavor_name,
                    cs.name as size_name,
                    pl.name as pickup_location_name,
                    pl.address as pickup_location_custom
                FROM orders o
                LEFT JOIN customers c ON c.id = o.customer_id
                LEFT JOIN cake_order_details cod ON cod.order_id = o.id
                LEFT JOIN cake_flavors cf ON cf.id = cod.flavor_id
                LEFT JOIN cake_sizes cs ON cs.id = cod.size_id
                LEFT JOIN pickup_locations pl ON pl.id = o.pickup_location_id
                WHERE o.id = ? AND o.is_cake_order = 1
            ";
            
            $stmt = $this->pdo->prepare($query);
            $stmt->execute([$id]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Parse cake_type_ids to get type names
            if ($result && !empty($result['cake_type_ids'])) {
                $typeIds = json_decode($result['cake_type_ids'], true);
                if (is_array($typeIds) && count($typeIds) > 0) {
                    $placeholders = str_repeat('?,', count($typeIds) - 1) . '?';
                    $typeQuery = "SELECT name FROM cake_types WHERE id IN ($placeholders)";
                    $typeStmt = $this->pdo->prepare($typeQuery);
                    $typeStmt->execute($typeIds);
                    $types = $typeStmt->fetchAll(PDO::FETCH_COLUMN);
                    $result['cake_type_name'] = implode(', ', $types);
                } else {
                    $result['cake_type_name'] = null;
                }
            } else {
                $result['cake_type_name'] = null;
            }
            
            return $result;
        } catch (PDOException $e) {
            error_log("getCakeOrderComplete error: " . $e->getMessage());
            return null;
        }
    }
}


