"""Source register and parameter table for 03_Simulated. Written out as sources.csv and sim_parameters.csv."""
import pandas as pd

SOURCES = [
 ('S01', 'GASTAT Labour Force Survey Q4 2025 - average monthly wages by nationality, sex, education (via GLMM)', 'General Authority for Statistics / Gulf Labour Markets & Migration', '2026', 'https://gulfmigration.grc.net/saudi-arabia-average-monthly-wages-per-paid-employee-in-main-employment-by-nationality-saudi-non-saudi-sex-and-education-level-in-sr-q4-2025/'),
 ('S02', 'Job posting: labour, Jeddah - SAR 1,800-2,000/month + free accommodation, transport, medical, air ticket; SAR 200 food allowance', 'Bureau of Emigration & Overseas Employment, Govt of Pakistan', 'offer expiry 17/04/2025', 'https://beoe.gov.pk/foreign-jobs/3185707'),
 ('S03', 'Job posting: 19 warehouse workers, Riyadh - SAR 1,200-1,400', 'Bureau of Emigration & Overseas Employment, Govt of Pakistan', '2026', 'https://beoe.gov.pk/foreign-jobs/4093177/4576207/19-warehouse-workers-required-in-saudi-arabia-salary-1200-1400-rial-sar'),
 ('S04', 'GOSI contribution rates 2026 (old/new scheme; non-Saudi 2% occupational hazards; base = basic + housing)', 'Noqta (summary of Social Insurance Law)', '2026', 'https://noqta.tn/en/blog/gosi-contribution-rate-salary-deduction-saudi-2026'),
 ('S05', 'GOSI rates increase 1 July 2026 (+0.5% pension each side, new-scheme Saudis)', 'Deel Local Payroll (PaySpace)', '2026-07-02', 'https://payspace.com/blog/saudi-arabia-gosi-contribution-rates-increase-1-july-2026-new-employee-employer-rates'),
 ('S06', 'Expat levy ~SAR 800/worker/month; iqama SAR 650/12 months; employer pays', 'Etqan Law Firm', '2025-07-17', 'https://etqanlawfirm-sa.com/en/labour-iqama-fees-in-saudi-arabia/'),
 ('S07', 'Saudi Labor Law working hours: Art. 98 (8h/day or 48h/week; Ramadan 6h/36h for Muslims), Art. 101 (30-min rest after 5h; max 12h at workplace), Art. 104 (Friday rest)', 'Qoyod (guide to the Labor Law)', '2026', 'https://www.qoyod.com/en/learn/working-hours-weekly-rest-saudi-labor-law/'),
 ('S08', 'Overtime Art. 107: hourly wage + 50% of basic; hourly basic = basic/240; 720 h/yr cap', 'Noqta', '2026-09-07', 'https://noqta.tn/en/blog/overtime-pay-calculation-saudi-labor-law-2026'),
 ('S09', 'End-of-service award (Art. 84: half month per year for first 5 years); annual leave 21 days (Art. 109)', 'Teamed', '2025', 'https://www.teamed.global/gulf-employment-law/saudi-arabia'),
 ('S10', 'Housing allowance usually 25% of basic; included in GOSI base and end-of-service base', 'Qoyod Business Glossary', '2026-10-12', 'https://www.qoyod.com/en/glossary/housing-allowance/'),
 ('S11', 'Average health-insurance premium per insured person SAR 4,511 (end 2024; Argaam study)', 'Middle East Insurance Review', '2025-09-24', 'https://www.meinsurancereview.com/News/ViewNewsLetterArticle/id/92996/Type/MiddleEast/Saudi-Arabia-Health-insurers-rely-on-investment-gains-for-profits-despite-sharp-premium-hikes'),
 ('S12', 'Nitaqat: Saudi employee counts fully from SAR 4,000/month, half at SAR 3,000', 'Argaam', '2021-04-18', 'https://www.argaam.com/en/article/articledetail/id/1460333'),
 ('S13', 'PF&D allowances: fatigue 3-5%, personal ~5%, delay 2-4%', 'Supply Chain Digest (quoting Manhattan Associates, enVista)', '2013-07-17', 'https://www.scdigest.com/ontarget/13-07-17-1.php?cid=7230'),
 ('S14', 'de Koster, Le-Duc & Roodbergen (2007) Design and control of warehouse order picking - order picking up to 55% of warehouse operating expense; travel ~50% of picker time', 'European Journal of Operational Research 182(2):481-501', '2007', 'https://doi.org/10.1016/j.ejor.2006.07.009'),
 ('S15', 'Labour can account for up to 50% of a warehouse total operating cost (Infor)', 'MHL News', '2009-07-15', 'https://www.mhlnews.com/labor-management/article/22039898/labor-productivity-can-be-50-of-warehouse-operating-cost'),
 ('S16', 'DHL Supply Chain FY2025: revenue EUR 17,778m, EBIT EUR 1,161m, EBIT margin 6.5%', 'DHL Group annual earnings 2025', '2026-03-05', 'https://group.dhl.com/en/media-relations/press-releases/2026/dhl-group-annual-earnings-2025.html'),
 ('S17', '3PL pricing benchmarks (Fulfillment Advisor 2025 survey, 600+ warehouses): receiving USD 10.52/pallet, receiving labour USD 39.88/h, storage USD 20.17/pallet/month, B2B order USD 4.80', 'Fulfill.com', '2026-06-10', 'https://fulfill.com/3pl-pricing'),
 ('S18', '3PL pricing guide citing Warehousing & Fulfillment 2025 survey (receiving USD 5-15/pallet, storage USD 15-40/pallet/month)', 'Red Stag Fulfillment', '2025', 'https://redstagfulfillment.com/freight-rates-and-fuel-surcharges/'),
 ('S19', 'Riyadh storage SAR 80-250 per pallet/month; industrial SAR 15-45/sqm/month', 'Averx Global', '2026-06-18', 'https://www.averxglobal.com/blog/warehouse-storage-solutions-riyadh'),
 ('S20', 'UAE 3PL: storage AED 50-120/pallet/month, inbound receiving AED 3-8 per carton, pick & pack AED 8-20/order', 'UAE Free Zone Finder', '2026-08-11', 'https://uaefreezonefinder.com/uae-fulfillment-warehousing-3pl-guide-2026/'),
 ('S21', 'Saudi industrial & logistics rents: Riyadh SAR 208/sqm (+16%), Dammam SAR 231/sqm (+9%), Jeddah +8% (Knight Frank Autumn 2025)', 'Arabian Business', '2025', 'https://www.arabianbusiness.com/industries/real-estate/saudi-logistics-rents-surge-to-record-highs-as-tech-and-retail-giants-snap-up-scarce-space-report'),
 ('S22', 'JLL KSA Industrial Q2 2026: Jeddah rents +4.8%; top submarket SAR 300/sqm/yr', 'Food Business MEA', '2026-08-26', 'https://www.foodbusinessmea.com/?p=2162850'),
 ('S23', 'Engineered labour standards typically raise productivity 15-35% (vendor experience)', 'Easy Metrics', '2025-04-25', 'https://www.easymetrics.com/understanding-engineered-labor-standards'),
 ('S24', 'Buddy punching accounts for 2.2% of gross payroll (Nucleus Research); 19% of employees admit to it', 'Synerion', '2015-01-16', 'https://blog.synerion.com/buddy-punching-and-employee-time-theft-statistics'),
 ('S25', 'Eid al-Fitr 2026 private-sector holiday: 4 days from close of business 29 Ramadan (Wed 18 Mar)', 'Gulf News', '2026-03-04', 'https://gulfnews.com/world/gulf/saudi/saudi-arabia-announces-eid-al-fitr-holidays-for-private-and-non-profit-sectors-1.500462986'),
 ('S26', 'Eid al-Fitr 2026 = Friday 20 March (Shawwal crescent not sighted)', 'Gulf News', '2026-03-18', 'https://gulfnews.com/world/gulf/saudi/saudi-arabia-announces-first-day-of-eid-al-fitr-2026-1.500478696'),
 ('S27', 'Saudi Supreme Court: Arafat Day Tue 26 May, Eid al-Adha Wed 27 May 2026', 'Petra (Jordan News Agency)', '2026-05-17', 'https://petra.gov.jo/cgi-bin/index.php/en/news/saudi-supreme-court-announces-timing-for-arafat-day-and-eid-al-adha'),
 ('S28', 'Eid al-Adha 2026 private sector: paid days off from Arafat Day (26 May), minimum 4 days', 'ZenHR', '2026-03-26', 'https://blog.zenhr.com/en/eid-al-adha-2026-holiday-saudi-arabia'),
 ('S29', '2026 Saudi holidays: Founding Day Sun 22 Feb, National Day Wed 23 Sep', 'ZenHR', '2025-12-21', 'https://blog.zenhr.com/en/saudi-arabia-public-holidays-2026-guide-to-ksa-holidays-dates'),
 ('S30', 'Ramadan 1447 began Wednesday 18 February 2026 in Saudi Arabia', 'LatestLY', '2026-03', 'https://www.latestly.com/lifestyle/festivals-events/eid-2026-date-in-saudi-arabia-march-19-or-20-supreme-court-calls-for-moon-sighting-tomorrow-7357113.html/amp'),
 ('S31', 'NielsenIQ State of the Nation 3Q25: traditional trade 23% and e-commerce 5.6% of Saudi FMCG sales', 'Enterprise AM', '2025-12-09', 'https://enterpriseam.com/ksa/2025/12/09/shoppers-tighten-grocery-spending-splurge-on-tech/'),
 ('S32', 'Baqalahs (small family-run grocers) ~57% of Saudi grocery market; hyper/supermarkets 37% (2018)', 'Bord Bia', '2018-04-13', 'https://www.bordbia.ie/industry/news/food-alerts/retail-in-saudi-arabia/'),
 ('S33', 'MHRSD: private-sector Ramadan working hours 6 per day (Art. 98)', 'Ajel', '2022-03-26', 'https://english.ajel.sa/saudi-arabia/mhrsd-announces-ramadan-working-hours-for-private-sector'),
 ('S34', 'Job posting: 10 storekeepers, KSA - SAR 1,500-2,000/month + free accommodation, food, transport, medical, air ticket', 'Bureau of Emigration & Overseas Employment, Govt of Pakistan', 'offer expiry 28/06/2025', 'https://beoe.gov.pk/foreign-jobs/3402034/3819070/10-storekeepers-required-in-saudi-arabia-salary-1500-2000-rial-sar'),
 ('S35', 'Job posting: store keeper, KSA - SAR 2,200-2,500/month', 'Bureau of Emigration & Overseas Employment, Govt of Pakistan', '2025', 'https://beoe.gov.pk/foreign-jobs/3141199/3536608/1-store-keeper-required-in-saudi-arabia-salary-2200-2500-rial-sar'),
 ('S36', 'Warehouse manager, Saudi Arabia: gross SAR 3,240-11,953/month (10th-90th percentile), ~SAR 8,092 after 5 years (49 respondents)', 'Paylab Saudi Arabia', '2026', 'https://saudiarabia.paylab.com/en/salaryinfo/management/warehouse-manager'),
 ('S37', 'Storekeeper, Saudi Arabia: gross SAR 2,357-7,624/month (10th-90th percentile, all nationalities, 38 respondents)', 'Paylab Saudi Arabia', '2026', 'https://saudiarabia.paylab.com/en/salaryinfo/management/storekeeper'),
 ('S38', 'Almunajem Foods (listed Saudi food distributor): gross margin 16% (FY2025 and last twelve months)', 'multiples.vc', '2026-10-21', 'https://multiples.vc/public-comps/almunajem-foods-valuation-multiples'),
 ('S39', 'Binzagr data walkthrough (Badr Almesri): costing per case / per pallet at facility level; client-specific rates (same case activity billed SAR 0.8 to one client, SAR 0.5 to another); labour posted per task (wage x task x duration); HU_ID_2 unused; zero-second tasks are an operator practice', 'Binzagr - meeting notes and transcript', '2026-10-21', ''),
 ('S40', 'Binzagr WMS mappings: transaction mapping (cost / revenue, type of movement), WMS user list (payroll number, menu level, status), unit-of-sale conversion factors, pallet-id sample', 'Binzagr - Badr Almesri (files)', '2026-10-22', ''),
]

