<?php
class StockTaking extends Model {
    protected static string $table = 'stock_takings';

    public static function generateReference(): string {
        $tid = static::tenantId();
        $prefix = 'ST-' . date('Ymd');
        $stmt = db()->prepare(
            "SELECT COUNT(*) FROM stock_takings WHERE tenant_id=? AND reference LIKE ?"
        );
        $stmt->execute([$tid, $prefix . '%']);
        $count = (int)$stmt->fetchColumn() + 1;
        return $prefix . str_pad($count, 4, '0', STR_PAD_LEFT);
    }

    public static function createSession(string $mode, int $locationId, ?string $notes = null): int {
        $tid = static::tenantId();
        $reference = self::generateReference();
        $userId = Auth::userId();

        $stmt = db()->prepare(
            "INSERT INTO stock_takings (tenant_id, reference, mode, location_id, notes, user_id) 
             VALUES (?, ?, ?, ?, ?, ?)"
        );
        $stmt->execute([$tid, $reference, $mode, $locationId, $notes, $userId]);
        return (int)db()->lastInsertId();
    }

    public static function getProductsForStockTaking(int $stockTakingId, string $mode, int $locationId): array {
        $tid = static::tenantId();
        
        if ($mode === 'category') {
            // Group by category
            $stmt = db()->prepare(
                "SELECT p.id, p.name, p.sku, p.cost_price as unit_cost, c.id as category_id, c.name as category_name,
                        COALESCE(SUM(s.quantity), 0) as current_stock
                 FROM products p
                 LEFT JOIN categories c ON c.id = p.category_id
                 LEFT JOIN stock s ON s.product_id = p.id AND s.location_id = ? AND s.tenant_id = p.tenant_id
                 WHERE p.tenant_id = ? AND p.is_active = 1
                 GROUP BY c.id, p.id
                 ORDER BY c.name, p.name"
            );
            $stmt->execute([$locationId, $tid]);
        } else {
            // Item wise (no grouping)
            $stmt = db()->prepare(
                "SELECT p.id, p.name, p.sku, p.cost_price as unit_cost, c.id as category_id, c.name as category_name,
                        COALESCE(SUM(s.quantity), 0) as current_stock
                 FROM products p
                 LEFT JOIN categories c ON c.id = p.category_id
                 LEFT JOIN stock s ON s.product_id = p.id AND s.location_id = ? AND s.tenant_id = p.tenant_id
                 WHERE p.tenant_id = ? AND p.is_active = 1
                 GROUP BY p.id
                 ORDER BY p.name"
            );
            $stmt->execute([$locationId, $tid]);
        }
        
        return $stmt->fetchAll();
    }

    public static function saveItem(int $stockTakingId, int $productId, int $categoryId, float $currentStock, float $countedQty, float $unitCost): int {
        $tid = static::tenantId();
        $countedQty = max(0, $countedQty);
        $difference = $countedQty - $currentStock;
        $totalAmount = abs($difference) * $unitCost;
        
        if ($difference < 0) {
            $status = 'shortage';
        } elseif ($difference > 0) {
            $status = 'over';
        } else {
            $status = 'equal';
        }

        // Check if item already exists
        $checkStmt = db()->prepare(
            "SELECT id FROM stock_taking_items WHERE stock_taking_id = ? AND product_id = ? AND tenant_id = ?"
        );
        $checkStmt->execute([$stockTakingId, $productId, $tid]);
        $existing = $checkStmt->fetch();

        if ($existing) {
            // Update existing
            $stmt = db()->prepare(
                "UPDATE stock_taking_items 
                 SET current_stock = ?, counted_qty = ?, difference = ?, status = ?, unit_cost = ?, total_amount = ?, updated_at = CURRENT_TIMESTAMP
                 WHERE id = ?"
            );
            $stmt->execute([$currentStock, $countedQty, $difference, $status, $unitCost, $totalAmount, $existing['id']]);
            return (int)$existing['id'];
        } else {
            // Insert new
            $stmt = db()->prepare(
                "INSERT INTO stock_taking_items (stock_taking_id, tenant_id, product_id, category_id, current_stock, counted_qty, difference, status, unit_cost, total_amount)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $stmt->execute([$stockTakingId, $tid, $productId, $categoryId, $currentStock, $countedQty, $difference, $status, $unitCost, $totalAmount]);
            return (int)db()->lastInsertId();
        }
    }

    public static function getItems(int $stockTakingId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT sti.*, p.name as product_name, p.sku, c.name as category_name
             FROM stock_taking_items sti
             LEFT JOIN products p ON p.id = sti.product_id
             LEFT JOIN categories c ON c.id = sti.category_id
             WHERE sti.stock_taking_id = ? AND sti.tenant_id = ?
             ORDER BY c.name, p.name"
        );
        $stmt->execute([$stockTakingId, $tid]);
        return $stmt->fetchAll();
    }

