import { prisma } from "./prisma.js";
import { createId } from "./id.js";

const systemDataFields: Array<[string, string, string, string]> = [
  ["client_first_name", "Client First Name", "client", "string"],
  ["client_middle_name", "Client Middle Name", "client", "string"],
  ["client_last_name", "Client Last Name", "client", "string"],
  ["client_full_name", "Client Full Name", "client", "string"],
  ["client_email", "Client Email", "client", "string"],
  ["client_phone", "Client Phone", "client", "string"],
  ["client_date_of_birth", "Client Date Of Birth", "client", "date"],
  ["client_country_of_citizenship", "Client Country Of Citizenship", "client", "string"],
  ["client_immigration_status", "Client Immigration Status", "client", "string"],
  ["client_marital_status", "Client Marital Status", "client", "string"],
  ["client_has_dependents", "Client Has Dependents", "client", "boolean"],
  ["client_dependents_count", "Client Dependents Count", "client", "number"],
  ["client_has_been_arrested", "Client Has Been Arrested", "client", "boolean"],
  ["client_ever_public_cash_assistance", "Client Ever Public Cash Assistance", "client", "boolean"],
  ["client_birth_city", "Client Birth City", "client", "string"],
  ["client_birth_country", "Client Birth Country", "client", "string"],
  ["client_gender", "Client Gender", "client", "string"],
  ["client_alien_number", "Client Alien Number", "client", "string"],
  ["client_ssn", "Client Social Security Number", "client", "string"],
  ["client_visa_number", "Client Visa Number", "client", "string"],
  ["client_i94_number", "Client I-94 Number", "client", "string"],
  ["client_class_of_admission", "Client Class Of Admission", "client", "string"],
  ["client_last_arrival_date", "Client Last Arrival Date", "client", "date"],
  ["client_authorized_stay_until", "Client Authorized Stay Until", "client", "date"],
  ["client_status_expires_on", "Client Status Expires On", "client", "date"],
  ["client_current_address_line1", "Client Current Address Line 1", "client", "string"],
  ["client_current_address_line2", "Client Current Address Line 2", "client", "string"],
  ["client_current_city", "Client Current City", "client", "string"],
  ["client_current_state", "Client Current State", "client", "string"],
  ["client_current_zip_code", "Client Current Zip Code", "client", "string"],
  ["client_current_country", "Client Current Country", "client", "string"],
  ["client_foreign_address_line1", "Client Most Recent Foreign Address Line 1", "client", "string"],
  ["client_foreign_city", "Client Most Recent Foreign City", "client", "string"],
  ["client_foreign_province", "Client Most Recent Foreign Province", "client", "string"],
  ["client_foreign_postal_code", "Client Most Recent Foreign Postal Code", "client", "string"],
  ["client_foreign_country", "Client Most Recent Foreign Country", "client", "string"],
  ["client_current_immigration_status", "Client Current Immigration Status", "client", "string"],
  ["passport_number", "Passport Number", "case", "string"],
  ["client_passport_issuing_country", "Passport Issuing Country", "case", "string"],
  ["client_passport_expiration_date", "Passport Expiration Date", "case", "date"],
  ["employer_name", "Employer Name", "case", "string"],
  ["job_title", "Job Title", "case", "string"],
  ["annual_salary", "Annual Salary", "case", "string"],
  ["i140_receipt_number", "I-140 Receipt Number", "case", "string"],
  ["i140_priority_date", "I-140 Priority Date", "case", "date"],
  ["i140_category", "I-140 Category", "case", "string"],
  ["perm_case_number", "PERM Case Number", "case", "string"],
  ["sponsor_fein", "Sponsor FEIN", "case", "string"],
  ["sponsor_contact_name", "Sponsor Contact Name", "case", "string"],
  ["sponsor_phone", "Sponsor Phone", "case", "string"],
  ["sponsor_email", "Sponsor Email", "case", "string"],
  ["parent_1_full_name", "Parent 1 Full Name", "case", "string"],
  ["parent_1_date_of_birth", "Parent 1 Date Of Birth", "case", "date"],
  ["parent_1_country_of_birth", "Parent 1 Country Of Birth", "case", "string"],
  ["parent_2_full_name", "Parent 2 Full Name", "case", "string"],
  ["parent_2_date_of_birth", "Parent 2 Date Of Birth", "case", "date"],
  ["parent_2_country_of_birth", "Parent 2 Country Of Birth", "case", "string"],
  ["spouse_full_name", "Current Spouse Full Name", "case", "string"],
  ["spouse_date_of_birth", "Current Spouse Date Of Birth", "case", "date"],
  ["spouse_country_of_birth", "Current Spouse Country Of Birth", "case", "string"],
  ["marriage_date", "Marriage Date", "case", "date"],
  ["marriage_country", "Marriage Country", "case", "string"],
  ["number_of_children", "Number Of Children", "case", "number"],
  ["child_1_full_name", "Child 1 Full Name", "case", "string"],
  ["child_1_date_of_birth", "Child 1 Date Of Birth", "case", "date"],
  ["child_1_country_of_birth", "Child 1 Country Of Birth", "case", "string"],
  ["height_feet", "Height Feet", "case", "number"],
  ["height_inches", "Height Inches", "case", "number"],
  ["weight_pounds", "Weight Pounds", "case", "number"],
  ["eye_color", "Eye Color", "case", "string"],
  ["hair_color", "Hair Color", "case", "string"],
  ["household_size", "Household Size", "case", "number"],
  ["household_assets_value", "Household Assets Value", "case", "string"],
  ["current_school_name", "Current School Name", "case", "string"],
  ["current_school_program", "Current School Program", "case", "string"],
  ["current_school_start_date", "Current School Start Date", "case", "date"],
  ["current_school_end_date", "Current School End Date", "case", "date"],
];

