<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    $ts = date('Y-m-d H:i:s');

    // ─── 1. Add "above_medium" qualification if missing ───
    $aboveMedium = $db->selectOne("SELECT id FROM qualifications WHERE code = 'above_medium'");
    if (!$aboveMedium) {
        $db->insert('qualifications', [
            'code'       => 'above_medium',
            'name_ar'    => 'مؤهل فوق المتوسط',
            'name_en'    => 'Above Medium Qualification',
            'sort_order' => 2,
            'is_active'  => 1,
        ]);
        // Push medium and none down
        $db->query("UPDATE qualifications SET sort_order = 3 WHERE code = 'medium'", []);
        $db->query("UPDATE qualifications SET sort_order = 4 WHERE code = 'none'", []);
    }

    // ─── 2. Add pricing config for above_medium (187,500) ───
    $aboveMediumId = (int) ($db->selectOne("SELECT id FROM qualifications WHERE code = 'above_medium'")['id'] ?? 0);
    if ($aboveMediumId > 0) {
        $branches = $db->select("SELECT id, branch_code FROM branches WHERE is_active = 1");
        foreach ($branches as $branch) {
            $existing = $db->selectOne(
                "SELECT id FROM pricing_configs WHERE branch_id = ? AND qualification_id = ? AND effective_from = '2024-07-01' AND membership_type = 'working'",
                [(int) $branch['id'], $aboveMediumId]
            );
            if (!$existing) {
                $db->insert('pricing_configs', [
                    'branch_id'        => (int) $branch['id'],
                    'qualification_id' => $aboveMediumId,
                    'membership_type'  => 'working',
                    'price'            => '187500.00',
                    'currency'         => 'EGP',
                    'effective_from'   => '2024-07-01',
                    'effective_to'     => null,
                    'is_active'        => 1,
                    'created_at'       => $ts,
                    'updated_at'       => $ts,
                ]);
            }
        }
    }

    // ─── 3. Create Board Offer: "عروض عضويات شيراتون 2026" ───
    $existingOffer = $db->selectOne(
        "SELECT id FROM board_offers WHERE title_ar = 'عروض عضويات شيراتون 2026'"
    );

    if ($existingOffer) {
        return;
    }

    $offerId = $db->insert('board_offers', [
        'title_ar'              => 'عروض عضويات شيراتون 2026',
        'title_en'              => 'Sheraton Membership Offers 2026',
        'description'           => 'عروض الدفع للعضويات — كاش بخصم 10% أو تقسيط على 24/40/60 شهر',
        'cash_discount_type'    => 'percentage',
        'cash_discount_value'   => '10.00',
        'inst_down_payment_pct' => '50.00',
        'inst_months'           => 24,
        'inst_interest_rate'    => '0.00',
        'inst_grace_type'       => null,
        'inst_grace_months'     => 0,
        'inst_post_grace_rate'  => null,
        'branch_id'             => null,
        'applies_to'            => 'membership_fee',
        'effective_from'        => '2026-01-01',
        'effective_to'          => '2026-12-31',
        'is_active'             => 1,
        'board_decision_number' => null,
        'board_decision_date'   => null,
        'notes'                 => 'من ملف اسعار عضويات شيراتون — 4 مستويات دفع',
        'created_by'            => null,
        'updated_by'            => null,
        'created_at'            => $ts,
        'updated_at'            => $ts,
    ]);

    // ─── 4. Create 4 Tiers ───
    $tiers = [
        [
            'tier_order'        => 1,
            'tier_name_ar'      => 'كاش كامل — خصم 10%',
            'payment_type'      => 'cash',
            'cash_discount_pct' => '10.00',
            'down_payment_pct'  => null,
            'months'            => null,
            'interest_rate'     => null,
        ],
        [
            'tier_order'        => 2,
            'tier_name_ar'      => 'تقسيط 24 شهر — بدون فوائد',
            'payment_type'      => 'installment',
            'cash_discount_pct' => null,
            'down_payment_pct'  => '50.00',
            'months'            => 24,
            'interest_rate'     => '0.00',
        ],
        [
            'tier_order'        => 3,
            'tier_name_ar'      => 'تقسيط 40 شهر — فائدة 15%',
            'payment_type'      => 'installment',
            'cash_discount_pct' => null,
            'down_payment_pct'  => '33.33',
            'months'            => 40,
            'interest_rate'     => '15.00',
        ],
        [
            'tier_order'        => 4,
            'tier_name_ar'      => 'تقسيط 60 شهر — فائدة 15%',
            'payment_type'      => 'installment',
            'cash_discount_pct' => null,
            'down_payment_pct'  => '13.33',
            'months'            => 60,
            'interest_rate'     => '15.00',
        ],
    ];

    foreach ($tiers as $tier) {
        $db->insert('board_offer_tiers', array_merge($tier, [
            'board_offer_id' => $offerId,
            'is_active'      => 1,
            'created_at'     => $ts,
            'updated_at'     => $ts,
        ]));
    }
};
