/**
 * OpenRights — Royalty Statement Normalizer
 *
 * Reads a raw platform royalty statement and emits a canonical row format
 * suitable for cross-platform reconciliation.
 *
 * Canonical output columns:
 *   Canonical Title | ISO Territory | Period (YYYY-Qn) |
 *   Gross (USD) | Net (USD) | Source Currency | FX Rate | Platform | Source Row
 *
 * Expected input sheet: "Raw Statement Paste"
 * Columns:
 *   A: Platform Title
 *   B: Platform Territory (can be full name, α-3, α-2, locale)
 *   C: Period (any format: "Q1 2026", "2026-Q1", "Jan-Mar 2026", "Q1 FY26")
 *   D: Gross
 *   E: Net
 *   F: Currency (ISO 4217)
 *   G: Platform
 *
 * Output sheet: "Canonical Output"
 *
 * Configure FX rates in FX_RATES below. Default: quarter-end spot rates to USD.
 *
 * openrights.blog
 */

const INPUT_SHEET = "Raw Statement Paste";
const OUTPUT_SHEET = "Canonical Output";

// Quarter-end FX rates to USD. Update quarterly.
const FX_RATES = {
  USD: 1.00,
  GBP: 1.30,
  EUR: 1.08,
  CAD: 0.74,
  AUD: 0.66,
  JPY: 0.0067,
  KRW: 0.00075,
  BRL: 0.20,
  MXN: 0.059,
  INR: 0.012,
};

// Territory normalization: free text → ISO 3166-1 α-2
const TERRITORY_MAP = {
  "UNITED STATES": "US", "USA": "US", "US": "US",
  "UNITED KINGDOM": "GB", "GB": "GB", "UK": "GB",
  "GERMANY": "DE", "DEU": "DE", "DE": "DE", "DE_DE": "DE",
  "FRANCE": "FR", "FRA": "FR", "FR": "FR", "FR_FR": "FR",
  "CANADA": "CA", "CAN": "CA", "CA": "CA",
  "MEXICO": "MX", "MEX": "MX", "MX": "MX",
  "BRAZIL": "BR", "BRA": "BR", "BR": "BR",
  "JAPAN": "JP", "JPN": "JP", "JP": "JP",
  "KOREA": "KR", "KOREA, SOUTH": "KR", "KR": "KR",
  "AUSTRALIA": "AU", "AUS": "AU", "AU": "AU",
  "SPAIN": "ES", "ESP": "ES", "ES": "ES",
  "ITALY": "IT", "ITA": "IT", "IT": "IT",
  "NETHERLANDS": "NL", "NLD": "NL", "NL": "NL",
};

function normalizeStatement() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const input = ss.getSheetByName(INPUT_SHEET);
  if (!input) {
    SpreadsheetApp.getUi().alert("No '" + INPUT_SHEET + "' sheet found.");
    return;
  }

  const lastRow = input.getLastRow();
  if (lastRow < 2) return;

  const rows = input.getRange(2, 1, lastRow - 1, 7).getValues();
  const out = [];

  rows.forEach((r, i) => {
    const sourceRow = i + 2;
    const [pTitle, pTerr, pPeriod, gross, net, currency, platform] = r;
    if (!pTitle) return;

    const canonicalTitle = titleCase(String(pTitle).replace(/[_]/g, " ").replace(/\s+/g, " ").trim());
    const isoTerr = normalizeTerritory(pTerr);
    const period = normalizePeriod(pPeriod);
    const curr = String(currency || "USD").toUpperCase().trim();
    const fx = FX_RATES[curr] || 1.0;
    const grossUSD = Number(gross || 0) * fx;
    const netUSD = Number(net || 0) * fx;

    out.push([
      canonicalTitle, isoTerr, period,
      Math.round(grossUSD * 100) / 100,
      Math.round(netUSD * 100) / 100,
      curr, fx, platform || "", sourceRow,
    ]);
  });

  let outSheet = ss.getSheetByName(OUTPUT_SHEET);
  if (!outSheet) outSheet = ss.insertSheet(OUTPUT_SHEET);
  outSheet.clear();
  outSheet.getRange(1, 1, 1, 9).setValues([["Canonical Title", "ISO Territory", "Period (YYYY-Qn)", "Gross (USD)", "Net (USD)", "Source Currency", "FX Rate", "Platform", "Source Row"]]);
  outSheet.getRange(1, 1, 1, 9).setFontWeight("bold").setBackground("#0D7D74").setFontColor("#FFFFFF");

  if (out.length > 0) {
    outSheet.getRange(2, 1, out.length, 9).setValues(out);
    outSheet.getRange(2, 4, out.length, 2).setNumberFormat("$#,##0.00");
    outSheet.getRange(2, 7, out.length, 1).setNumberFormat("0.0000");
  }
  outSheet.autoResizeColumns(1, 9);
  SpreadsheetApp.getUi().alert("Normalized " + out.length + " rows into '" + OUTPUT_SHEET + "'.");
}

function normalizeTerritory(raw) {
  if (!raw) return "";
  const key = String(raw).toUpperCase().trim();
  if (TERRITORY_MAP[key]) return TERRITORY_MAP[key];
  // Locale fallback: de_DE → DE
  const locale = key.match(/^[A-Z]{2}_([A-Z]{2})$/);
  if (locale) return locale[1];
  // 2-letter passthrough
  if (key.length === 2) return key;
  return key; // unknown — keep for manual review
}

function normalizePeriod(raw) {
  if (!raw) return "";
  const s = String(raw).toUpperCase().replace(/\s+/g, " ").trim();

  // Already canonical: 2026-Q1
  const already = s.match(/^(\d{4})-Q([1-4])$/);
  if (already) return already[1] + "-Q" + already[2];

  // Q1 2026 or Q1 FY26
  const qFirst = s.match(/Q([1-4])\s+(?:FY)?(\d{2,4})/);
  if (qFirst) return expandYear(qFirst[2]) + "-Q" + qFirst[1];

  // 2026-Q1 with extra tokens
  const ymQ = s.match(/(\d{4})[-_\s]+Q([1-4])/);
  if (ymQ) return ymQ[1] + "-Q" + ymQ[2];

  // Jan-Mar 2026 / Apr-Jun 2026 / etc.
  const monthMap = {
    "JAN-MAR": 1, "APR-JUN": 2, "JUL-SEP": 3, "OCT-DEC": 4,
    "JANUARY-MARCH": 1, "APRIL-JUNE": 2, "JULY-SEPTEMBER": 3, "OCTOBER-DECEMBER": 4,
  };
  for (const k in monthMap) {
    if (s.includes(k)) {
      const yr = s.match(/(\d{4})/);
      if (yr) return yr[1] + "-Q" + monthMap[k];
    }
  }

  return raw; // unknown — keep for manual review
}

function expandYear(y) {
  const n = String(y);
  if (n.length === 4) return n;
  if (n.length === 2) return "20" + n;
  return n;
}

function titleCase(s) {
  return String(s).toLowerCase().replace(/\b[a-z]/g, c => c.toUpperCase());
}