const systemForm = {
  code: "IMM_INTAKE_SUMMARY",
  name: "Immigration Intake Summary",
  versionLabel: "2026.1",
  description: "Core intake form used to drive dynamic question generation.",
  fields: [
    ["client_first_name", "Applicant first name", "applicant_first_name", "string", 1],
    ["client_last_name", "Applicant last name", "applicant_last_name", "string", 1],
    ["client_email", "Applicant email", "applicant_email", "string", 1],
    ["client_phone", "Applicant phone", "applicant_phone", "string", 1],
    ["client_date_of_birth", "Applicant date of birth", "applicant_date_of_birth", "date", 1],
    ["client_marital_status", "Marital status", "client_marital_status", "string", 0],
    ["client_has_dependents", "Has dependents", "client_has_dependents", "boolean", 0],
    ["client_dependents_count", "Dependents count", "client_dependents_count", "number", 0],
    ["client_has_been_arrested", "Has been arrested", "client_has_been_arrested", "boolean", 0],
    ["passport_number", "Passport number", "passport_number", "string", 1],
    ["client_country_of_citizenship", "Country of citizenship", "country_of_citizenship", "string", 1],
    ["employer_name", "Employer name", "employer_name", "string", 1],
    ["job_title", "Job title", "job_title", "string", 1],
  ] as Array<[string, string, string, string, number]>,
};

const systemPacket = {
  code: "DEFAULT_CASE_PACKET",
  name: "Default Case Packet",
  versionNumber: 1,
};