    public static function getSummary(int $stockTakingId): array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT 
                status,
                COUNT(*) as item_count,
                SUM(total_amount) as total_amount,
                SUM(ABS(difference)) as total_qty
             FROM stock_taking_items
             WHERE stock_taking_id = ? AND tenant_id = ?
             GROUP BY status"
        );
        $stmt->execute([$stockTakingId, $tid]);
        $results = $stmt->fetchAll();

        $summary = [
            'shortage' => ['item_count' => 0, 'total_amount' => 0, 'total_qty' => 0],
            'over' => ['item_count' => 0, 'total_amount' => 0, 'total_qty' => 0],
            'equal' => ['item_count' => 0, 'total_amount' => 0, 'total_qty' => 0]
        ];

        foreach ($results as $row) {
            $summary[$row['status']] = [
                'item_count' => (int)$row['item_count'],
                'total_amount' => (float)$row['total_amount'],
                'total_qty' => (float)$row['total_qty']
            ];
        }

        return $summary;
    }

    public static function complete(int $stockTakingId): bool {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "UPDATE stock_takings SET status = 'completed', updated_at = CURRENT_TIMESTAMP WHERE id = ? AND tenant_id = ?"
        );
        return $stmt->execute([$stockTakingId, $tid]);
    }

    public static function updateStockFromCount(int $stockTakingId): bool {
        $tid = static::tenantId();
        $st = self::find($stockTakingId);
        if (!$st || $st['status'] !== 'completed') {
            return false;
        }

        // Get all items with differences
        $stmt = db()->prepare(
            "SELECT product_id, counted_qty, current_stock, location_id 
             FROM stock_taking_items sti
             JOIN stock_takings st ON st.id = sti.stock_taking_id
             WHERE sti.stock_taking_id = ? AND sti.tenant_id = ? AND sti.difference != 0"
        );
        $stmt->execute([$stockTakingId, $tid]);
        $items = $stmt->fetchAll();

        foreach ($items as $item) {
            $difference = $item['counted_qty'] - $item['current_stock'];
            $absDifference = abs($difference);

            // Update stock table
            $updateStmt = db()->prepare(
                "UPDATE stock
                 SET quantity = quantity + ?
                 WHERE product_id = ? AND location_id = ? AND tenant_id = ?"
            );
            $updateStmt->execute([$difference, $item['product_id'], $st['location_id'], $tid]);

            // Record stock movement with appropriate type and sign
            $movementType = 'adjustment';
            $movementStmt = db()->prepare(
                "INSERT INTO stock_movements (tenant_id, product_id, location_id, type, quantity, reference, note, user_id)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $movementStmt->execute([
                $tid,
                $item['product_id'],
                $st['location_id'],
                $movementType,
                $difference,
                $st['reference'],
                'Stock taking adjustment',
                Auth::userId()
            ]);
        }

        // Mark stock as updated
        $markStmt = db()->prepare(
            "UPDATE stock_takings SET stock_updated = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND tenant_id = ?"
        );
        $markStmt->execute([$stockTakingId, $tid]);

        return true;
    }

    public static function find(int $id): ?array {
        $tid = static::tenantId();
        $stmt = db()->prepare(
            "SELECT st.*, l.name as location_name, u.name as user_name
             FROM stock_takings st
             LEFT JOIN locations l ON l.id = st.location_id
             LEFT JOIN users u ON u.id = st.user_id
             WHERE st.id = ? AND st.tenant_id = ?"
        );
        $stmt->execute([$id, $tid]);
        return $stmt->fetch() ?: null;
    }

    public static function listPaginated(int $page = 1): array {
        $tid = static::tenantId();
        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $offset = ($page - 1) * $pp;

        // Get total count
        $cntStmt = db()->prepare("SELECT COUNT(*) FROM stock_takings WHERE tenant_id = ?");
        $cntStmt->execute([$tid]);
        $total = (int)$cntStmt->fetchColumn();

        // Get paginated stock takings
        $stmt = db()->prepare(
            "SELECT st.*, l.name as location_name, u.name as user_name,
                    (SELECT COUNT(*) FROM stock_taking_items WHERE stock_taking_id = st.id) as item_count
             FROM stock_takings st
             LEFT JOIN locations l ON l.id = st.location_id
             LEFT JOIN users u ON u.id = st.user_id
             WHERE st.tenant_id = ?
             ORDER BY st.created_at DESC
             LIMIT {$pp} OFFSET {$offset}"
        );
        $stmt->execute([$tid]);

        return [
            'data' => $stmt->fetchAll(),
            'total' => $total,
            'last_page' => $perPage === 'all' ? 1 : (int)ceil($total / $pp),
            'page' => $page
        ];
    }
}
