import { config } from "dotenv";
import path from "node:path";
config({ path: path.resolve(process.cwd(), ".env.local") });

// Seeds three representative research studies so /products/research/studies
// and /admin/research have real data to render against. Safe to re-run —
// each study is upserted on slug.

async function main() {
  const databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) throw new Error("DATABASE_URL not set");

  const postgres = (await import("postgres")).default;
  const { drizzle } = await import("drizzle-orm/postgres-js");
  const schema = await import("./schema");
  const { eq } = await import("drizzle-orm");

  const sql = postgres(databaseUrl, { max: 1 });
  const db = drizzle(sql, { schema });

  type SiteSeed = { city: string; country: string; piName: string; enrolment: number };
  type InvestigatorSeed = { name: string; institution: string; role: string };
  type DocumentSeed = { name: string; type: string; url: string };

  type StudySeed = {
    slug: string;
    title: string;
    phase: "Planned" | "Recruiting" | "Active" | "Completed";
    nctId: string | null;
    sponsor: string;
    principalInvestigator: string;
    conditionKeyword: string;
    targetEnrolment: number;
    currentEnrolment: number;
    countries: string[];
    background: string;
    design: string;
    intervention: string;
    control: string;
    inclusion: string[];
    exclusion: string[];
    primaryOutcomes: string[];
    secondaryOutcomes: string[];
    status: "draft" | "published" | "archived";
    sites: SiteSeed[];
    investigators: InvestigatorSeed[];
    documents: DocumentSeed[];
  };

  const studies: StudySeed[] = [
    {
      slug: "og-rcr-outcomes-registry",
      title: "OrthoGlobe Rotator Cuff Repair Outcomes Registry",
      phase: "Recruiting",
      nctId: "NCT0%PLACEHOLDER%01",
      sponsor: "OrthoGlobe Charity Collaboration",
      principalInvestigator: "Prof Mohamed A. Imam",
      conditionKeyword: "Rotator cuff tear",
      targetEnrolment: 1200,
      currentEnrolment: 318,
      countries: ["United Kingdom", "Ireland", "Egypt", "Saudi Arabia", "India"],
      background:
        "Arthroscopic rotator cuff repair is one of the most commonly performed shoulder procedures, yet outcomes vary widely between centres and surgeon experience levels. The OG-RCR registry prospectively captures patient-reported outcomes, structural healing, and complications across a multicentre network to benchmark real-world results and identify modifiable predictors of failure.",
      design:
        "Prospective multicentre observational registry. Patients undergoing primary arthroscopic rotator cuff repair are enrolled pre-operatively and followed at 6 weeks, 6 months, and 12 months post-op.",
      intervention:
        "Standard-of-care arthroscopic rotator cuff repair as performed at each participating site — no protocol-mandated technique.",
      control: "Not applicable (observational single-arm registry).",
      inclusion: [
        "Adult patients (18 years and over)",
        "Symptomatic full-thickness rotator cuff tear confirmed on MRI",
        "Scheduled for primary arthroscopic repair",
        "Able to complete English, Arabic, or Hindi PROMs",
      ],
      exclusion: [
        "Revision cuff surgery",
        "Concomitant shoulder arthroplasty",
        "Active infection",
        "Inflammatory arthropathy involving the shoulder",
      ],
      primaryOutcomes: [
        "Oxford Shoulder Score (OSS) change from baseline to 12 months",
        "Structural healing on ultrasound at 12 months (Sugaya classification)",
      ],
      secondaryOutcomes: [
        "Constant-Murley score at 6 and 12 months",
        "Return-to-work interval",
        "Re-tear requiring reoperation within 12 months",
        "Patient satisfaction (Likert)",
      ],
      status: "published",
      sites: [
        { city: "London", country: "United Kingdom", piName: "Prof Mohamed A. Imam", enrolment: 112 },
        { city: "Dublin", country: "Ireland", piName: "Mr Hazem S. Ghaith", enrolment: 64 },
        { city: "Cairo", country: "Egypt", piName: "Dr Amit Vats", enrolment: 82 },
        { city: "Riyadh", country: "Saudi Arabia", piName: "Dr Ziad Harb", enrolment: 37 },
        { city: "New Delhi", country: "India", piName: "Dr Anshul Sobti", enrolment: 23 },
      ],
      investigators: [
        { name: "Prof Mohamed A. Imam", institution: "OrthoGlobe / Kings College Hospital", role: "Chief investigator" },
        { name: "Dr Atef A. Hassan", institution: "OrthoGlobe", role: "Coordinating investigator" },
        { name: "Dr Anshul Sobti", institution: "AIIMS Delhi", role: "Country lead — India" },
        { name: "Mr Hazem S. Ghaith", institution: "Mater Misericordiae University Hospital", role: "Country lead — Ireland" },
      ],
      documents: [
        { name: "Study protocol v2.1", type: "Protocol", url: "/uploads/research/og-rcr/protocol-v2.1.pdf" },
        { name: "Patient information sheet (EN)", type: "PIS", url: "/uploads/research/og-rcr/pis-en.pdf" },
        { name: "Case report form (CRF)", type: "CRF", url: "/uploads/research/og-rcr/crf.pdf" },
      ],
    },
    {
      slug: "og-hip-fracture-frailty-cohort",
      title: "International Hip Fracture Frailty Outcomes (IHFFO) Cohort",
      phase: "Active",
      nctId: null,
      sponsor: "OrthoGlobe Charity Collaboration",
      principalInvestigator: "Dr Atef A. Hassan",
      conditionKeyword: "Neck of femur fracture",
      targetEnrolment: 2000,
      currentEnrolment: 1174,
      countries: ["United Kingdom", "Germany", "Netherlands", "Australia"],
      background:
        "Hip fractures in frail older adults carry a 30-day mortality of 6–9% and a 1-year mortality exceeding 25%. IHFFO stratifies outcomes by the Clinical Frailty Scale (CFS) across four health systems to identify where frailty-adjusted care pathways move the needle.",
      design:
        "Prospective observational cohort with CFS assessment on admission, standard perioperative data capture, and 30/90/365-day mortality and functional follow-up.",
      intervention: "Usual care.",
      control: "Not applicable.",
      inclusion: [
        "Age 65 years or older",
        "Neck of femur, intertrochanteric, or subtrochanteric fracture",
        "Admitted within 48 hours of injury",
      ],
      exclusion: [
        "Pathological fracture (metastatic)",
        "Polytrauma with ISS above 15",
        "Prior ipsilateral hip arthroplasty",
      ],
      primaryOutcomes: [
        "30-day all-cause mortality stratified by Clinical Frailty Scale",
        "Time-to-theatre (hours from admission to skin incision)",
      ],
      secondaryOutcomes: [
        "90-day mortality",
        "Return to pre-fracture mobility at 1 year",
        "Post-operative delirium (4AT)",
        "30-day readmission rate",
      ],
      status: "published",
      sites: [
        { city: "Manchester", country: "United Kingdom", piName: "Dr Atef A. Hassan", enrolment: 412 },
        { city: "Munich", country: "Germany", piName: "Dr Ash Evans", enrolment: 287 },
        { city: "Utrecht", country: "Netherlands", piName: "Mr Tony Antonios", enrolment: 263 },
        { city: "Melbourne", country: "Australia", piName: "Mr Duncan Muir", enrolment: 212 },
      ],
      investigators: [
        { name: "Dr Atef A. Hassan", institution: "OrthoGlobe / Manchester Royal Infirmary", role: "Chief investigator" },
        { name: "Dr Ash Evans", institution: "LMU Munich", role: "Country lead — Germany" },
        { name: "Mr Tony Antonios", institution: "UMC Utrecht", role: "Country lead — Netherlands" },
      ],
      documents: [
        { name: "IHFFO protocol v1.3", type: "Protocol", url: "/uploads/research/ihffo/protocol-v1.3.pdf" },
        { name: "Data dictionary", type: "Data dictionary", url: "/uploads/research/ihffo/data-dictionary.csv" },
      ],
    },
    {
      slug: "og-acl-primary-vs-repair-rct",
      title: "Primary ACL Reconstruction vs. Arthroscopic Repair (OG-ACLR) — Pilot RCT",
      phase: "Planned",
      nctId: null,
      sponsor: "OrthoGlobe Charity Collaboration",
      principalInvestigator: "Dr Paolo Consigliere",
      conditionKeyword: "ACL tear",
      targetEnrolment: 120,
      currentEnrolment: 0,
      countries: ["United Kingdom", "Italy"],
      background:
        "Contemporary imaging and instrumentation have renewed interest in primary arthroscopic repair of proximal ACL ruptures in carefully selected patients. OG-ACLR is a pilot randomised trial comparing repair to standard reconstruction in patients with proximal avulsion-pattern tears seen within 3 weeks of injury.",
      design:
        "Two-arm parallel-group pilot RCT with 1:1 randomisation, stratified by age band and activity level (Tegner).",
      intervention:
        "Arthroscopic primary ACL repair with internal bracing, where feasible.",
      control: "Single-bundle hamstring tendon ACL reconstruction.",
      inclusion: [
        "Age 18 to 40",
        "MRI-confirmed proximal ACL avulsion (Sherman type 1 or 2)",
        "Presentation within 21 days of injury",
        "Pre-injury Tegner activity score 4 or higher",
      ],
      exclusion: [
        "Multi-ligament knee injury",
        "Concomitant meniscal root tear requiring repair",
        "BMI above 35",
      ],
      primaryOutcomes: [
        "KOOS subscale scores at 24 months",
        "Return-to-sport at pre-injury level (Tegner)",
      ],
      secondaryOutcomes: [
        "Revision surgery or ACL re-rupture rate",
        "IKDC at 12 and 24 months",
        "Cost per QALY (health economic sub-study)",
      ],
      status: "draft",
      sites: [
        { city: "London", country: "United Kingdom", piName: "Dr Paolo Consigliere", enrolment: 0 },
        { city: "Milan", country: "Italy", piName: "Dr Parth Patel", enrolment: 0 },
      ],
      investigators: [
        { name: "Dr Paolo Consigliere", institution: "OrthoGlobe / Fortius Clinic", role: "Chief investigator" },
      ],
      documents: [
        { name: "Protocol synopsis (draft)", type: "Protocol", url: "/uploads/research/og-aclr/protocol-synopsis.pdf" },
      ],
    },
  ];

  let inserted = 0;
  let updated = 0;

  for (const s of studies) {
    const now = new Date();
    const existing = await db
      .select({ id: schema.researchStudiesTable.id })
      .from(schema.researchStudiesTable)
      .where(eq(schema.researchStudiesTable.slug, s.slug))
      .limit(1);

    let studyId: string;

    if (existing.length === 0) {
      const [row] = await db
        .insert(schema.researchStudiesTable)
        .values({
          slug: s.slug,
          title: s.title,
          phase: s.phase,
          nctId: s.nctId,
          sponsor: s.sponsor,
          principalInvestigator: s.principalInvestigator,
          conditionKeyword: s.conditionKeyword,
          targetEnrolment: s.targetEnrolment,
          currentEnrolment: s.currentEnrolment,
          countries: s.countries,
          background: s.background,
          design: s.design,
          intervention: s.intervention,
          control: s.control,
          inclusion: s.inclusion,
          exclusion: s.exclusion,
          primaryOutcomes: s.primaryOutcomes,
          secondaryOutcomes: s.secondaryOutcomes,
          status: s.status,
          publishedAt: s.status === "published" ? now : null,
        })
        .returning({ id: schema.researchStudiesTable.id });
      studyId = row.id;
      inserted += 1;
    } else {
      studyId = existing[0].id;
      await db
        .update(schema.researchStudiesTable)
        .set({
          title: s.title,
          phase: s.phase,
          nctId: s.nctId,
          sponsor: s.sponsor,
          principalInvestigator: s.principalInvestigator,
          conditionKeyword: s.conditionKeyword,
          targetEnrolment: s.targetEnrolment,
          currentEnrolment: s.currentEnrolment,
          countries: s.countries,
          background: s.background,
          design: s.design,
          intervention: s.intervention,
          control: s.control,
          inclusion: s.inclusion,
          exclusion: s.exclusion,
          primaryOutcomes: s.primaryOutcomes,
          secondaryOutcomes: s.secondaryOutcomes,
          status: s.status,
          publishedAt: s.status === "published" ? now : null,
          updatedAt: now,
        })
        .where(eq(schema.researchStudiesTable.id, studyId));
      updated += 1;
    }

    // Replace nested children (simpler than diffing)
    await db.delete(schema.researchSites).where(eq(schema.researchSites.studyId, studyId));
    await db
      .delete(schema.researchInvestigators)
      .where(eq(schema.researchInvestigators.studyId, studyId));
    await db
      .delete(schema.researchDocuments)
      .where(eq(schema.researchDocuments.studyId, studyId));

    if (s.sites.length > 0) {
      await db.insert(schema.researchSites).values(
        s.sites.map((site, i) => ({
          studyId,
          city: site.city,
          country: site.country,
          piName: site.piName,
          enrolment: site.enrolment,
          position: i,
        })),
      );
    }

    if (s.investigators.length > 0) {
      await db.insert(schema.researchInvestigators).values(
        s.investigators.map((inv, i) => ({
          studyId,
          name: inv.name,
          institution: inv.institution,
          role: inv.role,
          position: i,
        })),
      );
    }

    if (s.documents.length > 0) {
      await db.insert(schema.researchDocuments).values(
        s.documents.map((doc, i) => ({
          studyId,
          name: doc.name,
          type: doc.type,
          url: doc.url,
          position: i,
        })),
      );
    }
  }

  console.log(
    `research_studies seed complete — ${inserted} inserted, ${updated} updated`,
  );
  await sql.end();
  process.exit(0);
}

main().catch((e) => {
  console.error(e);
  process.exit(1);
});