export async function ensureSystemSeed() {
  const existingFields = await prisma.$queryRaw<Array<{ field_key: string; id: string }>>`
    SELECT field_key, id FROM data_fields
  `;
  const existingFieldKeys = new Set(existingFields.map((item) => item.field_key));

  for (const [fieldKey, label, scope, dataType] of systemDataFields) {
    if (existingFieldKeys.has(fieldKey)) continue;

    await prisma.$executeRaw`
      INSERT INTO data_fields (
        id, field_key, label, entity_scope, data_type, is_repeatable, pii_level,
        created_at, updated_at
      ) VALUES (
        ${createId()},
        ${fieldKey},
        ${label},
        ${scope},
        ${dataType},
        0,
        'high',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;
  }

  const [existingForm] = await prisma.$queryRaw<Array<{ id: string }>>`
    SELECT id
    FROM form_templates
    WHERE law_firm_id IS NULL AND code = ${systemForm.code} AND version_label = ${systemForm.versionLabel}
    LIMIT 1
  `;

  let formTemplateId = existingForm?.id;

  if (!formTemplateId) {
    formTemplateId = createId();

    await prisma.$executeRaw`
      INSERT INTO form_templates (
        id, law_firm_id, code, name, version_label, description, is_active, is_system_template,
        created_at, updated_at
      ) VALUES (
        ${formTemplateId},
        NULL,
        ${systemForm.code},
        ${systemForm.name},
        ${systemForm.versionLabel},
        ${systemForm.description},
        1,
        1,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;
  }

  const dataFields = await prisma.$queryRaw<Array<{ id: string; field_key: string }>>`
    SELECT id, field_key FROM data_fields
  `;
  const dataFieldByKey = new Map(dataFields.map((item) => [item.field_key, item.id]));

  const existingFormFields = await prisma.$queryRaw<Array<{ field_key: string; id: string }>>`
    SELECT field_key, id
    FROM form_fields
    WHERE form_template_id = ${formTemplateId}
  `;
  const existingFormFieldKeys = new Set(existingFormFields.map((item) => item.field_key));

  for (const [fieldKey, label, pdfFieldName, dataType, isRequired] of systemForm.fields) {
    let formFieldId = existingFormFields.find((item) => item.field_key === fieldKey)?.id;

    if (!existingFormFieldKeys.has(fieldKey)) {
      formFieldId = createId();
      await prisma.$executeRaw`
        INSERT INTO form_fields (
          id, form_template_id, section_name, page_number, field_key, label, pdf_field_name,
          data_type, is_required, is_repeatable, created_at, updated_at
        ) VALUES (
          ${formFieldId},
          ${formTemplateId},
          'Core',
          1,
          ${fieldKey},
          ${label},
          ${pdfFieldName},
          ${dataType},
          ${isRequired},
          0,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;
    }

    const dataFieldId = dataFieldByKey.get(fieldKey);
    if (!dataFieldId || !formFieldId) continue;

    const [mappingExists] = await prisma.$queryRaw<Array<{ id: string }>>`
      SELECT id
      FROM form_mappings
      WHERE form_template_id = ${formTemplateId}
        AND form_field_id = ${formFieldId}
        AND data_field_id = ${dataFieldId}
      LIMIT 1
    `;

    if (!mappingExists) {
      await prisma.$executeRaw`
        INSERT INTO form_mappings (
          id, form_template_id, form_field_id, data_field_id, mapping_strategy,
          confidence_threshold, is_active, created_at, updated_at
        ) VALUES (
          ${createId()},
          ${formTemplateId},
          ${formFieldId},
          ${dataFieldId},
          'direct',
          0.8000,
          1,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;
    }

    const [ruleExists] = await prisma.$queryRaw<Array<{ id: string }>>`
      SELECT id
      FROM missing_information_rules
      WHERE law_firm_id IS NULL
        AND form_template_id = ${formTemplateId}
        AND form_field_id = ${formFieldId}
        AND data_field_id = ${dataFieldId}
      LIMIT 1
    `;

    if (!ruleExists) {
      await prisma.$executeRaw`
        INSERT INTO missing_information_rules (
          id, law_firm_id, workflow_template_id, form_template_id, form_field_id, data_field_id,
          rule_name, trigger_scope, minimum_confidence, conflict_policy, is_active, created_at, updated_at
        ) VALUES (
          ${createId()},
          NULL,
          NULL,
          ${formTemplateId},
          ${formFieldId},
          ${dataFieldId},
          ${`Rule for ${fieldKey}`},
          'case_form',
          0.8000,
          'ask_for_confirmation',
          1,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;
    }
  }

  const [packetExists] = await prisma.$queryRaw<Array<{ id: string }>>`
    SELECT id
    FROM packet_templates
    WHERE law_firm_id IS NULL AND code = ${systemPacket.code} AND version_number = ${systemPacket.versionNumber}
    LIMIT 1
  `;

  let packetTemplateId = packetExists?.id;
  if (!packetTemplateId) {
    packetTemplateId = createId();
    await prisma.$executeRaw`
      INSERT INTO packet_templates (
        id, law_firm_id, code, name, version_number, description, is_active, created_at, updated_at
      ) VALUES (
        ${packetTemplateId},
        NULL,
        ${systemPacket.code},
        ${systemPacket.name},
        ${systemPacket.versionNumber},
        'Default generated packet for case assembly',
        1,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;
  }

  const [packetItemExists] = await prisma.$queryRaw<Array<{ id: string }>>`
    SELECT id
    FROM packet_template_items
    WHERE packet_template_id = ${packetTemplateId} AND sort_order = 1
    LIMIT 1
  `;

  if (!packetItemExists) {
    await prisma.$executeRaw`
      INSERT INTO packet_template_items (
        id, packet_template_id, sort_order, item_type, display_name, form_template_id,
        source_strategy, is_required, created_at
      ) VALUES (
        ${createId()},
        ${packetTemplateId},
        1,
        'generated_form',
        'Primary generated intake form',
        ${formTemplateId},
        'dynamic',
        1,
        CURRENT_TIMESTAMP
      )
    `;
  }
}