/**
 * Module 0 — Eligibility & Intake Engine
 * -----------------------------------------------
 * Dynamic, schema-driven questionnaire + eligibility scoring.
 *
 * Design goal: YOU add/change sector-specific questions and eligibility
 * rules as DATA (via the admin functions below), not by asking for a
 * code change every time. Global questions apply to every sector;
 * sector-specific questions layer on top.
 *
 * Founder independence: submitFounderResponse() returns an instant
 * eligible / excluded / needs_review decision — no manual review step
 * required for the common cases.
 */

// ---- Schema -----------------------------------------------------------
export const SCHEMA = `
CREATE TABLE IF NOT EXISTS sectors (
  id TEXT PRIMARY KEY,           -- e.g. 'd2c', 'healthcare', 'hyper_retail'
  name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS questions (
  id TEXT PRIMARY KEY,
  sector_id TEXT,                 -- NULL = applies to all sectors
  question_text TEXT NOT NULL,
  question_type TEXT NOT NULL,    -- 'text' | 'number' | 'select' | 'boolean'
  options TEXT,                   -- JSON array, for 'select' type
  required INTEGER NOT NULL DEFAULT 1,
  display_order INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (sector_id) REFERENCES sectors(id)
);

CREATE TABLE IF NOT EXISTS eligibility_rules (
  id TEXT PRIMARY KEY,
  sector_id TEXT,                 -- NULL = applies to all sectors
  question_id TEXT NOT NULL,
  operator TEXT NOT NULL,         -- 'equals' | 'not_equals' | 'gt' | 'lt' | 'gte' | 'lte' | 'in' | 'not_in'
  value TEXT NOT NULL,            -- JSON-encoded comparison value
  action TEXT NOT NULL,           -- 'exclude' | 'flag_review'
  reason TEXT NOT NULL,           -- human-readable reason, shown internally
  FOREIGN KEY (question_id) REFERENCES questions(id)
);

CREATE TABLE IF NOT EXISTS submissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  founder_id TEXT NOT NULL,
  sector_id TEXT NOT NULL,
  answers TEXT NOT NULL,          -- JSON blob of {question_id: answer}
  status TEXT NOT NULL,           -- 'eligible' | 'excluded' | 'needs_review'
  triggered_reasons TEXT,         -- JSON array of reasons, if any
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`;

// ---- Admin: add/update questions and rules as DATA --------------------
// Call these whenever you want to add a new sector question or exclusion
// rule. No code deploy needed.

export async function addQuestion(db, { id, sectorId, questionText, questionType, options, required = true, displayOrder = 0 }) {
  await db
    .prepare(
      `INSERT INTO questions (id, sector_id, question_text, question_type, options, required, display_order)
       VALUES (?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(id) DO UPDATE SET
         sector_id=excluded.sector_id, question_text=excluded.question_text,
         question_type=excluded.question_type, options=excluded.options,
         required=excluded.required, display_order=excluded.display_order`
    )
    .bind(id, sectorId ?? null, questionText, questionType, options ? JSON.stringify(options) : null, required ? 1 : 0, displayOrder)
    .run();
}

export async function addEligibilityRule(db, { id, sectorId, questionId, operator, value, action, reason }) {
  await db
    .prepare(
      `INSERT INTO eligibility_rules (id, sector_id, question_id, operator, value, action, reason)
       VALUES (?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(id) DO UPDATE SET
         sector_id=excluded.sector_id, question_id=excluded.question_id,
         operator=excluded.operator, value=excluded.value,
         action=excluded.action, reason=excluded.reason`
    )
    .bind(id, sectorId ?? null, questionId, operator, JSON.stringify(value), action, reason)
    .run();
}

// ---- Fetch the questionnaire a founder should see for their sector ----
export async function getQuestionnaire(db, { sectorId }) {
  const { results } = await db
    .prepare(
      `SELECT * FROM questions WHERE sector_id IS NULL OR sector_id = ?
       ORDER BY display_order ASC`
    )
    .bind(sectorId)
    .all();

  return results.map((q) => ({
    ...q,
    options: q.options ? JSON.parse(q.options) : null,
    required: !!q.required,
  }));
}

