<?php
/**
 * pricing_engine_v2.php
 * Place: /rooms/modules/reports/pricing_engine_v2.php
 *
 * Dynamic Pricing Engine V2
 * - Reads active rules from pricing_rules
 * - Applies season/weekend/event/manual/occupancy/pickup rules
 * - Combines rules with demand logic from occupancy, pickup, rooms-left, lead time
 * - Respects min/max rate limits
 * - Does NOT update stored rates automatically
 *
 * Usage:
 *
 * require_once __DIR__ . '/../reports/pricing_engine_v2.php';
 *
 * $suggestion = pricing_engine_v2_suggest_rate($pdo, [
 *     'property_id' => 1,
 *     'room_type_id' => 2,
 *     'agent_id' => 0,
 *     'stay_date' => '2026-05-01',
 *     'base_rate' => 150.00,
 * ]);
 */

if (!function_exists('pe2_table_exists')) {
    function pe2_table_exists(PDO $pdo, string $tableName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('pe2_column_exists')) {
    function pe2_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
              AND column_name = :column_name
        ");
        $stmt->execute([
            ':table_name' => $tableName,
            ':column_name' => $columnName,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('pe2_round_rate')) {
    function pe2_round_rate(float $amount, float $roundTo = 1.00): float
    {
        if ($roundTo <= 0) {
            return round($amount, 2);
        }

        return round(round($amount / $roundTo) * $roundTo, 2);
    }
}

if (!function_exists('pe2_capacity')) {
    function pe2_capacity(PDO $pdo, int $propertyId = 0, int $roomTypeId = 0): int
    {
        if (!pe2_table_exists($pdo, 'rooms') || !pe2_table_exists($pdo, 'room_types')) {
            return 0;
        }

        $hasRoomActive = pe2_column_exists($pdo, 'rooms', 'is_active');
        $hasRoomStatus = pe2_column_exists($pdo, 'rooms', 'status');

        $where = "1=1";
        $params = [];

        if ($propertyId > 0) {
            $where .= " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $params[':property_id'] = $propertyId;
        }

        if ($roomTypeId > 0) {
            $where .= " AND r.room_type_id = :room_type_id ";
            $params[':room_type_id'] = $roomTypeId;
        }

        if ($hasRoomActive) {
            $where .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        if ($hasRoomStatus) {
            $where .= " AND (r.status IS NULL OR r.status NOT IN ('inactive','out_of_order','blocked')) ";
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM rooms r
            LEFT JOIN room_types rt ON rt.id = r.room_type_id
            WHERE $where
        ");
        $stmt->execute($params);

        return (int)($stmt->fetchColumn() ?: 0);
    }
}

if (!function_exists('pe2_booked_rooms')) {
    function pe2_booked_rooms(PDO $pdo, string $stayDate, int $propertyId = 0, int $roomTypeId = 0): int
    {
        if (!pe2_table_exists($pdo, 'orders') || !pe2_table_exists($pdo, 'order_rooming')) {
            return 0;
        }

        $hasOrderStatus = pe2_column_exists($pdo, 'orders', 'status');
        $hasOrderCancelled = pe2_column_exists($pdo, 'orders', 'cancelled');
        $hasRoomingCancelled = pe2_column_exists($pdo, 'order_rooming', 'cancelled');
        $hasRoomingProperty = pe2_column_exists($pdo, 'order_rooming', 'property_id');

        $daySql = $pdo->quote($stayDate);

        $where = "
            orm.check_in <= $daySql
            AND orm.check_out > $daySql
        ";

        $params = [];

        if ($propertyId > 0) {
            if ($hasRoomingProperty) {
                $where .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
            } else {
                $where .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
            }
            $params[':property_id'] = $propertyId;
        }

        if ($roomTypeId > 0) {
            $where .= " AND orm.room_type_id = :room_type_id ";
            $params[':room_type_id'] = $roomTypeId;
        }

        if ($hasOrderCancelled) {
            $where .= " AND COALESCE(o.cancelled, 0) = 0 ";
        }

        if ($hasRoomingCancelled) {
            $where .= " AND COALESCE(orm.cancelled, 0) = 0 ";
        }

        if ($hasOrderStatus) {
            $where .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted') ";
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(DISTINCT orm.id)
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            WHERE $where
        ");
        $stmt->execute($params);

        return (int)($stmt->fetchColumn() ?: 0);
    }
}

if (!function_exists('pe2_pickup')) {
    function pe2_pickup(PDO $pdo, string $stayDate, int $propertyId = 0, int $roomTypeId = 0): array
    {
        if (!pe2_table_exists($pdo, 'orders')) {
            return ['pickup_7' => 0, 'pickup_14' => 0, 'pickup_30' => 0];
        }

        $hasCreatedAt = pe2_column_exists($pdo, 'orders', 'created_at');
        $hasOrderStatus = pe2_column_exists($pdo, 'orders', 'status');
        $hasOrderCancelled = pe2_column_exists($pdo, 'orders', 'cancelled');
        $hasOrderRooming = pe2_table_exists($pdo, 'order_rooming');

        $staySql = $pdo->quote($stayDate);
        $last7Sql = $pdo->quote(date('Y-m-d', strtotime('-7 days')));
        $last14Sql = $pdo->quote(date('Y-m-d', strtotime('-14 days')));
        $last30Sql = $pdo->quote(date('Y-m-d', strtotime('-30 days')));

        $createdExpr = $hasCreatedAt ? "DATE(o.created_at)" : "DATE(o.check_in)";

        $joins = "";
        $where = "
            o.check_in <= $staySql
            AND o.check_out > $staySql
        ";
        $params = [];

        if ($roomTypeId > 0 && $hasOrderRooming) {
            $joins .= " LEFT JOIN order_rooming orm ON orm.order_id = o.id ";
            $where .= " AND orm.room_type_id = :room_type_id ";
            $params[':room_type_id'] = $roomTypeId;
        }

        if ($propertyId > 0) {
            $where .= " AND o.property_id = :property_id ";
            $params[':property_id'] = $propertyId;
        }

        if ($hasOrderCancelled) {
            $where .= " AND COALESCE(o.cancelled, 0) = 0 ";
        }

        if ($hasOrderStatus) {
            $where .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted') ";
        }

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(CASE WHEN $createdExpr >= $last7Sql THEN 1 ELSE 0 END), 0) AS pickup_7,
                COALESCE(SUM(CASE WHEN $createdExpr >= $last14Sql THEN 1 ELSE 0 END), 0) AS pickup_14,
                COALESCE(SUM(CASE WHEN $createdExpr >= $last30Sql THEN 1 ELSE 0 END), 0) AS pickup_30
            FROM orders o
            $joins
            WHERE $where
        ");
        $stmt->execute($params);

        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'pickup_7' => (int)($row['pickup_7'] ?? 0),
            'pickup_14' => (int)($row['pickup_14'] ?? 0),
            'pickup_30' => (int)($row['pickup_30'] ?? 0),
        ];
    }
}

if (!function_exists('pe2_rule_matches')) {
    function pe2_rule_matches(array $rule, array $context): bool
    {
        $propertyId = (int)($context['property_id'] ?? 0);
        $roomTypeId = (int)($context['room_type_id'] ?? 0);
        $agentId = (int)($context['agent_id'] ?? 0);
        $stayDate = (string)($context['stay_date'] ?? date('Y-m-d'));
        $occupancy = (float)($context['occupancy_percent'] ?? 0);
        $pickup7 = (int)($context['pickup_7'] ?? 0);

        if (strtotime($stayDate) === false) {
            return false;
        }

        if (!empty($rule['valid_from']) && strtotime($stayDate) < strtotime((string)$rule['valid_from'])) {
            return false;
        }

        if (!empty($rule['valid_to']) && strtotime($stayDate) > strtotime((string)$rule['valid_to'])) {
            return false;
        }

        if (!empty($rule['property_id']) && (int)$rule['property_id'] !== $propertyId) {
            return false;
        }

        if (!empty($rule['room_type_id']) && (int)$rule['room_type_id'] !== $roomTypeId) {
            return false;
        }

        if (!empty($rule['agent_id']) && (int)$rule['agent_id'] !== $agentId) {
            return false;
        }

        if (!empty($rule['days_of_week'])) {
            $dayNum = (string)(int)date('N', strtotime($stayDate));
            $allowedDays = array_filter(array_map('trim', explode(',', (string)$rule['days_of_week'])));
            if ($allowedDays && !in_array($dayNum, $allowedDays, true)) {
                return false;
            }
        }

        if ($rule['occupancy_from'] !== null && $rule['occupancy_from'] !== '' && $occupancy < (float)$rule['occupancy_from']) {
            return false;
        }

        if ($rule['occupancy_to'] !== null && $rule['occupancy_to'] !== '' && $occupancy > (float)$rule['occupancy_to']) {
            return false;
        }

        if ($rule['pickup_from'] !== null && $rule['pickup_from'] !== '' && $pickup7 < (int)$rule['pickup_from']) {
            return false;
        }

        if ($rule['pickup_to'] !== null && $rule['pickup_to'] !== '' && $pickup7 > (int)$rule['pickup_to']) {
            return false;
        }

        return true;
    }
}

if (!function_exists('pe2_load_rules')) {
    function pe2_load_rules(PDO $pdo, array $context): array
    {
        if (!pe2_table_exists($pdo, 'pricing_rules')) {
            return [];
        }

        $stmt = $pdo->prepare("
            SELECT *
            FROM pricing_rules
            WHERE is_active = 1
            ORDER BY priority ASC, id ASC
        ");
        $stmt->execute();

        $rules = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $matched = [];

        foreach ($rules as $rule) {
            if (pe2_rule_matches($rule, $context)) {
                $matched[] = $rule;
            }
        }

        return $matched;
    }
}

if (!function_exists('pricing_engine_v2_suggest_rate')) {
    function pricing_engine_v2_suggest_rate(PDO $pdo, array $options): array
    {
        $propertyId = (int)($options['property_id'] ?? 0);
        $roomTypeId = (int)($options['room_type_id'] ?? 0);
        $agentId = (int)($options['agent_id'] ?? 0);
        $stayDate = (string)($options['stay_date'] ?? date('Y-m-d'));
        $baseRate = (float)($options['base_rate'] ?? 0);
        $roundTo = isset($options['round_to']) ? (float)$options['round_to'] : 1.00;

        $globalMinRate = isset($options['min_rate']) ? (float)$options['min_rate'] : ($baseRate * 0.75);
        $globalMaxRate = isset($options['max_rate']) ? (float)$options['max_rate'] : ($baseRate * 1.65);

        if ($baseRate <= 0) {
            return [
                'ok' => false,
                'error' => 'Base rate must be greater than zero.',
                'base_rate' => $baseRate,
                'recommended_rate' => 0,
                'rules_applied' => [],
                'reasons' => [],
            ];
        }

        if (strtotime($stayDate) === false) {
            return [
                'ok' => false,
                'error' => 'Invalid stay date.',
                'base_rate' => $baseRate,
                'recommended_rate' => $baseRate,
                'rules_applied' => [],
                'reasons' => [],
            ];
        }

        $capacity = pe2_capacity($pdo, $propertyId, $roomTypeId);
        $booked = pe2_booked_rooms($pdo, $stayDate, $propertyId, $roomTypeId);
        $roomsLeft = $capacity - $booked;
        $occupancy = $capacity > 0 ? ($booked / $capacity) * 100 : 0;
        $pickup = pe2_pickup($pdo, $stayDate, $propertyId, $roomTypeId);
        $leadDays = (int)floor((strtotime($stayDate) - strtotime(date('Y-m-d'))) / 86400);

        $context = [
            'property_id' => $propertyId,
            'room_type_id' => $roomTypeId,
            'agent_id' => $agentId,
            'stay_date' => $stayDate,
            'capacity' => $capacity,
            'booked_rooms' => $booked,
            'rooms_left' => $roomsLeft,
            'occupancy_percent' => $occupancy,
            'pickup_7' => (int)($pickup['pickup_7'] ?? 0),
            'pickup_14' => (int)($pickup['pickup_14'] ?? 0),
            'pickup_30' => (int)($pickup['pickup_30'] ?? 0),
            'lead_days' => $leadDays,
        ];

        $rate = $baseRate;
        $rulesApplied = [];
        $reasons = [];

        $minRate = $globalMinRate;
        $maxRate = $globalMaxRate;

        $rules = pe2_load_rules($pdo, $context);

        foreach ($rules as $rule) {
            $beforeRate = $rate;
            $adjustmentType = (string)($rule['adjustment_type'] ?? 'percent');
            $adjustmentValue = (float)($rule['adjustment_value'] ?? 0);

            if ($rule['min_rate'] !== null && $rule['min_rate'] !== '') {
                $minRate = max($minRate, (float)$rule['min_rate']);
            }

            if ($rule['max_rate'] !== null && $rule['max_rate'] !== '') {
                $maxRate = min($maxRate, (float)$rule['max_rate']);
            }

            if ($adjustmentType === 'percent') {
                $rate = $rate * (1 + ($adjustmentValue / 100));
            } else {
                $rate = $rate + $adjustmentValue;
            }

            $rulesApplied[] = [
                'id' => (int)($rule['id'] ?? 0),
                'rule_name' => (string)($rule['rule_name'] ?? 'Pricing Rule'),
                'rule_type' => (string)($rule['rule_type'] ?? ''),
                'adjustment_type' => $adjustmentType,
                'adjustment_value' => $adjustmentValue,
                'apply_mode' => (string)($rule['apply_mode'] ?? 'suggest_only'),
                'priority' => (int)($rule['priority'] ?? 100),
                'before_rate' => round($beforeRate, 2),
                'after_rate' => round($rate, 2),
            ];

            $symbol = $adjustmentType === 'percent' ? '%' : '';
            $prefix = $adjustmentValue >= 0 ? '+' : '';
            $reasons[] = (string)($rule['rule_name'] ?? 'Pricing Rule') . ': ' . $prefix . number_format($adjustmentValue, 2) . $symbol;
        }

        /* Built-in demand logic after rule logic */
        $demandAdjustment = 0.0;

        if ($occupancy >= 95) {
            $demandAdjustment += 0.20;
            $reasons[] = 'Demand logic: occupancy >= 95%: +20%';
        } elseif ($occupancy >= 85) {
            $demandAdjustment += 0.14;
            $reasons[] = 'Demand logic: occupancy >= 85%: +14%';
        } elseif ($occupancy >= 75) {
            $demandAdjustment += 0.08;
            $reasons[] = 'Demand logic: occupancy >= 75%: +8%';
        } elseif ($occupancy <= 30) {
            $demandAdjustment -= 0.10;
            $reasons[] = 'Demand logic: occupancy <= 30%: -10%';
        } elseif ($occupancy <= 45) {
            $demandAdjustment -= 0.05;
            $reasons[] = 'Demand logic: occupancy <= 45%: -5%';
        }

        if ($capacity > 0) {
            if ($roomsLeft <= 0) {
                $demandAdjustment += 0.18;
                $reasons[] = 'Demand logic: sold out / over capacity: +18%';
            } elseif ($roomsLeft < 3) {
                $demandAdjustment += 0.12;
                $reasons[] = 'Demand logic: fewer than 3 rooms left: +12%';
            } elseif ($roomsLeft < 6) {
                $demandAdjustment += 0.07;
                $reasons[] = 'Demand logic: fewer than 6 rooms left: +7%';
            }
        }

        if (($pickup['pickup_7'] ?? 0) >= 5) {
            $demandAdjustment += 0.08;
            $reasons[] = 'Demand logic: strong 7-day pickup: +8%';
        } elseif (($pickup['pickup_7'] ?? 0) >= 3) {
            $demandAdjustment += 0.05;
            $reasons[] = 'Demand logic: good 7-day pickup: +5%';
        }

        if (($pickup['pickup_30'] ?? 0) <= 1 && $occupancy < 50) {
            $demandAdjustment -= 0.04;
            $reasons[] = 'Demand logic: weak pickup and soft occupancy: -4%';
        }

        $dayOfWeek = (int)date('N', strtotime($stayDate));
        if ($dayOfWeek >= 5) {
            $demandAdjustment += 0.03;
            $reasons[] = 'Demand logic: weekend premium: +3%';
        }

        if ($leadDays <= 2 && $occupancy < 60) {
            $demandAdjustment -= 0.04;
            $reasons[] = 'Demand logic: last-minute unsold inventory: -4%';
        } elseif ($leadDays >= 45 && $occupancy >= 60) {
            $demandAdjustment += 0.03;
            $reasons[] = 'Demand logic: strong advance demand: +3%';
        }

        $beforeDemandRate = $rate;
        $rate = $rate * (1 + $demandAdjustment);

        if (abs($demandAdjustment) > 0.0001) {
            $rulesApplied[] = [
                'id' => 0,
                'rule_name' => 'Built-in Demand Logic',
                'rule_type' => 'demand',
                'adjustment_type' => 'percent',
                'adjustment_value' => round($demandAdjustment * 100, 2),
                'apply_mode' => 'suggest_only',
                'priority' => 9999,
                'before_rate' => round($beforeDemandRate, 2),
                'after_rate' => round($rate, 2),
            ];
        }

        $boundedRate = max($minRate, min($maxRate, $rate));
        $recommendedRate = pe2_round_rate($boundedRate, $roundTo);

        if (abs($boundedRate - $rate) > 0.0001) {
            $reasons[] = 'Rate bounded by min/max controls.';
        }

        if (!$reasons) {
            $reasons[] = 'No pricing rules or demand pressure detected: keep base rate.';
        }

        $adjustmentPercent = $baseRate > 0 ? (($recommendedRate - $baseRate) / $baseRate) * 100 : 0;

        $demandLevel = 'normal';
        if ($adjustmentPercent >= 25) {
            $demandLevel = 'very_high';
        } elseif ($adjustmentPercent >= 10) {
            $demandLevel = 'high';
        } elseif ($adjustmentPercent <= -10) {
            $demandLevel = 'low';
        } elseif ($adjustmentPercent < 0) {
            $demandLevel = 'soft';
        }

        $autoApplyRules = array_values(array_filter($rulesApplied, fn($r) => ($r['apply_mode'] ?? '') === 'auto_apply'));

        return [
            'ok' => true,
            'property_id' => $propertyId,
            'room_type_id' => $roomTypeId,
            'agent_id' => $agentId,
            'stay_date' => $stayDate,
            'base_rate' => round($baseRate, 2),
            'recommended_rate' => $recommendedRate,
            'min_rate' => round($minRate, 2),
            'max_rate' => round($maxRate, 2),
            'adjustment_percent' => round($adjustmentPercent, 2),
            'demand_level' => $demandLevel,
            'capacity' => $capacity,
            'booked_rooms' => $booked,
            'rooms_left' => $roomsLeft,
            'occupancy_percent' => round($occupancy, 2),
            'pickup_7' => (int)($pickup['pickup_7'] ?? 0),
            'pickup_14' => (int)($pickup['pickup_14'] ?? 0),
            'pickup_30' => (int)($pickup['pickup_30'] ?? 0),
            'lead_days' => $leadDays,
            'rules_applied' => $rulesApplied,
            'auto_apply_rule_count' => count($autoApplyRules),
            'reasons' => $reasons,
        ];
    }
}

if (!function_exists('pricing_engine_v2_suggest_range')) {
    function pricing_engine_v2_suggest_range(PDO $pdo, array $options): array
    {
        $dateFrom = (string)($options['date_from'] ?? date('Y-m-d'));
        $dateTo = (string)($options['date_to'] ?? date('Y-m-d', strtotime('+29 days')));

        if (strtotime($dateFrom) === false || strtotime($dateTo) === false || strtotime($dateFrom) > strtotime($dateTo)) {
            return [];
        }

        $rows = [];
        $start = new DateTime($dateFrom);
        $end = new DateTime($dateTo);
        $endExclusive = (clone $end)->modify('+1 day');

        while ($start < $endExclusive) {
            $day = $start->format('Y-m-d');
            $options['stay_date'] = $day;
            $rows[] = pricing_engine_v2_suggest_rate($pdo, $options);
            $start->modify('+1 day');
        }

        return $rows;
    }
}
