/**
 * CEA Digital — Zero-UI Google Sheets Custom Functions
 * Supports BOTH:
 *   1. Direct Unified Wallet Keys (`cea_live_...` from https://ceadigital.io/tools — $2 Tripwire / $29/mo Growth)
 *   2. Apify API Tokens (`apify_api_...` calling Apify run-sync-get-dataset-items)
 *
 * Install: Extensions -> Apps Script -> Paste this file -> Save.
 */

var CEA_DIRECT_BASE_URL = "https://ceadigital.io";
var CEA_APIFY_BASE_URL = "https://api.apify.com/v2/acts";

/**
 * Pierce an anonymous LLC and skip-trace the human Managing Member's wireless phone & email.
 *
 * @param {string} companyOrOwnerName The LLC name or property owner name (e.g. "PALMETTO INDUSTRIAL HOLDINGS LLC").
 * @param {string} address The property or registered street address.
 * @param {string} apiKey Your Direct Unified Key (`cea_live_...` from ceadigital.io/tools) or Apify Token.
 * @param {string=} reportType Optional: "pierce_llc_and_skip_trace" (default), "skip_trace_only", or "full_kyb_guarantor_diligence".
 * @return {Array<Array<string>>} Horizontal row: [Pierced Owner, Wireless Phone 1, Email 1, Entity Status, OFAC Status]
 * @customfunction
 */
function SKIPTRACE(companyOrOwnerName, address, apiKey, reportType) {
  if (!companyOrOwnerName) {
    return [["Enter an LLC or Owner Name", "", "", "", ""]];
  }
  var key = (apiKey || "").toString().trim();
  if (!key) {
    return [["Missing API Key — Get a $2 (100-hit) or $29/mo (5,000-hit) key at https://ceadigital.io/tools", "", "", "", ""]];
  }
  var payload = {
    companyOrOwnerNames: [companyOrOwnerName.toString()],
    addresses: address ? [address.toString()] : [],
    reportType: reportType || "pierce_llc_and_skip_trace"
  };

  var item = _callCeaOrApify(
    key,
    "/v1/tools/skip-trace",
    "ceadigital~skip-trace-owner-phone-finder",
    payload
  );
  if (item._error) {
    return [[item._error, "", "", "", ""]];
  }
  var phone1 = (item.wireless_phones && item.wireless_phones.length > 0) ? item.wireless_phones[0].number : "NO_HIT ($0.00)";
  var email1 = (item.verified_emails && item.verified_emails.length > 0) ? item.verified_emails[0] : "";
  return [[
    item.pierced_human_owner || item.query || "",
    phone1,
    email1,
    item.sos_filing_status || "",
    item.ofac_sanctions_status || "CLEAR"
  ]];
}

/**
 * Extract 0-3s spoken hook, 0-3s burned-in visual OCR hook, retention score, and transcript from TikTok / Reels / Shorts.
 *
 * @param {string} videoUrl Direct TikTok, Instagram Reel, or YouTube Short URL.
 * @param {string} apiKey Your Direct Unified Key (`cea_live_...` from ceadigital.io/tools) or Apify Token.
 * @param {string=} translateTo Optional ISO language code (e.g. "es", "de", "pt", "fr").
 * @return {Array<Array<string>>} Horizontal row: [Spoken Hook 0-3s, Visual OCR Hook 0-3s, Retention Score, Full Transcript, Localized Hook]
 * @customfunction
 */
function VIDEOHOOK(videoUrl, apiKey, translateTo) {
  if (!videoUrl) {
    return [["Enter a TikTok, Reel, or Short URL", "", "", "", ""]];
  }
  var key = (apiKey || "").toString().trim();
  if (!key) {
    return [["Missing API Key — Get a $2 (100-hit) or $29/mo (5,000-hit) key at https://ceadigital.io/tools", "", "", "", ""]];
  }
  var payload = {
    videoUrls: [videoUrl.toString()],
    generateUgcBrief: true,
    translateTo: translateTo ? translateTo.toString() : ""
  };

  var item = _callCeaOrApify(
    key,
    "/v1/tools/video-hook",
    "ceadigital~instagram-tiktok-youtube-transcript-scraper",
    payload
  );
  if (item._error) {
    return [[item._error, "", "", "", ""]];
  }
  var localized = (item.translated_script && item.translated_script.localized_hook_0_3s) ? item.translated_script.localized_hook_0_3s : "";
  return [[
    item.spoken_hook_0_3s || "",
    item.visual_ocr_hook_0_3s || "",
    String(item.hook_retention_score || ""),
    item.transcript_text || "",
    localized
  ]];
}

/**
 * Fetch 30-day median realized sold price on eBay/Mercari, lowest active Buy-It-Now, and net flip profit after 13.25% fees.
 *
 * @param {string} keyword Product title, model number, or SKU (e.g. "Fujifilm X100V Silver").
 * @param {string=} condition Item condition: "used" (default), "new", "for_parts", or "any".
 * @param {string=} apiKey Your Direct Unified Key (`cea_live_...` from ceadigital.io/tools) or Apify Token.
 * @return {Array<Array<string>>} Horizontal row: [Median Sold USD, Lowest Active Buy-Now USD, Net Flip Profit After Fees USD, Sell-Through %, Underpriced Deal?]
 * @customfunction
 */