// ---- Rule evaluation (pure function, easy to unit test) ---------------
function evaluateOperator(operator, answer, ruleValue) {
  switch (operator) {
    case "equals":
      return answer === ruleValue;
    case "not_equals":
      return answer !== ruleValue;
    case "gt":
      return Number(answer) > Number(ruleValue);
    case "lt":
      return Number(answer) < Number(ruleValue);
    case "gte":
      return Number(answer) >= Number(ruleValue);
    case "lte":
      return Number(answer) <= Number(ruleValue);
    case "in":
      return Array.isArray(ruleValue) && ruleValue.includes(answer);
    case "not_in":
      return Array.isArray(ruleValue) && !ruleValue.includes(answer);
    default:
      return false;
  }
}

// ---- Submit a founder's answers, get an instant decision ---------------
export async function submitFounderResponse(db, { founderId, sectorId, answers }) {
  // Fetch applicable rules (global + sector-specific)
  const { results: rules } = await db
    .prepare(
      `SELECT * FROM eligibility_rules WHERE sector_id IS NULL OR sector_id = ?`
    )
    .bind(sectorId)
    .all();

  const triggered = [];

  for (const rule of rules) {
    const answer = answers[rule.question_id];
    if (answer === undefined) continue; // question not answered, skip rule
    const ruleValue = JSON.parse(rule.value);
    if (evaluateOperator(rule.operator, answer, ruleValue)) {
      triggered.push({ action: rule.action, reason: rule.reason });
    }
  }

  let status = "eligible";
  if (triggered.some((t) => t.action === "exclude")) {
    status = "excluded";
  } else if (triggered.some((t) => t.action === "flag_review")) {
    status = "needs_review";
  }

  await db
    .prepare(
      `INSERT INTO submissions (founder_id, sector_id, answers, status, triggered_reasons)
       VALUES (?, ?, ?, ?, ?)`
    )
    .bind(founderId, sectorId, JSON.stringify(answers), status, JSON.stringify(triggered))
    .run();

  return {
    status, // 'eligible' | 'excluded' | 'needs_review'
    reasons: triggered.map((t) => t.reason),
    // Founder-facing message — control the tone here, keep it factual not harsh
    message:
      status === "eligible"
        ? "You're eligible to proceed. Next step: sign the engagement agreement to view your sample report."
        : status === "needs_review"
        ? "Your submission needs a quick manual review before we can confirm eligibility."
        : "Based on your responses, this isn't a fit for our current process.",
  };
}

// ---- Example seed: how YOU add sector rules without touching code -----
// (Run once, or whenever you want to add/change criteria)
export async function seedExampleRules(db) {
  await addQuestion(db, {
    id: "q_business_type",
    sectorId: null, // global — every sector sees this
    questionText: "What best describes your primary business model?",
    questionType: "select",
    options: ["D2C brand", "Healthcare/hospital chain", "Hyper-retail chain", "Trading company", "Import-dependent reseller", "Other"],
    displayOrder: 1,
  });

  await addEligibilityRule(db, {
    id: "r_exclude_trading",
    sectorId: null,
    questionId: "q_business_type",
    operator: "equals",
    value: "Trading company",
    action: "exclude",
    reason: "Trading companies are not eligible under current criteria.",
  });

  await addEligibilityRule(db, {
    id: "r_exclude_import",
    sectorId: null,
    questionId: "q_business_type",
    operator: "equals",
    value: "Import-dependent reseller",
    action: "exclude",
    reason: "Import-dependent resellers are not eligible under current criteria.",
  });

  // Add more sector-specific questions/rules the same way, e.g.:
  // addQuestion(db, { id: 'q_hospital_beds', sectorId: 'healthcare', questionText: 'Number of operational beds?', questionType: 'number', displayOrder: 2 });
  // addEligibilityRule(db, { id: 'r_healthcare_min_beds', sectorId: 'healthcare', questionId: 'q_hospital_beds', operator: 'lt', value: 10, action: 'flag_review', reason: 'Below typical bed-count threshold, needs manual review.' });
}