<?php
class Product extends Model {
    protected static string $table = 'products';

    public static function search(string $q, int $limit = 20): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT p.*, c.name as category_name, b.name as brand_name,
                    COALESCE(SUM(s.quantity),0) as total_stock
             FROM products p
             LEFT JOIN categories c ON c.id=p.category_id
             LEFT JOIN brands b ON b.id=p.brand_id
             LEFT JOIN stock s ON s.product_id=p.id AND s.tenant_id=p.tenant_id
             WHERE p.tenant_id=? AND p.is_active=1
               AND (p.name LIKE ? OR p.sku LIKE ? OR p.barcode LIKE ?)
             GROUP BY p.id LIMIT ?"
        );
        $like = "%{$q}%";
        $stmt->execute([$tid,$like,$like,$like,$limit]);
        return $stmt->fetchAll();
    }

    public static function withStock(int $id): ?array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT p.*, c.name as category_name, b.name as brand_name
             FROM products p
             LEFT JOIN categories c ON c.id=p.category_id
             LEFT JOIN brands b ON b.id=p.brand_id
             WHERE p.id=? AND p.tenant_id=? LIMIT 1"
        );
        $stmt->execute([$id,$tid]);
        $product = $stmt->fetch();
        if (!$product) return null;

        $stock = db()->prepare(
            "SELECT s.*, l.name as location_name
             FROM stock s JOIN locations l ON l.id=s.location_id
             WHERE s.product_id=? AND s.tenant_id=?"
        );
        $stock->execute([$id,$tid]);
        $product['stock'] = $stock->fetchAll();
        return $product;
    }

    public static function getBatches(int $productId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT b.*, l.name as location_name
             FROM batches b
             LEFT JOIN locations l ON l.id=b.location_id
             WHERE b.product_id=? AND b.tenant_id=? 
             ORDER BY b.mfg_date DESC, b.batch_no DESC"
        );
        $stmt->execute([$productId, $tid]);
        return $stmt->fetchAll();
    }

    public static function getSerialNumbers(int $productId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT sn.*, l.name as location_name
             FROM serial_numbers sn
             LEFT JOIN locations l ON l.id=sn.location_id
             WHERE sn.product_id=? AND sn.tenant_id=? 
             ORDER BY sn.created_at DESC"
        );
        $stmt->execute([$productId, $tid]);
        return $stmt->fetchAll();
    }

    public static function enableBatchTracking(int $productId): bool {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "UPDATE products SET batch_tracking=1 WHERE id=? AND tenant_id=?"
        );
        return $stmt->execute([$productId, $tid]);
    }

    public static function enableSerialTracking(int $productId): bool {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "UPDATE products SET serial_tracking=1 WHERE id=? AND tenant_id=?"
        );
        return $stmt->execute([$productId, $tid]);
    }

    public static function disableBatchTracking(int $productId): bool {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "UPDATE products SET batch_tracking=0 WHERE id=? AND tenant_id=?"
        );
        return $stmt->execute([$productId, $tid]);
    }

    public static function disableSerialTracking(int $productId): bool {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "UPDATE products SET serial_tracking=0 WHERE id=? AND tenant_id=?"
        );
        return $stmt->execute([$productId, $tid]);
    }

    public static function lowStock(): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT p.*, COALESCE(SUM(s.quantity),0) as total_stock, l.name as location_name
             FROM products p
             LEFT JOIN stock s ON s.product_id=p.id AND s.tenant_id=p.tenant_id
             LEFT JOIN locations l ON l.id=s.location_id
             WHERE p.tenant_id=? AND p.is_active=1
             GROUP BY p.id
             HAVING total_stock <= p.alert_qty"
        );
        $stmt->execute([$tid]);
        return $stmt->fetchAll();
    }

    public static function expiringBatches(int $days = 30): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT p.name as product_name, b.batch_no, b.expiry_date,
                    DATEDIFF(b.expiry_date,CURDATE()) as days_left,
                    COALESCE(SUM(s.quantity),0) as qty
             FROM batches b
             JOIN products p ON p.id=b.product_id
             LEFT JOIN stock s ON s.batch_id=b.id AND s.tenant_id=b.tenant_id
             WHERE b.tenant_id=? AND b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL ? DAY)
             GROUP BY b.id ORDER BY b.expiry_date ASC"
        );
        $stmt->execute([$tid,$days]);
        return $stmt->fetchAll();
    }

    public static function listPaginated(int $page=1, string $search='', ?int $categoryId=null, ?int $brandId=null, string $status='', ?int $locationId=null): array {
        $tid    = static::tenantId();
        $where  = "p.tenant_id = ?";
        $params = [$tid];
        if (!empty($search)) { $where .= " AND (p.name LIKE ? OR p.sku LIKE ?)"; $params[] = "%{$search}%"; $params[] = "%{$search}%"; }
        if ($categoryId !== null && $categoryId > 0) { $where .= " AND p.category_id = ?"; $params[] = $categoryId; }
        if ($brandId !== null && $brandId > 0) { $where .= " AND p.brand_id = ?"; $params[] = $brandId; }
        if (!empty($status)) { $where .= " AND p.is_active = ?"; $params[] = (int)$status; }
        else { $where .= " AND p.is_active = 1"; }

        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset  = ($page-1)*$pp;

        // Build the count query
        $countSql = "SELECT COUNT(*) FROM products p WHERE {$where}";
        try {
            $count   = db()->prepare($countSql);
            $count->execute($params);
            $total   = (int)$count->fetchColumn();
        } catch (PDOException $e) {
            error_log("Product listPaginated count error: " . $e->getMessage());
            error_log("SQL: $countSql, params: " . json_encode($params));
            throw $e;
        }

        // Build the main query using a LEFT JOIN derived table for stock.
        // This avoids the positional parameter mismatch that occurs with subqueries
        // where the location filter placeholder appears before the WHERE placeholders.
        // With the derived table, stock filter params come first in SQL order,
        // matching the natural array build order — no array_unshift needed.
        $stockParams = [$tid];
        $stockFilter = "WHERE s.tenant_id = ?";
        if ($locationId !== null && $locationId > 0) { $stockFilter .= " AND s.location_id = ?"; $stockParams[] = $locationId; }

        $mainParams = array_merge($stockParams, $params);
        $sql = "SELECT p.*,c.name as category_name,b.name as brand_name,
                       COALESCE(sq.stock_sum, 0) as total_stock
                FROM products p
                LEFT JOIN categories c ON c.id=p.category_id
                LEFT JOIN brands b ON b.id=p.brand_id
                LEFT JOIN (
                    SELECT s.product_id, SUM(s.quantity) as stock_sum
                    FROM stock s
                    {$stockFilter}
                    GROUP BY s.product_id
                ) sq ON sq.product_id = p.id
                WHERE {$where} ORDER BY p.name ASC LIMIT {$pp} OFFSET {$offset}";
        try {
            $stmt = db()->prepare($sql);
            $stmt->execute($mainParams);
        } catch (PDOException $e) {
            error_log("Product listPaginated main error: " . $e->getMessage());
            error_log("SQL: $sql, params: " . json_encode($mainParams));
            throw $e;
        }
        return ['data'=>$stmt->fetchAll(),'total'=>$total,'last_page'=>$perPage === 'all' ? 1 : (int)ceil($total/$pp),'page'=>$page];
    }

    public static function getMovementHistory(int $productId, int $limit = 10): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT sm.type, sm.quantity, sm.reference, sm.created_at, sm.user_id, u.name as user_name,
                    sm.location_id, l.name as location_name,
                    CASE 
                        WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN 'in'
                        WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN 'out'
                        ELSE 'other'
                    END as direction
             FROM stock_movements sm
             LEFT JOIN users u ON u.id = sm.user_id
             LEFT JOIN locations l ON l.id = sm.location_id
             WHERE sm.product_id = ? AND sm.tenant_id = ?
             ORDER BY sm.created_at DESC
             LIMIT ?"
        );
        $stmt->execute([$productId, $tid, $limit]);
        return $stmt->fetchAll();
    }

    public static function getMovementSummary(int $productId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT
                COALESCE(SUM(CASE WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN sm.quantity ELSE 0 END), 0) as total_in,
                COALESCE(SUM(CASE WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN sm.quantity ELSE 0 END), 0) as total_out,
                COALESCE(SUM(sm.quantity), 0) as balance
             FROM stock_movements sm
             WHERE sm.product_id = ? AND sm.tenant_id = ?"
        );
        $stmt->execute([$productId, $tid]);
        $result = $stmt->fetch();

        $totalIn = (float)($result['total_in'] ?? 0);
        $totalOut = (float)($result['total_out'] ?? 0);
        // total_out is negative, so we display it as positive with a minus sign
        $displayTotalOut = abs($totalOut);
        $calculatedBalance = (float)($result['balance'] ?? 0);

        return [
            'total_in' => $totalIn,
            'total_out' => $displayTotalOut,
            'balance' => $calculatedBalance
        ];
    }

    public static function getMovementSummaryByLocation(int $productId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT sm.location_id, l.name as location_name,
                    COALESCE(SUM(CASE WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in') OR (sm.type IN ('adjustment', 'return') AND sm.quantity > 0) THEN sm.quantity ELSE 0 END), 0) as total_in,
                    COALESCE(SUM(CASE WHEN sm.type IN ('sale', 'transfer_out', 'adjustment_out') OR (sm.type IN ('adjustment', 'return') AND sm.quantity < 0) THEN sm.quantity ELSE 0 END), 0) as total_out,
                    COALESCE(SUM(sm.quantity), 0) as balance
             FROM stock_movements sm
             LEFT JOIN locations l ON l.id = sm.location_id
             WHERE sm.product_id = ? AND sm.tenant_id = ?
             GROUP BY sm.location_id, l.name
             ORDER BY l.name"
        );
        $stmt->execute([$productId, $tid]);
        return $stmt->fetchAll();
    }
}
