"""Generates 35-scope3-factor-register.sql from b2g-scope3-factor-register-v1.xlsx (built from the official source files)."""
import openpyxl, re, sys
SRC=sys.argv[1]; OUT=sys.argv[2]
wb=openpyxl.load_workbook(SRC,data_only=True)
def q(v):
    if v is None or v=="": return "NULL"
    if isinstance(v,(int,float)): return repr(float(v))
    return "'"+str(v).replace("\\","\\\\").replace("'","''")+"'"
SRCKEY={"DESNZ 2026":"DESNZ26","EPA Hub 2025":"EPA25","USEEIO v1.3":"USEEIO13","CEA v22.0":"CEA22"}
USE={"Direct (India)":1,"Direct (country of stay)":1,"Approved proxy (UK)":2,"Approved proxy (US)":2,"Not for inventory":3}
def period(code,txt):
    if code.startswith("DESNZ26"): return ("2025-04-01","2027-03-31")
    if code.startswith("EPA25"): return ("2024-04-01","2027-03-31")
    if code.startswith("USEEIO13"): return ("2022-01-01","2025-12-31")
    m=re.search(r"FY (\d{4})-(\d{2})",txt or "")
    if code.startswith("CEA22-WAG") and m:
        a=int(m.group(1)); end="2027-03-31" if a==2025 else f"{a+1}-03-31"
        return (f"{a}-04-01",end)
    return (None,None)
L=[]
L.append("""-- b2g India edition: Scope 3 factor register (platform-approved emission factors), v1, 2 Nov 2026.
-- Generated by gen_35_scope3_factors.py from b2g-scope3-factor-register-v1.xlsx, which is built only from the official files in
-- b2g-local-pack/factor-sources (UK DESNZ 2026 v1.2, US EPA Emission Factors Hub 2025, US EPA USEEIO v1.3, CEA CO2 Baseline Database v22.0,
-- World Bank FX and US CPI). Every row keeps its exact source reference. No value is typed by hand.
-- s3factorsrc_tbl: source datasets. s3factor_tbl: factors (platform rows have s3f_membercompmst_fk 0; company-added factors use it).
-- s3fxcpi_tbl: INR to 2022 USD conversion inputs for spend-based factors.
-- Undo: DROP TABLE s3factor_tbl, s3factorsrc_tbl, s3fxcpi_tbl;
USE bgi_b2g_reg_dev;
CREATE TABLE IF NOT EXISTS s3factorsrc_tbl (
  s3factorsrc_pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s3fs_key VARCHAR(20) NOT NULL,
  s3fs_publisher VARCHAR(200) NOT NULL,
  s3fs_version VARCHAR(200) NOT NULL,
  s3fs_file VARCHAR(250) NULL,
  s3fs_url VARCHAR(300) NULL,
  s3fs_gwp VARCHAR(20) NOT NULL,
  s3fs_geography VARCHAR(30) NOT NULL,
  s3fs_retrievedon DATE NOT NULL,
  UNIQUE KEY ux_s3fs (s3fs_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Scope 3 factor source datasets';
CREATE TABLE IF NOT EXISTS s3factor_tbl (
  s3factor_pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s3f_code VARCHAR(60) NOT NULL COMMENT 'stable factor id',
  s3f_s3factorsrc_fk INT NULL,
  s3f_membercompmst_fk INT NOT NULL DEFAULT 0 COMMENT '0 = platform register; else company-added factor',
  s3f_reference VARCHAR(300) NOT NULL COMMENT 'sheet / row / id in source file',
  s3f_categories VARCHAR(40) NOT NULL COMMENT 'Scope 3 categories, comma separated',
  s3f_description VARCHAR(400) NOT NULL,
  s3f_unit VARCHAR(60) NOT NULL COMMENT 'activity unit (denominator)',
  s3f_value DECIMAL(24,10) NOT NULL,
  s3f_valueunit VARCHAR(60) NOT NULL,
  s3f_gwp VARCHAR(20) NOT NULL,
  s3f_geography VARCHAR(60) NOT NULL,
  s3f_indiause TINYINT NOT NULL COMMENT '1 direct, 2 approved proxy, 3 reference only',
  s3f_quality CHAR(1) NULL COMMENT 'H, M, L default',
  s3f_validfrom DATE NULL,
  s3f_validto DATE NULL,
  s3f_status TINYINT NOT NULL DEFAULT 1 COMMENT '0 draft, 1 approved, 2 retired',
  s3f_approvedby INT NULL,
  s3f_approvedon DATETIME NULL,
  s3f_note VARCHAR(400) NULL,
  s3f_createdon DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ux_s3f (s3f_code, s3f_membercompmst_fk),
  KEY ix_s3f_cat (s3f_categories),
  KEY ix_s3f_comp (s3f_membercompmst_fk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Scope 3 emission factor register';
CREATE TABLE IF NOT EXISTS s3fxcpi_tbl (
  s3fx_year SMALLINT NOT NULL PRIMARY KEY,
  s3fx_inrperusd DECIMAL(14,6) NOT NULL,
  s3fx_uscpi DECIMAL(14,6) NULL COMMENT 'published value, NULL if not yet published',
  s3fx_uscpiused DECIMAL(14,6) NOT NULL,
  s3fx_note VARCHAR(250) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='INR per USD and US CPI for USEEIO spend conversion';
""")
so=wb["Sources"]
for r in so.iter_rows(min_row=2,values_only=True):
    if not r[0] or r[0]=="WB": continue
    L.append(f"INSERT INTO s3factorsrc_tbl (s3fs_key,s3fs_publisher,s3fs_version,s3fs_file,s3fs_url,s3fs_gwp,s3fs_geography,s3fs_retrievedon) VALUES ({q(r[0])},{q(r[1])},{q(r[2])},{q(r[3])},{q(r[4])},{q(r[5])},{q(r[6])},'2026-11-02') ON DUPLICATE KEY UPDATE s3fs_version=VALUES(s3fs_version);")