# (id, group, parameter, unit, low, base, high, basis, sources, note)
P = [
 # ---- expat warehouse operative, monthly SAR
 ('L01', 'labour_expat', 'basic_salary', 'SAR/month', 1400, 1800, 2300, 'Researched', 'S02;S03;S01', 'Postings: Riyadh warehouse worker 1,200-1,400; Jeddah labour 1,800-2,000. GASTAT non-Saudi male wages: no schooling 1,956, primary 2,088, lower secondary 2,305.'),
 ('L02', 'labour_expat', 'housing_pct_of_basic', 'share', 0.15, 0.25, 0.30, 'Researched', 'S10;S02', '25% of basic is the market norm (allowance); shared company accommodation is often cheaper -> low 15%.'),
 ('L03', 'labour_expat', 'transport', 'SAR/month', 100, 200, 300, 'Assumption', 'S02', 'Company transport is normally provided (posting S02); cost per head assumed.'),
 ('L04', 'labour_expat', 'food_allowance', 'SAR/month', 0, 200, 300, 'Researched', 'S02', 'SAR 200 food allowance in the Jeddah posting.'),
 ('L05', 'labour_expat', 'gosi_employer_rate_expat', 'share of basic+housing', 0.02, 0.02, 0.02, 'Researched', 'S04;S05', 'Non-Saudi: 2% occupational hazards, employer only.'),
 ('L06', 'labour_expat', 'expat_levy', 'SAR/month', 700, 800, 800, 'Researched', 'S06', '~SAR 800/worker/month; may be lower for establishments with a higher Saudi share.'),
 ('L07', 'labour_expat', 'iqama_fee', 'SAR/year', 650, 650, 650, 'Researched', 'S06', 'SAR 650 per 12 months.'),
 ('L08', 'labour_expat', 'medical_insurance', 'SAR/year', 800, 1500, 2500, 'Assumption', 'S11', 'Basic-class worker cover; national average premium per insured person is SAR 4,511 (all classes) - workers sit well below it.'),
 ('L09', 'labour_expat', 'eosb_months_per_year', 'months of wage', 0.5, 0.5, 0.5, 'Researched', 'S09;S10', 'Art. 84: half a month per year for the first 5 years; base = basic + housing.'),
 ('L10', 'labour_expat', 'air_ticket_biennial', 'SAR/2 years', 1500, 2000, 2500, 'Assumption', 'S02', 'Air ticket provided (S02); price assumed.'),
 ('L11', 'labour_expat', 'recruitment_visa_amortised', 'SAR per hire over 24 months', 4000, 6000, 10000, 'Assumption', '', 'Visa, recruitment agency and onboarding, amortised over a 2-year contract. No authoritative public figure.'),
 # ---- Saudi operative
 ('L20', 'labour_saudi', 'total_wage', 'SAR/month', 4000, 5000, 6000, 'Researched', 'S12;S01', 'Nitaqat counts a Saudi fully from SAR 4,000. GASTAT Saudi male lower-secondary average 7,352 (all occupations).'),
 ('L21', 'labour_saudi', 'basic_share_of_wage', 'share', 0.70, 0.75, 0.80, 'Assumption', 'S10', 'Basic ~75-80% of wage when housing is 25% of basic.'),
 ('L22', 'labour_saudi', 'gosi_employer_rate_saudi', 'share of basic+housing', 0.1175, 0.1225, 0.1275, 'Researched', 'S04;S05', 'Old scheme 11.75%; new scheme 12.25% (to Jun 2026), 12.75% (from Jul 2026).'),
 ('L23', 'labour_mix', 'saudi_share_of_operatives', 'share', 0.10, 0.20, 0.30, 'Assumption', '', 'Actual workforce mix is unknown (see 04_Gaps).'),
 # ---- role tiers (WMS menu level from Binzagr's user list: tally clerk / storekeeper / warehouse manager)
 ('L12', 'labour_roles', 'storekeeper_basic_salary', 'SAR/month', 1500, 1900, 2500, 'Researched', 'S34;S35;S37', 'Expat storekeeper postings SAR 1,500-2,500 basic + benefits (same band as operatives); Paylab gross 2,357-7,624 (all nationalities). Tally clerks use the operative basic salary (L01).'),
 ('L24', 'labour_roles', 'manager_monthly_wage', 'SAR/month', 5000, 8000, 12000, 'Researched', 'S36', 'Paylab warehouse manager gross SAR 3,240-11,953 (P10-P90), ~8,092 after 5 years.'),
 ('L25', 'labour_roles', 'manager_oncost_share', 'share of wage', 0.15, 0.20, 0.30, 'Assumption', 'S04;S06;S09', 'GOSI, medical, end of service and (if expat) levy / iqama on top of the cash wage.'),
 # ---- working time
 ('T01', 'time', 'working_days_per_month', 'days', 26, 26, 26, 'Researched', 'S07', '6-day week, Friday rest (Art. 98/104).'),
 ('T02', 'time', 'daily_hours', 'hours', 8, 8, 8, 'Researched', 'S07', 'Art. 98.'),
 ('T03', 'time', 'daily_hours_ramadan', 'hours', 6, 6, 6, 'Researched', 'S07;S33', 'Art. 98 / MHRSD.'),
 ('T04', 'time', 'break_hours', 'hours', 0.5, 0.5, 0.5, 'Researched', 'S07', 'Art. 101: 30-minute rest after 5 hours.'),
 ('T05', 'time', 'max_hours_at_workplace', 'hours', 12, 12, 12, 'Researched', 'S07', 'Art. 101.'),
 ('T06', 'time', 'overtime_premium_on_basic', 'share', 0.5, 0.5, 0.5, 'Researched', 'S08', 'Art. 107.'),
 ('T07', 'time', 'annual_leave_days', 'days/year', 21, 21, 21, 'Researched', 'S09', 'Art. 109 (30 after 5 years).'),
 ('T08', 'time', 'public_holiday_days', 'days/year', 10, 10, 12, 'Researched', 'S25;S28;S29', 'Eid al-Fitr 4 + Eid al-Adha 4 + Founding Day + National Day.'),
 ('T09', 'time', 'pfd_allowance', 'share', 0.10, 0.12, 0.14, 'Researched', 'S13', 'Personal ~5% + fatigue 3-5% + delay 2-4%.'),
 # ---- overhead / pricing
 ('C01', 'cost', 'labour_share_of_warehouse_opex', 'share', 0.40, 0.50, 0.60, 'Researched', 'S15;S14', 'Labour up to ~50% of opex; order picking alone up to 55%.'),
 ('C02', 'cost', 'ebit_margin_3pl', 'share', 0.04, 0.065, 0.10, 'Researched', 'S16', 'DHL Supply Chain FY2025 EBIT margin 6.5%.'),
 ('C03', 'cost', 'us_receiving_fee_per_pallet', 'USD', 5, 10.52, 15, 'Researched', 'S17;S18', 'Benchmark only (cross-check of the rate card).'),
 ('C04', 'cost', 'us_receiving_labour_rate', 'USD/hour', 30, 39.88, 60, 'Researched', 'S17', 'Benchmark only.'),
 ('C05', 'cost', 'ksa_storage_per_pallet_month', 'SAR', 80, 150, 250, 'Researched', 'S19', 'Storage revenue is NOT modelled (pallet positions unknown) - context only.'),
 # ---- reference values used for checks / levers only (not drawn in the labour Monte Carlo)
 ('C06', 'reference', 'client_case_rate_example', 'SAR/case', 0.5, 0.65, 0.8, 'Client', 'S39', 'Badr: the same case activity is billed SAR 0.8 to one client and SAR 0.5 to another. Used only to check the simulated case-pick rate.'),
 ('C07', 'reference', 'distributor_gross_margin', 'share', 0.12, 0.16, 0.20, 'Researched', 'S38', 'Turns extra Riyadh sales into profit in the opportunity levers (no Binzagr cost data yet).'),
 # ---- labour model
 ('M01', 'model', 'cycle_idle_cap_minutes', 'minutes', 15, 30, 45, 'Assumption', '', 'Gap between scans longer than this is a break/idle, not work content.'),
 ('M02', 'model', 'standard_time_percentile', 'percentile of badge-month mean cycle', 0.60, 0.50, 0.40, 'Assumption', '', 'Standard = time the median performer needs (low = more lenient, high = tighter).'),
 ('M03', 'model', 'target_performance_percentile', 'percentile of shift performance', 0.60, 0.75, 0.90, 'Assumption', 'S23', 'Target = what the best X% of Binzagr shifts already achieve. Base P75 (top quartile).'),
 ('M04', 'model', 'direct_shift_min_events', 'events', 30, 30, 30, 'Assumption', '', 'Shifts with fewer scans are treated as indirect/occasional (excluded from savings).'),
 ('M05', 'model', 'savings_capture_rate', 'share', 0.5, 0.7, 0.9, 'Assumption', 'S23', 'Share of the modelled gap that is realistically captured (rostering is lumpy).'),
 ('M06', 'model', 'badge_sharing_split_hours', 'hours', 12, 12, 12, 'Researched', 'S07', 'Sessions longer than the 12h legal presence limit are split into two worker-shifts.'),
 ('M07', 'model', 'bulk_posting_same_second_share', 'share of scans', 0.85, 0.90, 0.95, 'Assumption', '', 'Sessions with more than this share of scans in the same second as the previous scan are batch confirmations (95th percentile of sessions = 0.89); paid hours count, but they are not benchmarked.'),
]


def tables():
    src = pd.DataFrame(SOURCES, columns=['source_id', 'title', 'publisher', 'date', 'url'])
    par = pd.DataFrame(P, columns=['param_id', 'group', 'parameter', 'unit', 'low', 'base', 'high', 'basis', 'sources', 'note'])
    return src, par


def get(par, name, scen='base'):
    r = par[par.parameter == name]
    assert len(r) == 1, name
    return float(r.iloc[0][scen])


def pdict(par, scen='base'):
    assert par.parameter.is_unique
    return {r.parameter: float(r[scen]) for _, r in par.iterrows()}


if __name__ == '__main__':          # writes the source register and parameter table (sources, sim_parameters)
    from sim_common import write_table
    src, par = tables()
    assert src.source_id.is_unique and par.param_id.is_unique and par.parameter.is_unique
    write_table(src, 'sources')
    write_table(par, 'sim_parameters')
    print(f'sources: {len(src)}, parameters: {len(par)}')