<?php
namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use App\Models\{
    Quarry, PitBench, CrusherLine, Product, Stockpile, Warehouse, StockItem, Equipment, EquipmentDocument,
    Customer, Supplier, Transporter, PriceList, PriceListLine, Department, Designation, Employee,
    Project, ProjectTask, BoqItem, Subcontract, Unit, MaintenanceSchedule
};

class MasterSeeder extends Seeder {
    public function run(): void {
        $t  = Unit::where('symbol', 't')->value('id');
        $l  = Unit::where('symbol', 'L')->value('id');
        $ea = Unit::where('symbol', 'ea')->value('id');
        $kg = Unit::where('symbol', 'kg')->value('id');
        $bag = Unit::where('symbol', 'bag')->value('id');
        $m  = Unit::where('symbol', 'm')->value('id');

        $sh = Quarry::updateOrCreate(['code' => 'SH-01'], [
            'name' => 'Nowills Shai Hills Quarry', 'location' => 'Shai Hills', 'region' => 'Greater Accra',
            'gps_coordinates' => '5.9236, 0.0524', 'licence_no' => 'MC/SH/2019/041', 'licence_expiry' => '2029-06-30',
            'epa_permit_no' => 'EPA/GR/2021/118', 'epa_expiry' => '2027-03-31', 'is_active' => true,
        ]);
        $ns = Quarry::updateOrCreate(['code' => 'NS-02'], [
            'name' => 'Nowills Nsawam Quarry', 'location' => 'Nsawam', 'region' => 'Eastern',
            'gps_coordinates' => '5.8034, -0.3505', 'licence_no' => 'MC/NS/2020/077', 'licence_expiry' => '2030-11-30',
            'epa_permit_no' => 'EPA/ER/2022/064', 'epa_expiry' => '2027-09-30', 'is_active' => true,
        ]);
        foreach ([[$sh, 'B1', 'Bench 1 — North Face', 12, 'active'], [$sh, 'B2', 'Bench 2 — Central', 14, 'active'], [$sh, 'B3', 'Bench 3 — South Extension', 10, 'development'],
                 [$ns, 'B1', 'Bench 1 — Ridge Face', 13, 'active'], [$ns, 'B2', 'Bench 2 — Valley Cut', 11, 'active']] as [$q, $cd, $nm, $ht, $st]) {
            PitBench::updateOrCreate(['quarry_id' => $q->id, 'code' => $cd], [
                'name' => $nm, 'bench_height' => $ht, 'status' => $st]);
        }
        foreach ([[$sh, 'CR-SH-01', 'Primary Jaw Line — Shai Hills', 'jaw', 250], [$sh, 'CR-SH-02', 'Secondary Cone Line — Shai Hills', 'cone', 180],
                 [$ns, 'CR-NS-01', 'Jaw Line — Nsawam', 'jaw', 200]] as [$q, $cd, $nm, $tp, $cp]) {
            CrusherLine::updateOrCreate(['code' => $cd], [
                'quarry_id' => $q->id, 'name' => $nm, 'crusher_type' => $tp, 'rated_capacity_tph' => $cp, 'is_active' => true]);
        }

        $agg = 1; $dst = 2; $bld = 3;
        $prods = [
            ['AGG-05', 'Quarry Dust 0–5mm', $dst, 45, 26, 'Fine dust for block making and bedding'],
            ['AGG-10', 'Chippings 5–10mm', $agg, 95, 58, 'Concrete chippings'],
            ['AGG-14', 'Chippings 10–14mm', $agg, 105, 62, 'Concrete chippings'],
            ['AGG-20', 'Chippings 14–20mm', $agg, 110, 65, 'Premium concrete aggregate'],
            ['AGG-40', 'Hardcore 20–40mm', $agg, 100, 60, 'Road base hardcore'],
            ['AGG-60', 'Hardcore 40–60mm', $agg, 85, 52, 'Foundation hardcore'],
            ['BLD-01', 'Boulders / Armour Rock', $bld, 70, 40, 'Sea defence & gabion rock'],
            ['SND-M', 'Manufactured Sand', $dst, 60, 34, 'Washed crusher sand'],
        ];
        $pids = [];
        foreach ($prods as [$cd, $nm, $ct, $sr, $cr, $ds]) {
            $p = Product::updateOrCreate(['code' => $cd], [
                'name' => $nm, 'category_id' => $ct, 'unit_id' => $t, 'standard_rate' => $sr, 'cost_rate' => $cr,
                'description' => $ds, 'taxable' => true, 'is_sellable' => true, 'min_stock_level' => 200, 'is_active' => true]);
            $pids[$cd] = $p->id;
        }
        $spn = 1;
        foreach ([$sh, $ns] as $q) {
            foreach (['AGG-20', 'AGG-14', 'AGG-10', 'AGG-05', 'AGG-40'] as $pc) {
                Stockpile::updateOrCreate(['quarry_id' => $q->id, 'product_id' => $pids[$pc]], [
                    'code' => 'SP-'.str_pad((string) $spn++, 4, '0', STR_PAD_LEFT),
                    'location' => "Bay $pc", 'capacity_tonnes' => 20000, 'current_qty' => 0]);
            }
        }

        $hq = Warehouse::updateOrCreate(['code' => 'WH-HQ'], ['name' => 'Main Store — Head Office', 'location' => 'Tema HQ', 'keeper_name' => 'Ama Serwaa', 'is_active' => true]);
        $shs = Warehouse::updateOrCreate(['code' => 'WH-SH'], ['name' => 'Shai Hills Site Store', 'quarry_id' => $sh->id, 'location' => 'Shai Hills', 'keeper_name' => 'Yaw Antwi', 'is_active' => true]);
        $fuel = Warehouse::updateOrCreate(['code' => 'WH-FB'], ['name' => 'Fuel Bay — Shai Hills', 'quarry_id' => $sh->id, 'location' => 'Shai Hills', 'keeper_name' => 'Yaw Antwi', 'is_active' => true]);

        // [code, name, category, unit, last_rate, reorder, max]
        $items = [
            ['DSL', 'Diesel (AGO)', 'fuel-lubricant', $l, 14.8, 2000, 20000],
            ['PTR', 'Petrol (PMS)', 'fuel-lubricant', $l, 13.5, 500, 4000],
            ['OIL-15W', 'Engine Oil 15W40 (20L)', 'fuel-lubricant', $ea, 1450, 10, 60],
            ['HYD-68', 'Hydraulic Oil H68 (20L)', 'fuel-lubricant', $ea, 1620, 8, 40],
            ['GRS', 'Grease MP3 (15kg)', 'fuel-lubricant', $ea, 890, 6, 40],
            ['ANFO', 'ANFO Explosive', 'explosive', $kg, 38, 500, 5000],
            ['EML-32', 'Emulsion Cartridge 32mm', 'explosive', $ea, 24, 200, 2000],
            ['DET-E', 'Electric Detonators', 'explosive', $ea, 18, 300, 3000],
            ['CRDTX', 'Cordtex Detonating Cord', 'explosive', $m, 12, 400, 4000],
            ['JAW-PLT', 'Jaw Plates Set (Metso)', 'spare-part', $ea, 48500, 1, 3],
            ['CONE-LN', 'Cone Liners Set', 'spare-part', $ea, 62000, 1, 2],
            ['BELT-800', 'Conveyor Belt 800mm', 'spare-part', $m, 980, 50, 300],
            ['DRILL-BT', 'Drill Bits 76mm', 'consumable', $ea, 2400, 8, 40],
            ['FLT-KIT', 'Service Filters Kit', 'spare-part', $ea, 1850, 12, 60],
            ['TYRE-235', 'Tyre 23.5-25 Loader', 'spare-part', $ea, 14500, 2, 8],
            ['CEM-50', 'Cement 50kg (Ghacem)', 'consumable', $bag, 118, 200, 2000],
            ['WELD-RD', 'Welding Rods (5kg)', 'consumable', $ea, 320, 15, 100],
            ['BOOT-S', 'Safety Boots (Pair)', 'ppe', $ea, 450, 30, 200],
            ['HELM', 'Safety Helmet', 'ppe', $ea, 120, 40, 300],
            ['VEST', 'Reflective Vest', 'ppe', $ea, 65, 60, 500],
        ];
        foreach ($items as [$cd, $nm, $ct, $un, $rt, $ro, $mx]) {
            StockItem::updateOrCreate(['code' => $cd], [
                'name' => $nm, 'category' => $ct, 'unit_id' => $un, 'default_warehouse_id' => $hq->id,
                'reorder_level' => $ro, 'max_level' => $mx, 'last_purchase_rate' => $rt, 'avg_cost_rate' => $rt,
                'current_qty' => 0, 'is_active' => true]);
        }

        // [code, name, category, make, model, year, reg, fuel, hours, km, status, quarry]
        $eqs = [
            ['EQ-EX-001', 'CAT 336 Excavator', 'excavator', 'CAT', '336 GC', '2021', 'SH-EX-21-01', 'diesel', 18450, 0, 'available', $sh->id],
            ['EQ-EX-002', 'Komatsu PC210 Excavator', 'excavator', 'Komatsu', 'PC210LC', '2019', 'NS-EX-19-01', 'diesel', 22100, 0, 'available', $ns->id],
            ['EQ-WL-001', 'CAT 950 Wheel Loader', 'wheel-loader', 'CAT', '950 GC', '2020', 'SH-WL-20-01', 'diesel', 15800, 0, 'available', $sh->id],
            ['EQ-DT-001', 'HOWO Dump Truck', 'dump-truck', 'Sinotruk', 'HOWO 371', '2022', 'GR 4521-22', 'diesel', 9600, 142000, 'available', $sh->id],
            ['EQ-DT-002', 'HOWO Dump Truck', 'dump-truck', 'Sinotruk', 'HOWO 371', '2022', 'GR 4522-22', 'diesel', 9100, 138500, 'down-maintenance', $sh->id],
            ['EQ-DT-003', 'HOWO Dump Truck', 'dump-truck', 'Sinotruk', 'HOWO 371', '2023', 'GR 7810-23', 'diesel', 6200, 88400, 'available', $ns->id],
            ['EQ-DT-004', 'MAN Tipper Truck', 'dump-truck', 'MAN', 'TGS 33.440', '2020', 'GR 3105-20', 'diesel', 12400, 196000, 'available', $ns->id],
            ['EQ-DZ-001', 'CAT D6 Dozer', 'dozer', 'CAT', 'D6 XE', '2018', 'SH-DZ-18-01', 'diesel', 24500, 0, 'available', $sh->id],
            ['EQ-DR-001', 'Atlas Copco Drill Rig', 'drill-rig', 'Epiroc', 'FlexiROC T30', '2021', 'SH-DR-21-01', 'diesel', 7300, 0, 'available', $sh->id],
            ['EQ-GN-001', '250kVA Generator', 'generator', 'Perkins', '2506A', '2020', 'SH-GN-20-01', 'diesel', 8900, 0, 'available', $sh->id],
            ['EQ-PK-001', 'Toyota Hilux Pickup', 'pickup', 'Toyota', 'Hilux 2.8', '2023', 'GR 9902-23', 'diesel', 2100, 45600, 'available', null],
            ['EQ-WB-001', 'Water Bowser', 'water-bowser', 'MAN', 'TGM 18.250', '2019', 'GR 2210-19', 'diesel', 11800, 174000, 'available', $sh->id],
        ];
        $eids = [];
        foreach ($eqs as [$cd, $nm, $ct, $mk, $md, $yr, $rg, $fl, $hs, $km, $st, $qy]) {
            $e = Equipment::updateOrCreate(['code' => $cd], [
                'name' => $nm, 'category' => $ct, 'make' => $mk, 'model_no' => $md, 'year_of_manufacture' => $yr,
                'serial_no' => 'SN-'.$cd, 'registration_no' => $rg, 'ownership' => 'owned', 'fuel_type' => $fl,
                'current_hours' => $hs, 'current_km' => $km, 'meter_type' => $km > 0 ? 'both' : 'hours',
                'status' => $st, 'current_quarry_id' => $qy, 'purchase_date' => "$yr-03-15",
                'purchase_cost' => 50000 * (int) substr($yr, 2), 'is_active' => true]);
            $eids[$cd] = $e->id;
        }
        foreach (['EQ-DT-001' => ['2026-12-31', '2027-02-28'], 'EQ-DT-002' => ['2026-10-15', '2026-09-30'], 'EQ-PK-001' => ['2027-05-31', '2027-01-31']] as $cd => [$ins, $rw]) {
            EquipmentDocument::updateOrCreate(['equipment_id' => $eids[$cd], 'doc_type' => 'insurance'], ['doc_no' => 'SIC-'.$cd, 'expiry_date' => $ins, 'issued_by' => 'SIC Insurance']);
            EquipmentDocument::updateOrCreate(['equipment_id' => $eids[$cd], 'doc_type' => 'roadworthy'], ['doc_no' => 'DVLA-'.$cd, 'expiry_date' => $rw, 'issued_by' => 'DVLA']);
        }
        foreach ([['EQ-EX-001', 'Full service — 500hr', 500, 'hours', 18950], ['EQ-DT-001', 'Engine service — 10,000km', 10000, 'km', 152000], ['EQ-GN-001', 'Generator service — 250hr', 250, 'hours', 9150]] as [$cd, $ds, $iv, $un, $nx]) {
            MaintenanceSchedule::updateOrCreate(['equipment_id' => $eids[$cd], 'service_type' => $ds], [
                'interval_value' => $iv, 'interval_unit' => $un, 'next_due_meter' => $nx, 'is_active' => true]);
        }

        $std = PriceList::updateOrCreate(['name' => 'Standard Price List 2026'], ['currency' => 'GHS', 'effective_from' => '2026-01-01', 'is_default' => true, 'is_active' => true]);
        foreach ($pids as $cd => $pid) {
            PriceListLine::updateOrCreate(['price_list_id' => $std->id, 'product_id' => $pid], [
                'rate_per_tonne' => Product::find($pid)->standard_rate, 'min_qty' => 1]);
        }
        $sp = PriceList::updateOrCreate(['name' => 'Project Special Rates 2026'], ['currency' => 'GHS', 'effective_from' => '2026-01-01', 'is_default' => false, 'is_active' => true]);
        foreach ($pids as $cd => $pid) {
            PriceListLine::updateOrCreate(['price_list_id' => $sp->id, 'product_id' => $pid], [
                'rate_per_tonne' => round(Product::find($pid)->standard_rate * 0.95, 2), 'min_qty' => 500]);
        }

        // [name, type, phone, email, address, city, tin, credit, terms]
        $custs = [
            ['Consar Construct Ltd', 'company', '0208111111', 'orders@consar.com.gh', 'Tema Motorway, Accra', 'Accra', 'C0001111111', 500000, 30],
            ['Kofi Estates Ltd', 'company', '0208222222', 'site@estates.com.gh', 'East Legon Hills, Accra', 'Accra', 'C0002222222', 750000, 30],
            ['Atlantic Civil Works', 'company', '0208333333', 'procure@atlantic.com.gh', 'Spintex Road, Accra', 'Accra', 'C0003333333', 300000, 21],
            ['Danso Builders', 'company', '0208444444', 'info@dansobuilders.com', 'Kasoa', 'Kasoa', 'C0004444444', 150000, 14],
            ['Bright Future Developers', 'company', '0208555555', 'accounts@brightfuture.com.gh', 'Dawhenya', 'Dawhenya', 'C0005555555', 1000000, 45],
            ['Ghana Road Contractors', 'company', '0208666666', 'supply@grc.com.gh', 'Takoradi', 'Takoradi', 'C0006666666', 400000, 30],
            ['Nana Yaw (Private)', 'individual', '0244777777', null, 'Adenta, Accra', 'Accra', null, 20000, 0],
            ['Safe Homes Ltd', 'company', '0208888888', 'admin@safehomes.com', 'Weija, Accra', 'Accra', 'C0008888888', 100000, 21],
        ];
        $cids = []; $cn = 1;
        foreach ($custs as [$nm, $tp, $ph, $em, $ad, $ct, $tn, $cl2, $pt]) {
            $c = Customer::updateOrCreate(['name' => $nm], ['code' => 'CUS-'.str_pad((string) $cn++, 3, '0', STR_PAD_LEFT),
                'customer_type' => $tp, 'phone' => $ph, 'email' => $em, 'address' => $ad, 'city' => $ct,
                'tin' => $tn, 'price_list_id' => $std->id, 'credit_limit' => $cl2, 'payment_terms_days' => $pt,
                'opening_balance' => 0, 'current_balance' => 0, 'is_active' => true]);
            $cids[$nm] = $c->id;
        }
        // [name, phone, email, address, city, tin, terms, type]
        $sups = [
            ['TotalEnergies Ghana', '0249111111', 'b2b@totalenergies.com.gh', 'Liberation Road, Accra', 'Accra', 'C0011111111', 30, 'fuel'],
            ['GOIL Company Ltd', '0249222222', 'bulk@goil.com.gh', 'Kojo Thompson Road, Accra', 'Accra', 'C0012222222', 30, 'fuel'],
            ['Mantrac Ghana Ltd', '0249333333', 'parts@mantrac.com.gh', 'Tema Heavy Industrial Area', 'Tema', 'C0013333333', 30, 'spares'],
            ['CFAO Ghana', '0249444444', 'sales@cfao.com.gh', 'Accra', 'Accra', 'C0014444444', 30, 'spares'],
            ['Maxam Ghana Ltd', '0249555555', 'orders@maxam.com.gh', 'Tarkwa', 'Tarkwa', 'C0015555555', 14, 'explosives'],
            ['Ghacem Ltd', '0249666666', 'sales@ghacem.com', 'Tema', 'Tema', 'C0016666666', 21, 'materials'],
            ['Melcom Ltd', '0249777777', 'corporate@melcom.com', 'Accra', 'Accra', 'C0017777777', 14, 'materials'],
            ['Japan Motors', '0249888888', 'fleet@japanmotors.com', 'Accra', 'Accra', 'C0018888888', 30, 'services'],
        ];
        $sids = []; $sn2 = 1;
        foreach ($sups as [$nm, $ph, $em, $ad, $ct, $tn, $pt, $ty]) {
            $s = Supplier::updateOrCreate(['name' => $nm], ['code' => 'SUP-'.str_pad((string) $sn2++, 3, '0', STR_PAD_LEFT),
                'phone' => $ph, 'email' => $em, 'address' => $ad, 'city' => $ct,
                'tin' => $tn, 'payment_terms_days' => $pt, 'supplier_type' => $ty,
                'opening_balance' => 0, 'current_balance' => 0, 'is_active' => true]);
            $sids[$nm] = $s->id;
        }
        foreach ([['Owusu Haulage Ltd', 'Yaw Owusu', '0244111222', 'GR 1000-20', 45], ['Swift Trans Ghana', 'Abena Owusu', '0244333444', 'GR 2000-21', 42], ['Adom Logistics', 'Kwesi Adom', '0244555666', 'GR 3000-22', 40]] as [$nm, $ct, $ph, $vh, $rt]) {
            Transporter::updateOrCreate(['name' => $nm], ['contact_person' => $ct, 'phone' => $ph, 'vehicle_no' => $vh, 'rate_per_tonne' => $rt, 'is_active' => true]);
        }

        $d = fn($c) => Department::where('code', $c)->value('id');
        $g = fn($t) => Designation::where('title', $t)->value('id');
        // [first, last, gender, dob, phone, email, dept, desig, hired, type, basic, allowances, bank, acct, ssnit]
        $staff = [
            ['Yaw', 'Mensah', 'male', '1985-04-12', '0244123456', 'yaw.mensah@nowills.com.gh', 'QRY', 'Quarry Manager', '2020-02-01', 'permanent', 18000, ['housing' => 3000, 'transport' => 1500], 'GCB', '1111111111111', 'SSNIT-001'],
            ['Kwesi', 'Owusu', 'male', '1988-09-03', '0244234567', null, 'QRY', 'Quarry Manager', '2021-06-01', 'permanent', 15000, ['housing' => 2500, 'transport' => 1200], 'GCB', '1111111111112', 'SSNIT-002'],
            ['Ama', 'Serwaa', 'female', '1990-01-22', '0244345678', 'ama.serwaa@nowills.com.gh', 'FIN', 'Accountant', '2019-03-01', 'permanent', 12000, ['housing' => 2000, 'transport' => 1000], 'Ecobank', '2222222222222', 'SSNIT-003'],
            ['Kwame', 'Asante', 'male', '1992-07-11', '0244456789', null, 'HRM', 'HR Officer', '2021-01-15', 'permanent', 9000, ['transport' => 800], 'GCB', '1111111111113', 'SSNIT-004'],
            ['Kofi', 'Boateng', 'male', '1994-11-30', '0244567890', 'kofi.boateng@nowills.com.gh', 'SAL', 'Sales Officer', '2022-05-01', 'permanent', 7500, ['transport' => 800, 'commission' => 500], 'Absa', '3333333333333', 'SSNIT-005'],
            ['Abena', 'Osei', 'female', '1995-05-18', '0244678901', null, 'SAL', 'Weighbridge Clerk', '2023-02-01', 'permanent', 4200, ['transport' => 500], 'GCB', '1111111111114', 'SSNIT-006'],
            ['Yaw', 'Antwi', 'male', '1989-02-14', '0244789012', null, 'MTN', 'Store Keeper', '2020-08-01', 'permanent', 6500, ['transport' => 600], 'GCB', '1111111111115', 'SSNIT-007'],
            ['Kojo', 'Appiah', 'male', '1991-12-05', '0244890123', null, 'MTN', 'Mechanic', '2021-09-01', 'permanent', 6800, ['transport' => 600, 'overtime' => 400], 'GCB', '1111111111116', 'SSNIT-008'],
            ['Esi', 'Koomson', 'female', '1993-08-27', '0244901234', null, 'PRD', 'QC Technician', '2022-01-10', 'permanent', 6200, ['transport' => 600], 'Fidelity', '4444444444444', 'SSNIT-009'],
            ['Kwabena', 'Frimpong', 'male', '1987-06-19', '0244012345', null, 'QRY', 'Excavator Operator', '2019-07-01', 'permanent', 5800, ['risk' => 700, 'transport' => 500], 'GCB', '1111111111117', 'SSNIT-010'],
            ['Yaw', 'Darko', 'male', '1990-10-08', '0244123457', null, 'QRY', 'Dumper Driver', '2020-11-01', 'permanent', 4800, ['risk' => 500, 'transport' => 500], 'GCB', '1111111111118', 'SSNIT-011'],
            ['Ama', 'Konadu', 'female', '1996-03-25', '0244234568', null, 'HSE', 'HSE Officer', '2023-06-01', 'permanent', 8200, ['transport' => 700], 'Stanbic', '5555555555555', 'SSNIT-012'],
            ['Kwaku', 'Manu', 'male', '1986-12-17', '0244345679', null, 'PRJ', 'Project Manager', '2018-04-01', 'permanent', 16000, ['housing' => 2800, 'transport' => 1400], 'GCB', '1111111111119', 'SSNIT-013'],
            ['Adjoa', 'Mensimah', 'female', '1998-09-09', '0244456790', null, 'PRD', 'Crusher Operator', '2024-01-15', 'contract', 5200, ['shift' => 600], 'GCB', '1111111111120', 'SSNIT-014'],
        ];
        $eno = 1;
        foreach ($staff as [$fn, $ln, $sx, $db, $ph, $em, $dp, $ds, $hj, $et, $bs, $al, $bk, $ac, $sn3]) {
            Employee::updateOrCreate(['ssnit_no' => $sn3], [
                'employee_no' => 'EMP-'.str_pad((string) $eno++, 4, '0', STR_PAD_LEFT),
                'first_name' => $fn, 'last_name' => $ln, 'gender' => $sx, 'date_of_birth' => $db,
                'phone' => $ph, 'email' => $em, 'department_id' => $d($dp), 'designation_id' => $g($ds),
                'date_employed' => $hj, 'employment_type' => $et, 'basic_salary' => $bs, 'allowances' => $al,
                'bank_name' => $bk, 'account_no' => $ac, 'ssnit_no' => $sn3, 'status' => 'active']);
        }
        DB::table('document_sequences')->where('key', 'employee')->update(['next_number' => 15]);
        $sh->update(['manager_id' => Employee::where('ssnit_no', 'SSNIT-001')->value('id')]);
        $ns->update(['manager_id' => Employee::where('ssnit_no', 'SSNIT-002')->value('id')]);

        $pm13 = Employee::where('ssnit_no', 'SSNIT-013')->value('id');
        $p1 = Project::updateOrCreate(['code' => 'PRJ-2026-001'], [
            'name' => 'East Legon Hills Estate Roads', 'client_id' => $cids['Kofi Estates Ltd'], 'client_name' => 'Kofi Estates Ltd',
            'location' => 'East Legon Hills, Accra', 'region' => 'Greater Accra', 'contract_no' => 'KE/2026/014', 'contract_value' => 4800000,
            'start_date' => '2026-03-01', 'end_date' => '2026-11-30', 'project_manager' => 'Kwaku Manu',
            'status' => 'active', 'progress_percent' => 45, 'retention_percent' => 10, 'is_active' => true]);
        $p2 = Project::updateOrCreate(['code' => 'PRJ-2026-002'], [
            'name' => 'Dawhenya Affordable Housing — Phase 1', 'client_id' => $cids['Bright Future Developers'], 'client_name' => 'Bright Future Developers',
            'location' => 'Dawhenya, Greater Accra', 'region' => 'Greater Accra', 'contract_no' => 'BF/2026/003', 'contract_value' => 12500000,
            'start_date' => '2026-05-15', 'end_date' => '2027-06-30', 'project_manager' => 'Kwaku Manu',
            'status' => 'active', 'progress_percent' => 22, 'retention_percent' => 10, 'is_active' => true]);
        $p3 = Project::updateOrCreate(['code' => 'PRJ-2026-003'], [
            'name' => 'Nsawam Quarry Haul Road Upgrade', 'client_id' => null, 'client_name' => 'Internal — Nowills',
            'location' => 'Nsawam', 'region' => 'Eastern', 'contract_no' => 'NW/INT/2026/01', 'contract_value' => 850000,
            'start_date' => '2026-07-01', 'end_date' => '2026-10-31', 'project_manager' => 'Kwesi Owusu',
            'status' => 'active', 'progress_percent' => 60, 'retention_percent' => 0, 'is_active' => true]);
        $boq = [
            [$p1, '1', 'Earthworks & shaping', 'm³', 12000, 45], [$p1, '2', 'Sub-base hardcore supply & lay', 't', 8500, 140],
            [$p1, '3', 'Prime coat & surfacing', 'm²', 24000, 65], [$p1, '4', 'Drainage culverts', 'm', 900, 380], [$p1, '5', 'Road markings & signs', 'lot', 1, 95000],
            [$p2, '1', 'Foundations — 40 units', 'lot', 1, 2400000], [$p2, '2', 'Blockwork & superstructure', 'lot', 1, 4200000], [$p2, '3', 'Roofing', 'lot', 1, 1800000],
            [$p2, '4', 'Plumbing & electrical first fix', 'lot', 1, 1500000], [$p2, '5', 'Internal roads & drainage', 'lot', 1, 1200000],
            [$p3, '1', 'Regravelling 6km haul road', 'km', 6, 95000], [$p3, '2', 'Culvert crossings', 'no', 4, 28000], [$p3, '3', 'Dust suppression system', 'lot', 1, 65000],
        ];
        foreach ($boq as [$p, $ino, $ds, $un, $q, $rt]) {
            BoqItem::updateOrCreate(['project_id' => $p->id, 'item_no' => $ino], [
                'description' => $ds, 'unit' => $un, 'qty' => $q, 'rate' => $rt, 'amount' => $q * $rt,
                'qty_executed' => 0, 'amount_executed' => 0]);
        }
        // [project, wbs, name, start, end, weight, progress, status, assignee]
        $tasks = [
            [$p1, '1.1', 'Mobilisation & setting out', '2026-03-01', '2026-03-15', 5, 100, 'completed', 'Kwaku Manu'],
            [$p1, '1.2', 'Earthworks section A', '2026-03-16', '2026-06-30', 25, 80, 'in-progress', 'Kwaku Manu'],
            [$p1, '1.3', 'Sub-base section A', '2026-05-01', '2026-08-31', 30, 55, 'in-progress', 'Kwaku Manu'],
            [$p1, '1.4', 'Drainage works', '2026-07-01', '2026-09-30', 20, 30, 'in-progress', 'Kwaku Manu'],
            [$p1, '1.5', 'Surfacing & markings', '2026-09-01', '2026-11-30', 20, 0, 'not-started', 'Kwaku Manu'],
            [$p2, '2.1', 'Foundations Block A–D', '2026-05-15', '2026-09-30', 40, 45, 'in-progress', 'Kwaku Manu'],
            [$p2, '2.2', 'Superstructure Block A–B', '2026-08-01', '2026-12-31', 35, 10, 'in-progress', 'Kwaku Manu'],
            [$p2, '2.3', 'Services first fix', '2026-10-01', '2027-02-28', 25, 0, 'not-started', 'Kwaku Manu'],
            [$p3, '3.1', 'Haul road regravelling', '2026-07-01', '2026-09-15', 70, 75, 'in-progress', 'Kwesi Owusu'],
            [$p3, '3.2', 'Culverts & drainage', '2026-08-15', '2026-10-31', 30, 25, 'in-progress', 'Kwesi Owusu'],
        ];
        foreach ($tasks as [$p, $wbs, $nm, $sd, $ed, $wt, $pc, $st, $as]) {
            ProjectTask::updateOrCreate(['project_id' => $p->id, 'wbs_code' => $wbs], [
                'task_name' => $nm, 'start_date' => $sd, 'end_date' => $ed, 'weight_factor' => $wt,
                'progress_percent' => $pc, 'status' => $st, 'assigned_to' => $as]);
        }
        Subcontract::updateOrCreate(['subcontract_no' => 'SUB-26-00001'], [
            'project_id' => $p1->id, 'subcontractor_name' => 'J. Quarcoo Earthworks', 'scope_of_work' => 'Earthworks section A — cut to spoil and shaping',
            'contract_amount' => 540000, 'start_date' => '2026-03-16', 'end_date' => '2026-06-30',
            'retention_percent' => 5, 'status' => 'active']);
        Subcontract::updateOrCreate(['subcontract_no' => 'SUB-26-00002'], [
            'project_id' => $p2->id, 'subcontractor_name' => 'BuildRight Blockwork', 'scope_of_work' => 'Blockwork superstructure Blocks A–B',
            'contract_amount' => 2100000, 'start_date' => '2026-08-01', 'end_date' => '2026-12-31',
            'retention_percent' => 10, 'status' => 'active']);
        DB::table('document_sequences')->where('key', 'subcontract')->update(['next_number' => 3]);
    }
}