sc=wb["Spend conversion"]
for r in sc.iter_rows(min_row=5,max_row=8,values_only=True):
    cpi=r[2] if isinstance(r[2],(int,float)) else None
    note="World Bank PA.NUS.FCRF / FP.CPI.TOTL, updated 8 Oct 2026"+("; 2025 US CPI not published, 2024 CPI used as approved proxy" if cpi is None else "")
    L.append(f"INSERT INTO s3fxcpi_tbl VALUES ({int(r[0])},{q(r[1])},{q(cpi)},{q(r[3])},{q(note)}) ON DUPLICATE KEY UPDATE s3fx_inrperusd=VALUES(s3fx_inrperusd), s3fx_uscpi=VALUES(s3fx_uscpi), s3fx_uscpiused=VALUES(s3fx_uscpiused), s3fx_note=VALUES(s3fx_note);")
rg=wb["Register"]; n=0; batch=[]
QD={"Medium":"M","Low":"L","High":"H"}
for r in rg.iter_rows(min_row=2,values_only=True):
    if not r[0]: continue
    if not isinstance(r[7],(int,float)): raise SystemExit("non-numeric value in "+r[0])
    a,b=period(r[0],r[13])
    batch.append(f"({q(r[0])},(SELECT s3factorsrc_pk FROM s3factorsrc_tbl WHERE s3fs_key={q(SRCKEY[r[1]])}),{q(r[3])},{q(r[4])},{q(str(r[5])[:400])},{q(r[6])},{repr(float(r[7]))},{q(r[8])},{q(r[9])},{q(r[10])},{USE[r[11]]},{q(QD.get(r[12]))},{q(a)},{q(b)},1,NOW(),{q((r[14] or '')[:400])})")
    n+=1
    if len(batch)==200:
        L.append("INSERT INTO s3factor_tbl (s3f_code,s3f_s3factorsrc_fk,s3f_reference,s3f_categories,s3f_description,s3f_unit,s3f_value,s3f_valueunit,s3f_gwp,s3f_geography,s3f_indiause,s3f_quality,s3f_validfrom,s3f_validto,s3f_status,s3f_approvedon,s3f_note) VALUES\n"+",\n".join(batch)+"\nON DUPLICATE KEY UPDATE s3f_value=VALUES(s3f_value), s3f_reference=VALUES(s3f_reference);"); batch=[]
if batch: L.append("INSERT INTO s3factor_tbl (s3f_code,s3f_s3factorsrc_fk,s3f_reference,s3f_categories,s3f_description,s3f_unit,s3f_value,s3f_valueunit,s3f_gwp,s3f_geography,s3f_indiause,s3f_quality,s3f_validfrom,s3f_validto,s3f_status,s3f_approvedon,s3f_note) VALUES\n"+",\n".join(batch)+"\nON DUPLICATE KEY UPDATE s3f_value=VALUES(s3f_value), s3f_reference=VALUES(s3f_reference);")
open(OUT,"w",encoding="utf-8").write("\n".join(L)+"\n")
print("factors",n)