<?php
/**
 * rate_support.php - shared rate resolution for RoomZ.
 *
 * These functions used to live only inside reservation_form.php, which meant
 * anything else needing a rate (the calendar's Extend Stay check, reports,
 * a future rebooking screen) either could not price a night or had to
 * re-implement the rules and drift out of step.
 *
 * Every file that needs to price a night includes THIS file. There is one
 * definition of:
 *   - what a meal plan is (BB / HB / FB)
 *   - which rate plan wins for a room type + agent + date + meal plan
 *   - how occupancy turns a plan into a nightly amount
 *
 * All functions are guarded with function_exists(), so including this file
 * alongside a page that already defines them is safe.
 */

if (!function_exists('roomz_meal_plans')) {
    function roomz_meal_plans(): array
    {
        return [
            'BB' => 'BB - Bed + Breakfast',
            'HB' => 'HB - Half Board',
            'FB' => 'FB - Full Board',
        ];
    }
}

if (!function_exists('normalize_meal_plan_for_rates')) {
    function normalize_meal_plan_for_rates($value): string
    {
        $value = strtoupper(trim((string)$value));
        return in_array($value, ['HB', 'BB', 'FB'], true) ? $value : 'BB';
    }
}

if (!function_exists('rate_tables_available')) {
    function rate_tables_available(PDO $pdo): bool
    {
        static $ok = null;
        if ($ok !== null) return $ok;
        try {
            $stmt = $pdo->query("
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME IN ('rate_plans','rate_plan_prices')");
            $ok = ((int)$stmt->fetchColumn() === 2);
        } catch (Throwable $e) {
            $ok = false;
        }
        return $ok;
    }
}

if (!function_exists('rate_contracts_available')) {
    /**
     * Deploy-safe check for the contract layer. All three pieces must exist
     * before the contract path can be used:
     *   rate_contracts table, rate_plans.contract_id, agents.rate_contract_id
     * Until then this returns false and resolution uses the legacy scalar
     * agents.rate_plan_id link exactly as before.
     */
    function rate_contracts_available(PDO $pdo): bool
    {
        static $ok = null;
        if ($ok !== null) return $ok;
        try {
            $stmt = $pdo->query("
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'rate_contracts'");
            if ((int)$stmt->fetchColumn() !== 1) { $ok = false; return $ok; }
            $stmt = $pdo->query("
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND ((TABLE_NAME = 'rate_plans' AND COLUMN_NAME = 'contract_id')
                    OR (TABLE_NAME = 'agents'
                        AND COLUMN_NAME IN ('rate_contract_id','group_rate_contract_id')))");
            $ok = ((int)$stmt->fetchColumn() === 3);
        } catch (Throwable $e) {
            $ok = false;
        }
        return $ok;
    }
}

if (!function_exists('rate_plans_has_rack_priority')) {
    function rate_plans_has_rack_priority(PDO $pdo): bool
    {
        static $ok = null;
        if ($ok !== null) return $ok;
        try {
            $stmt = $pdo->query("
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'rate_plans'
                  AND COLUMN_NAME = 'rack_priority'");
            $ok = ((int)$stmt->fetchColumn() === 1);
        } catch (Throwable $e) {
            $ok = false;
        }
        return $ok;
    }
}

if (!function_exists('meal_supplements_available')) {
    function meal_supplements_available(PDO $pdo): bool
    {
        static $ok = null;
        if ($ok !== null) return $ok;
        try {
            $stmt = $pdo->query("
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'meal_plan_supplements'");
            $ok = ((int)$stmt->fetchColumn() === 1);
        } catch (Throwable $e) {
            $ok = false;
        }
        return $ok;
    }
}

if (!function_exists('lookup_meal_supplement')) {
    /**
     * Per-adult / per-child nightly supplement for a meal plan.
     * Returns null when the table is absent or nothing covers the stay date.
     */
    function lookup_meal_supplement(PDO $pdo, $propertyId, string $mealPlan, string $stayDate)
    {
        if ($mealPlan === '' || !meal_supplements_available($pdo)) return null;
        try {
            $sql = "
                SELECT adult_supplement, child_supplement, currency
                FROM meal_plan_supplements
                WHERE meal_plan = :meal_plan
                  AND status = 1
                  AND valid_from <= :stay_date_from
                  AND valid_to   >= :stay_date_to";
            $params = [
                ':meal_plan'      => $mealPlan,
                ':stay_date_from' => $stayDate,
                ':stay_date_to'   => $stayDate,
            ];
            if ($propertyId !== null && (int)$propertyId > 0) {
                $sql .= " AND property_id = :property_id ";
                $params[':property_id'] = (int)$propertyId;
            }
            $sql .= " ORDER BY DATEDIFF(valid_to, valid_from) ASC, id DESC LIMIT 1 ";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row ? $row : null;
        } catch (Throwable $e) {
            return null;
        }
    }
}

if (!function_exists('fetch_rate_plan')) {
    /**
     * Resolve the rate for one room type, on one night, for one meal plan.
     *
     * Reads the normalised tables (rate_plans + rate_plan_prices +
     * agents.rate_plan_id) so an agent's contract is ONE row shared by
     * hundreds of agents, and falls back to the legacy flat
     * room_type_rate_plans when the new schema is not deployed (or when the
     * new tables have no public rate for this night, which is the case until
     * the rack rates are migrated across).
     *
     * Ordering is by SPECIFICITY, not id: the narrowest date window wins.
     * The old `ORDER BY id DESC` meant a correction row inserted against an
     * annual plan silently outranked a narrower peak-season plan all year.
     *
     * Returns the plan row plus 'rate_source':
     *   'agent'           - the agent's own contract was used
     *   'agent_derived'   - the contract had no plan for this meal plan, so
     *                       the contract base + a configured meal supplement
     *                       was used. See derived_from_supplement.
     *   'public'          - no agent on the booking; rack rate used
     *   'public_fallback' - an agent WAS set but has no contract covering
     *                       this night, so rack rate was applied silently.
     *                       Callers should surface this to the user.
     */
    function fetch_rate_plan(PDO $pdo, int $roomTypeId, ?int $agentId, string $stayDate, string $mealPlan = 'BB', int $totalRooms = 1, $propertyId = null): ?array
    {
        $mealPlan   = normalize_meal_plan_for_rates($mealPlan);
        $totalRooms = max(1, $totalRooms);

        if (!rate_tables_available($pdo)) {
            return fetch_rate_plan_legacy($pdo, $roomTypeId, $agentId, $stayDate, $mealPlan);
        }

        $select = "
            SELECT
                pp.id,
                pp.room_type_id,
                rp.title,
                rp.meal_plan,
                pp.valid_from,
                pp.valid_to,
                pp.sharing_rate,
                pp.single_rate,
                pp.single_supplement,
                pp.child_rate_percent,
                pp.currency,
                pp.status,
                NULL AS season_label,
                'secondary' AS season_color,
                rp.id AS rate_plan_id,
                rp.code AS rate_plan_code
            FROM rate_plan_prices pp
            INNER JOIN rate_plans rp ON rp.id = pp.rate_plan_id
        ";
        // Narrowest season first; newest row only as a final tie-break.
        $order = " ORDER BY DATEDIFF(pp.valid_to, pp.valid_from) ASC, pp.id DESC LIMIT 1 ";
        if (rate_plans_has_rack_priority($pdo)) {
            $publicOrder = " ORDER BY rp.rack_priority ASC,
                                      DATEDIFF(pp.valid_to, pp.valid_from) ASC,
                                      pp.id DESC LIMIT 1 ";
        } else {
            $publicOrder = $order;
        }

        $hasContracts = rate_contracts_available($pdo);

        /*
           Pass 1: the agent's CONTRACT, then the plan within it matching the
           requested meal plan.

           This is the fix for agents being pinned to a single meal plan. The
           old join was `ag.rate_plan_id = rp.id` - a scalar, so an agent could
           only ever reach ONE plan, and every plan carries exactly one
           meal_plan. Asking a BB-linked agent for HB matched nothing and fell
           through to rack, silently and at the wrong price.
        */
        if ($agentId && $agentId > 0 && $hasContracts) {
            try {
                $stmt = $pdo->prepare($select . "
                    INNER JOIN agents ag
                        ON rp.contract_id = CASE
                            WHEN :total_rooms > 1
                                 AND COALESCE(ag.group_rate_contract_id, 0) > 0
                            THEN ag.group_rate_contract_id
                            ELSE ag.rate_contract_id
                        END
                    WHERE ag.id = :agent_id
                      AND rp.contract_id IS NOT NULL
                      AND pp.room_type_id = :room_type_id
                      AND pp.status = 1
                      AND rp.status = 1
                      AND rp.meal_plan = :meal_plan
                      AND pp.valid_from <= :stay_date_from
                      AND pp.valid_to   >= :stay_date_to
                " . $order);
                $stmt->execute([
                    ':agent_id'       => $agentId,
                    ':room_type_id'   => $roomTypeId,
                    ':meal_plan'      => $mealPlan,
                    ':stay_date_from' => $stayDate,
                    ':stay_date_to'   => $stayDate,
                    ':total_rooms'    => $totalRooms,
                ]);
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($row) {
                    $row['rate_source'] = 'agent';
                    return $row;
                }
            } catch (Throwable $e) { /* fall through */ }
        }

        /*
           Pass 1b: the legacy scalar link. Kept so any agent not yet repointed
           to a contract resolves exactly as it did before this change. Only
           reached when the contract path found nothing.
        */
        if ($agentId && $agentId > 0) {
            try {
                $stmt = $pdo->prepare($select . "
                    INNER JOIN agents ag
                        ON rp.id = CASE
                            WHEN :total_rooms > 1
                                 AND COALESCE(ag.group_rate_plan_id, 0) > 0
                            THEN ag.group_rate_plan_id
                            ELSE ag.rate_plan_id
                        END
                    WHERE ag.id = :agent_id
                      AND pp.room_type_id = :room_type_id
                      AND pp.status = 1
                      AND rp.status = 1
                      AND rp.meal_plan = :meal_plan
                      AND pp.valid_from <= :stay_date_from
                      AND pp.valid_to   >= :stay_date_to
                " . $order);
                $stmt->execute([
                    ':agent_id'       => $agentId,
                    ':room_type_id'   => $roomTypeId,
                    ':meal_plan'      => $mealPlan,
                    ':stay_date_from' => $stayDate,
                    ':stay_date_to'   => $stayDate,
                    ':total_rooms'    => $totalRooms,
                ]);
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($row) {
                    $row['rate_source'] = 'agent';
                    return $row;
                }
            } catch (Throwable $e) { /* fall through */ }
        }

        /*
           Pass 1c: the contract exists but holds no plan for this meal plan -
           e.g. BUSHWAYS is BB-only and the booking wants HB. Take the
           contract's base rate (BB preferred) and add the configured meal
           supplement, rather than dropping to a rack rate the agent never
           agreed to. Flagged so the UI can show the rate was derived.
        */
        if ($agentId && $agentId > 0 && $hasContracts) {
            try {
                $stmt = $pdo->prepare($select . "
                    INNER JOIN agents ag
                        ON rp.contract_id = CASE
                            WHEN :total_rooms > 1
                                 AND COALESCE(ag.group_rate_contract_id, 0) > 0
                            THEN ag.group_rate_contract_id
                            ELSE ag.rate_contract_id
                        END
                    WHERE ag.id = :agent_id
                      AND rp.contract_id IS NOT NULL
                      AND pp.room_type_id = :room_type_id
                      AND pp.status = 1
                      AND rp.status = 1
                      AND pp.valid_from <= :stay_date_from
                      AND pp.valid_to   >= :stay_date_to
                    ORDER BY (rp.meal_plan = 'BB') DESC,
                             DATEDIFF(pp.valid_to, pp.valid_from) ASC,
                             pp.id DESC
                    LIMIT 1
                ");
                $stmt->execute([
                    ':agent_id'       => $agentId,
                    ':room_type_id'   => $roomTypeId,
                    ':stay_date_from' => $stayDate,
                    ':stay_date_to'   => $stayDate,
                    ':total_rooms'    => $totalRooms,
                ]);
                $base = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($base) {
                    $supp = lookup_meal_supplement($pdo, $propertyId, $mealPlan, $stayDate);
                    if ($supp) {
                        $adultSupp = (float)$supp['adult_supplement'];
                        $base['derived_from_meal_plan']  = $base['meal_plan'];
                        $base['derived_supplement']      = $adultSupp;
                        $base['derived_from_supplement'] = true;
                        $base['sharing_rate'] = (float)$base['sharing_rate'] + $adultSupp;
                        if ((float)$base['single_rate'] > 0) {
                            $base['single_rate'] = (float)$base['single_rate'] + $adultSupp;
                        }
                        // Child supplement rides on child_rate_percent, which is
                        // a percentage of sharing_rate - leave it to the caller
                        // and expose the figure instead.
                        $base['derived_child_supplement'] = (float)$supp['child_supplement'];
                        $base['meal_plan']   = $mealPlan;
                        $base['rate_source'] = 'agent_derived';
                        return $base;
                    }
                    // No supplement configured. Do NOT price it - fall through
                    // to rack, but the caller is told the contract had a gap.
                }
            } catch (Throwable $e) { /* fall through */ }
        }

        /*
           Pass 2: the public/rack rate, identified by the EXPLICIT is_public
           flag - never by "this plan has no agents attached". Several migrated
           Semper contracts (GATE 1, BZZ ODYSSEYS, BAOBAB HB...) currently have
           no agents linked; treating those as rack would quote a negotiated
           contract rate to a walk-in guest.
        */
        try {
            $stmt = $pdo->prepare($select . "
                WHERE pp.room_type_id = :room_type_id
                  AND pp.status = 1
                  AND rp.status = 1
                  AND rp.is_public = 1
                  AND rp.meal_plan = :meal_plan
                  AND pp.valid_from <= :stay_date_from
                  AND pp.valid_to   >= :stay_date_to
            " . $publicOrder);
            $stmt->execute([
                ':room_type_id'   => $roomTypeId,
                ':meal_plan'      => $mealPlan,
                ':stay_date_from' => $stayDate,
                ':stay_date_to'   => $stayDate,
            ]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row) {
                $row['rate_source'] = ($agentId && $agentId > 0) ? 'public_fallback' : 'public';
                if ($agentId && $agentId > 0) {
                    // The agent had a contract but nothing in it covered this
                    // meal plan / room type / date. Surface it.
                    $row['unresolved_meal_plan'] = $mealPlan;
                }
                return $row;
            }
        } catch (Throwable $e) { /* fall through to legacy */ }

        // Nothing in the new tables - the legacy rows still hold the rack rates.
        return fetch_rate_plan_legacy($pdo, $roomTypeId, $agentId, $stayDate, $mealPlan);
    }
}

if (!function_exists('fetch_rate_plan_legacy')) {
    function fetch_rate_plan_legacy(PDO $pdo, int $roomTypeId, ?int $agentId, string $stayDate, string $mealPlan): ?array
    {
        $mealPlan = normalize_meal_plan_for_rates($mealPlan);
        // Same specificity fix applied to the old flat table.
        $order = " ORDER BY DATEDIFF(valid_to, valid_from) ASC, id DESC LIMIT 1 ";

        if ($agentId && $agentId > 0) {
            $stmt = $pdo->prepare("
                SELECT * FROM room_type_rate_plans
                WHERE room_type_id = :room_type_id_agent
                  AND status = 1
                  AND valid_from <= :stay_date_agent_from
                  AND valid_to >= :stay_date_agent_to
                  AND agent_id = :agent_id_match
                  AND meal_plan = :meal_plan_agent
            " . $order);
            $stmt->execute([
                ':room_type_id_agent'   => $roomTypeId,
                ':stay_date_agent_from' => $stayDate,
                ':stay_date_agent_to'   => $stayDate,
                ':agent_id_match'       => $agentId,
                ':meal_plan_agent'      => $mealPlan,
            ]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row) {
                $row['rate_source'] = 'agent';
                return $row;
            }
        }

        $stmt = $pdo->prepare("
            SELECT * FROM room_type_rate_plans
            WHERE room_type_id = :room_type_id_default
              AND status = 1
              AND valid_from <= :stay_date_default_from
              AND valid_to >= :stay_date_default_to
              AND agent_id IS NULL
              AND meal_plan = :meal_plan_default
        " . $order);
        $stmt->execute([
            ':room_type_id_default'   => $roomTypeId,
            ':stay_date_default_from' => $stayDate,
            ':stay_date_default_to'   => $stayDate,
            ':meal_plan_default'      => $mealPlan,
        ]);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $row['rate_source'] = ($agentId && $agentId > 0) ? 'public_fallback' : 'public';
            return $row;
        }
        return null;
    }
}

if (!function_exists('calculate_room_night_total')) {
    function calculate_room_night_total(
        int $adults,
        int $children,
        float $sharingRate,
        float $singleRate,
        float $singleSupplement,
        float $childPercent = 50.0
    ): array {
        $adults = max(0, $adults);
        $children = max(0, $children);
        $childPercent = max(0, $childPercent);

        if ($adults <= 0) {
            return [
                'adult_total'       => 0.00,
                'child_total'       => 0.00,
                'room_total'        => 0.00,
                'applied_rate_type' => 'empty_room',
                'rate_label'        => 'Empty',
            ];
        }

        if ($adults === 1) {
            $adultTotal = $singleRate > 0
                ? $singleRate
                : ($sharingRate + $singleSupplement);

            $appliedType = $singleRate > 0
                ? 'single_rate'
                : 'sharing_plus_single_supplement';

            $rateLabel = $singleRate > 0 ? 'Single' : 'Sharing + Supplement';
        } else {
            $adultTotal = $adults * $sharingRate;
            $appliedType = 'sharing';
            $rateLabel = 'Sharing';
        }

        $childRate  = $sharingRate * ($childPercent / 100);
        $childTotal = $children * $childRate;
        $roomTotal  = $adultTotal + $childTotal;

        return [
            'adult_total'       => round($adultTotal, 2),
            'child_total'       => round($childTotal, 2),
            'room_total'        => round($roomTotal, 2),
            'applied_rate_type' => $appliedType,
            'rate_label'        => $rateLabel,
        ];
    }
}
