/**
 * OpenRights — Avails Validator
 *
 * Validates an avails sheet against:
 *   1. Required-field presence (Title, Territory, Right, Start, End)
 *   2. Date coherence (End ≥ Start)
 *   3. Territory codes against IFTA / ISO 3166-1 α-2 allowed set
 *   4. Exclusive field vocabulary (Yes/No only)
 *   5. Right vocabulary (IFTA rights taxonomy)
 *
 * Expected input sheet: "Paste Generic Avails Here"
 * Columns (row 1 = headers, data starts row 2):
 *   A: Title
 *   B: Territory / Group
 *   C: Right
 *   D: Start
 *   E: End
 *   F: Exclusive
 *   G: Notes
 *
 * Output sheet: "Validation Errors"
 *
 * Usage: Extensions → Apps Script → paste this file → Run validateAvails().
 *
 * openrights.blog
 */

const INPUT_SHEET = "Paste Generic Avails Here";
const OUTPUT_SHEET = "Validation Errors";

const VALID_TERRITORIES = new Set([
  // ISO 3166-1 α-2 common codes
  "US", "CA", "MX", "BR", "AR", "CL", "CO", "PE", "VE",
  "UK", "GB", "IE", "FR", "DE", "IT", "ES", "PT", "NL", "BE", "LU", "AT", "CH",
  "SE", "NO", "DK", "FI", "IS",
  "PL", "CZ", "SK", "HU", "RO", "BG", "GR", "TR", "RU", "UA",
  "CN", "JP", "KR", "TW", "HK", "SG", "MY", "TH", "VN", "ID", "PH", "IN", "PK", "BD",
  "AU", "NZ", "ZA", "NG", "EG", "MA", "KE",
  "SA", "AE", "QA", "KW", "IL",
  // IFTA region / group names
  "DACH", "Benelux", "Nordics", "Scandinavia", "CIS", "LATAM", "EMEA", "APAC",
  "ANZ", "MENA", "GCC", "Francophone", "Hispanophone",
  "Worldwide", "World",
]);

const VALID_RIGHTS = new Set([
  "SVOD Rights", "TVOD Rights", "AdVOD Rights (AVOD)", "EST / Download-to-Own",
  "Pay TV", "Premium Pay TV", "Basic Cable", "Free TV", "Free TV Catch-Up",
  "Theatrical Rights", "Non-Theatrical Rights", "Airline Rights", "Ship / Cruise Rights",
  "Hotel / Hospitality Rights", "Military Rights", "Educational Rights",
  "Physical Rights (DVD/Blu-ray)", "Clip Rights", "Format Rights", "Remake Rights",
]);

function validateAvails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const input = ss.getSheetByName(INPUT_SHEET);
  if (!input) {
    SpreadsheetApp.getUi().alert("No '" + INPUT_SHEET + "' sheet found. Create one with the OpenRights generic avails columns.");
    return;
  }

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

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

  rows.forEach((r, i) => {
    const rowNum = i + 2;
    const [title, territory, right, start, end, exclusive] = r.map(v => (v === null || v === undefined ? "" : v));

    if (!String(title).trim()) errors.push([rowNum, "Title", "MISSING", "Required field is blank"]);
    if (!String(territory).trim()) errors.push([rowNum, "Territory", "MISSING", "Required field is blank"]);
    if (!String(right).trim()) errors.push([rowNum, "Right", "MISSING", "Required field is blank"]);
    if (!start) errors.push([rowNum, "Start", "MISSING", "Required field is blank"]);
    if (!end) errors.push([rowNum, "End", "MISSING", "Required field is blank"]);

    if (start && end && new Date(end) < new Date(start)) {
      errors.push([rowNum, "End", "INVALID", "End date (" + end + ") is before Start (" + start + ")"]);
    }

    const terr = String(territory).trim();
    if (terr && !VALID_TERRITORIES.has(terr)) {
      errors.push([rowNum, "Territory", "UNKNOWN", "'" + terr + "' is not in the IFTA / ISO territory set"]);
    }

    const r2 = String(right).trim();
    if (r2 && !VALID_RIGHTS.has(r2)) {
      errors.push([rowNum, "Right", "UNKNOWN", "'" + r2 + "' is not in the IFTA rights taxonomy"]);
    }

    const ex = String(exclusive).trim();
    if (ex && ex !== "Yes" && ex !== "No") {
      errors.push([rowNum, "Exclusive", "INVALID", "Must be exactly 'Yes' or 'No', got '" + ex + "'"]);
    }
  });

  let out = ss.getSheetByName(OUTPUT_SHEET);
  if (!out) out = ss.insertSheet(OUTPUT_SHEET);
  out.clear();
  out.getRange(1, 1, 1, 4).setValues([["Row", "Field", "Severity", "Problem"]]);
  out.getRange(1, 1, 1, 4).setFontWeight("bold").setBackground("#0D7D74").setFontColor("#FFFFFF");

  if (errors.length > 0) {
    out.getRange(2, 1, errors.length, 4).setValues(errors);
    // Color rows by severity
    for (let i = 0; i < errors.length; i++) {
      const sev = errors[i][2];
      const color = sev === "MISSING" || sev === "INVALID" ? "#FEE2E2" : "#FEF3C7";
      out.getRange(i + 2, 1, 1, 4).setBackground(color);
    }
  } else {
    out.getRange(2, 1).setValue("✓ No validation errors. All rows pass.");
  }
  out.autoResizeColumns(1, 4);
  SpreadsheetApp.getUi().alert("Validation complete. " + errors.length + " issue(s) found. See '" + OUTPUT_SHEET + "' sheet.");
}