<?php

class RawMaterial extends Model
{
    protected string $table = 'raw_materials';

    public function allOrdered(): array
    {
        return $this->db->query(
            "SELECT * FROM raw_materials ORDER BY is_active DESC, material_name ASC"
        )->fetchAll();
    }

    public function stats(): array
    {
        $stmt = $this->db->query(
            "SELECT
                COUNT(*) as total_materials,
                COALESCE(SUM(CASE WHEN current_stock <= 0 AND is_active = 1 THEN 1 ELSE 0 END),0) as out_of_stock,
                COALESCE(SUM(CASE WHEN current_stock > 0 AND current_stock <= reorder_level AND is_active = 1 THEN 1 ELSE 0 END),0) as low_stock,
                COALESCE(SUM(CASE WHEN current_stock > reorder_level AND is_active = 1 THEN 1 ELSE 0 END),0) as in_stock_ok,
                COALESCE(SUM(current_stock * unit_cost),0) as total_value,
                COALESCE(SUM(CASE WHEN expiry_date IS NOT NULL AND expiry_date < CURDATE() AND is_active = 1 THEN 1 ELSE 0 END),0) as expired,
                COALESCE(SUM(CASE WHEN expiry_date IS NOT NULL AND expiry_date >= CURDATE() AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) AND is_active = 1 THEN 1 ELSE 0 END),0) as expiring_soon
             FROM raw_materials"
        );
        return $stmt->fetch();
    }

    public function stockIn(int $id, float $qty, ?float $unitCost, ?int $userId, string $notes = '', ?string $expiryDate = null): void
    {
        $this->db->beginTransaction();
        try {
            $this->db->prepare("UPDATE raw_materials SET current_stock = current_stock + :qty WHERE id = :id")
                ->execute(['qty' => $qty, 'id' => $id]);

            if ($unitCost !== null) {
                $this->db->prepare("UPDATE raw_materials SET unit_cost = :cost WHERE id = :id")
                    ->execute(['cost' => $unitCost, 'id' => $id]);
            }
            if ($expiryDate !== null) {
                $this->db->prepare("UPDATE raw_materials SET expiry_date = :exp WHERE id = :id")
                    ->execute(['exp' => $expiryDate ?: null, 'id' => $id]);
            }

            $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, notes, created_by)
                 VALUES (:id, 'PURCHASE_IN', :qty, 'MANUAL', :notes, :user_id)"
            )->execute(['id' => $id, 'qty' => $qty, 'notes' => $notes ?: 'Stock purchase', 'user_id' => $userId]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    // $qty positive = adds stock, negative = removes stock. $type is 'ADJUSTMENT' or 'WASTAGE'
    public function adjust(int $id, float $qty, string $type, ?int $userId, string $notes = ''): void
    {
        $this->db->beginTransaction();
        try {
            $this->db->prepare("UPDATE raw_materials SET current_stock = current_stock + :qty WHERE id = :id")
                ->execute(['qty' => $qty, 'id' => $id]);

            $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, notes, created_by)
                 VALUES (:id, :type, :qty, 'MANUAL', :notes, :user_id)"
            )->execute(['id' => $id, 'type' => $type, 'qty' => $qty, 'notes' => $notes ?: null, 'user_id' => $userId]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function transactionsFor(int $id, int $limit = 50): array
    {
        $stmt = $this->db->prepare(
            "SELECT st.*, u.full_name as created_by_name
             FROM stock_transactions st
             LEFT JOIN users u ON u.id = st.created_by
             WHERE st.raw_material_id = :id
             ORDER BY st.id DESC LIMIT " . (int) $limit
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetchAll();
    }

    public function recentMovementsAll(int $limit = 60): array
    {
        $stmt = $this->db->query(
            "SELECT st.*, rm.material_name, rm.unit, u.full_name as created_by_name
             FROM stock_transactions st
             JOIN raw_materials rm ON rm.id = st.raw_material_id
             LEFT JOIN users u ON u.id = st.created_by
             ORDER BY st.id DESC LIMIT " . (int) $limit
        );
        return $stmt->fetchAll();
    }
    public function hasTransactions(int $id): bool
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) as cnt FROM stock_transactions WHERE raw_material_id = :id");
        $stmt->execute(['id' => $id]);
        return (int) $stmt->fetch()['cnt'] > 0;
    }

    // Average daily consumption (sales + kitchen issues) over the last N days, per material
    public function consumptionMap(int $days = 14): array
    {
        $stmt = $this->db->prepare(
            "SELECT raw_material_id, SUM(ABS(quantity)) as total_used
             FROM stock_transactions
             WHERE transaction_type IN ('SALE_DEDUCT','KITCHEN_ISSUE')
               AND created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)
             GROUP BY raw_material_id"
        );
        $stmt->execute(['days' => $days]);
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[$row['raw_material_id']] = (float) $row['total_used'] / $days;
        }
        return $map;
    }

    // Top consumed materials over the last N days — for the consumption trend chart
    public function topConsumedTrend(int $days = 7, int $limit = 5): array
    {
        $topStmt = $this->db->prepare(
            "SELECT rm.id, rm.material_name, SUM(ABS(st.quantity)) as total_used
             FROM stock_transactions st
             JOIN raw_materials rm ON rm.id = st.raw_material_id
             WHERE st.transaction_type IN ('SALE_DEDUCT','KITCHEN_ISSUE')
               AND st.created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)
             GROUP BY rm.id, rm.material_name
             ORDER BY total_used DESC LIMIT " . (int) $limit
        );
        $topStmt->execute(['days' => $days]);
        $topMaterials = $topStmt->fetchAll();

        $labels = [];
        $datasets = [];
        for ($i = $days - 1; $i >= 0; $i--) {
            $labels[] = date('D', strtotime("-{$i} days"));
        }

        foreach ($topMaterials as $tm) {
            $dailyStmt = $this->db->prepare(
                "SELECT DATE(created_at) as d, SUM(ABS(quantity)) as qty
                 FROM stock_transactions
                 WHERE raw_material_id = :id AND transaction_type IN ('SALE_DEDUCT','KITCHEN_ISSUE')
                   AND created_at >= DATE_SUB(CURDATE(), INTERVAL :days DAY)
                 GROUP BY DATE(created_at)"
            );
            $dailyStmt->execute(['id' => $tm['id'], 'days' => $days - 1]);
            $dayMap = [];
            foreach ($dailyStmt->fetchAll() as $row) {
                $dayMap[$row['d']] = (float) $row['qty'];
            }
            $series = [];
            for ($i = $days - 1; $i >= 0; $i--) {
                $d = date('Y-m-d', strtotime("-{$i} days"));
                $series[] = $dayMap[$d] ?? 0;
            }
            $datasets[] = ['label' => $tm['material_name'], 'data' => $series];
        }

        return ['labels' => $labels, 'datasets' => $datasets];
    }

    // Materials with zero movement in the last N days despite being active — possible dead stock
    public function staleMaterials(int $days = 30): array
    {
        $stmt = $this->db->prepare(
            "SELECT rm.* FROM raw_materials rm
             WHERE rm.is_active = 1 AND rm.current_stock > 0
               AND rm.id NOT IN (
                   SELECT DISTINCT raw_material_id FROM stock_transactions
                   WHERE created_at >= DATE_SUB(NOW(), INTERVAL :days DAY)
               )"
        );
        $stmt->execute(['days' => $days]);
        return $stmt->fetchAll();
    }
}