function SOLDCOMP(keyword, condition, apiKey) {
  if (!keyword) {
    return [["Enter a product keyword or SKU", "", "", "", ""]];
  }
  var key = (apiKey || "").toString().trim();
  if (!key) {
    return [["Missing API Key — Get a $2 (100-hit) or $29/mo (5,000-hit) key at https://ceadigital.io/tools", "", "", "", ""]];
  }
  var payload = {
    keywords: [keyword.toString()],
    condition: condition ? condition.toString() : "used",
    filterDealers: true
  };

  var item = _callCeaOrApify(
    key,
    "/v1/tools/sold-comps",
    "ceadigital~ebay-sold-listings-comps-scraper",
    payload
  );
  if (item._error) {
    return [[item._error, "", "", "", ""]];
  }
  return [[
    item.median_realized_sold_usd != null ? "$" + item.median_realized_sold_usd : "N/A",
    item.lowest_active_buy_now_usd != null ? "$" + item.lowest_active_buy_now_usd : "N/A",
    item.net_flip_profit_after_fees_usd != null ? "$" + item.net_flip_profit_after_fees_usd : "N/A",
    item.sell_through_rate_pct != null ? item.sell_through_rate_pct + "%" : "",
    item.is_underpriced_deal ? "DEAL ALERT (" + item.underpriced_pct_below_median + "% BELOW MEDIAN)" : "STANDARD"
  ]];
}

/**
 * Fetch active municipal building permits or code violations + pierced property owner wireless phone & contractor KYB.
 *
 * @param {string} cityOrZip City/State or 5-digit ZIP code (e.g. "Charleston, SC" or "29403").
 * @param {string=} permitType Permit or violation trigger: "commercial_ti" (default), "roofing", "solar", "hvac", "adu_remodel", or "code_violation".
 * @param {string=} apiKey Your Direct Unified Key (`cea_live_...` from ceadigital.io/tools) or Apify Token.
 * @return {Array<Array<string>>} Horizontal row: [Permit #, Property Address, Pierced Human Owner, Owner Wireless Phone, Job Value USD]
 * @customfunction
 */
function PERMITSKIP(cityOrZip, permitType, apiKey) {
  if (!cityOrZip) {
    return [["Enter a City, State or ZIP code", "", "", "", ""]];
  }
  var key = (apiKey || "").toString().trim();
  if (!key) {
    return [["Missing API Key — Get a $2 (100-hit) or $29/mo (5,000-hit) key at https://ceadigital.io/tools", "", "", "", ""]];
  }
  var payload = {
    citiesOrZips: [cityOrZip.toString()],
    permitTypes: [permitType ? permitType.toString().toLowerCase() : "commercial_ti"],
    minJobValueUsd: 15000,
    autoSkipTraceOwner: true,
    screenContractorKyb: true
  };

  var item = _callCeaOrApify(
    key,
    "/v1/tools/building-permits",
    "ceadigital~building-permits-code-violations-scraper",
    payload
  );
  if (item._error) {
    return [[item._error, "", "", "", ""]];
  }
  var phone1 = (item.owner_wireless_phones && item.owner_wireless_phones.length > 0)
    ? item.owner_wireless_phones[0].number
    : (item.owner_phone_primary || "NO_HIT ($0.00)");
  return [[
    item.permit_number || "",
    item.property_address || "",
    item.pierced_human_owner || item.parcel_owner_raw || "",
    phone1,
    item.estimated_job_value_usd != null ? "$" + item.estimated_job_value_usd : ""
  ]];
}

/**
 * Internal helper routing `cea_live_` keys to ceadigital.io and Apify tokens to api.apify.com.
 */
function _callCeaOrApify(apiKey, directPath, apifyActorId, payload) {
  try {
    var isDirect = apiKey.indexOf("cea_live_") === 0 || apiKey.indexOf("eyJ") === 0;
    var url = isDirect
      ? (CEA_DIRECT_BASE_URL + directPath)
      : (CEA_APIFY_BASE_URL + "/" + apifyActorId + "/run-sync-get-dataset-items?token=" + encodeURIComponent(apiKey));

    var headers = { "Content-Type": "application/json" };
    if (isDirect) {
      headers["Authorization"] = "Bearer " + apiKey;
    }

    var response = UrlFetchApp.fetch(url, {
      method: "post",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    var code = response.getResponseCode();
    var bodyText = response.getContentText();
    if (code === 402) {
      return { _error: "Quota Exhausted (HTTP 402) — Top up or upgrade to $29/mo (5,000 hits) at https://ceadigital.io/tools" };
    }
    if (code >= 400) {
      return { _error: "API Error (" + code + "): " + bodyText.substring(0, 120) };
    }

    var parsed = JSON.parse(bodyText);
    if (isDirect && parsed && parsed.data && parsed.data.items && parsed.data.items.length > 0) {
      return parsed.data.items[0];
    }
    if (Array.isArray(parsed)) {
      var nonSummary = parsed.filter(function(r) { return r && !r._summary; });
      if (nonSummary.length > 0) {
        return nonSummary[0];
      }
    }
    return { _error: "No matching record found ($0.00 charged)" };
  } catch (err) {
    return { _error: "Request failed: " + err.toString() };
  }
}