/**
 * Dev environment seed — idempotent fixtures for Tariq's day-to-day testing.
 *
 * Distinct from tests/e2e/scripts/seed.ts (which truncates everything for a
 * deterministic Playwright run). This script:
 *   - leaves untouched data alone
 *   - deletes-then-inserts ONLY rows that match the well-known seed emails
 *   - inserts richer fixtures (3 patients, 2 doctors, an appointment, and —
 *     if Phase 18 has shipped — three subscription packages).
 *
 * Run via:  npm run seed   (from the repo root)
 */
import { Client } from 'pg';
import * as argon2 from 'argon2';
import { config as loadEnv } from 'dotenv';
import { resolve } from 'node:path';

loadEnv({ path: resolve(__dirname, '..', 'backend', '.env') });

type SeededUser = {
  email: string;
  password: string;
  firstName: string;
  lastName: string;
  role: 'admin' | 'doctor' | 'patient';
};

const ADMIN: SeededUser = {
  email: 'admin@sehat.local',
  password: 'AdminPass1!',
  firstName: 'Sehat',
  lastName: 'Admin',
  role: 'admin',
};

const DOCTORS: Array<SeededUser & { specialty: string }> = [
  {
    email: 'cardio@sehat.local',
    password: 'DoctorPass1!',
    firstName: 'Imran',
    lastName: 'Rashid',
    role: 'doctor',
    specialty: 'cardiology',
  },
  {
    email: 'derma@sehat.local',
    password: 'DoctorPass1!',
    firstName: 'Sana',
    lastName: 'Mahmood',
    role: 'doctor',
    specialty: 'dermatology',
  },
];

const PATIENTS: Array<SeededUser & { dob: string; gender: 'male' | 'female' }> = [
  {
    email: 'patient1@sehat.local',
    password: 'PatientPass1!',
    firstName: 'Ayesha',
    lastName: 'Siddiqui',
    role: 'patient',
    dob: '1995-04-12',
    gender: 'female',
  },
  {
    email: 'patient2@sehat.local',
    password: 'PatientPass1!',
    firstName: 'Bilal',
    lastName: 'Ahmed',
    role: 'patient',
    dob: '1988-11-03',
    gender: 'male',
  },
  {
    email: 'patient3@sehat.local',
    password: 'PatientPass1!',
    firstName: 'Hira',
    lastName: 'Qureshi',
    role: 'patient',
    dob: '2001-07-22',
    gender: 'female',
  },
];

const ALL_SEED_EMAILS = [
  ADMIN.email,
  ...DOCTORS.map((d) => d.email),
  ...PATIENTS.map((p) => p.email),
];

async function hash(plain: string): Promise<string> {
  return argon2.hash(plain, { type: argon2.argon2id });
}

async function tableExists(client: Client, name: string): Promise<boolean> {
  const res = await client.query(
    `SELECT 1 FROM information_schema.tables
     WHERE table_schema = 'public' AND table_name = $1`,
    [name],
  );
  return (res.rowCount ?? 0) > 0;
}

async function main() {
  if (process.env.NODE_ENV === 'production') {
    throw new Error('Refusing to run dev-seed with NODE_ENV=production');
  }

  const client = new Client({
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5433', 10),
    user: process.env.DB_USERNAME || 'sehat',
    password: process.env.DB_PASSWORD || 'sehat_dev',
    database: process.env.DB_DATABASE || 'sehat_sahoolat',
  });
  await client.connect();
  console.log(`[seed] connected to ${client.database} on ${client.host}:${client.port}`);

  try {
    await client.query('BEGIN');

    // ----- Wipe prior seed rows (idempotent re-run) -----
    // Delete child rows first to avoid FK trouble. We only target rows tied to
    // the seed emails, so production-like data (if any) is left alone.
    const userIdsRes = await client.query(
      `SELECT id, email, role FROM users WHERE email = ANY($1::text[])`,
      [ALL_SEED_EMAILS],
    );
    const oldUserIds = userIdsRes.rows.map((r) => r.id);

    if (oldUserIds.length > 0) {
      // appointments reference users directly
      await client.query(
        `DELETE FROM appointments WHERE patient_id = ANY($1::uuid[]) OR doctor_id = ANY($1::uuid[])`,
        [oldUserIds],
      );
      // doctor side
      const oldDoctorIds = (
        await client.query(`SELECT id FROM doctors WHERE user_id = ANY($1::uuid[])`, [oldUserIds])
      ).rows.map((r) => r.id);
      if (oldDoctorIds.length > 0) {
        await client.query(`DELETE FROM doctor_schedules WHERE doctor_id = ANY($1::uuid[])`, [
          oldDoctorIds,
        ]);
        await client.query(`DELETE FROM doctor_specialties WHERE doctor_id = ANY($1::uuid[])`, [
          oldDoctorIds,
        ]);
        await client.query(`DELETE FROM doctor_qualifications WHERE doctor_id = ANY($1::uuid[])`, [
          oldDoctorIds,
        ]);
        await client.query(`DELETE FROM doctor_licenses WHERE doctor_id = ANY($1::uuid[])`, [
          oldDoctorIds,
        ]);
      }
      await client.query(`DELETE FROM doctors WHERE user_id = ANY($1::uuid[])`, [oldUserIds]);

      // patient side — EMR demographics + records linked via patient profile id
      const oldPatientIds = (
        await client.query(`SELECT id FROM patients WHERE user_id = ANY($1::uuid[])`, [oldUserIds])
      ).rows.map((r) => r.id);
      if (oldPatientIds.length > 0) {
        const oldEmrIds = (
          await client.query(`SELECT id FROM emr_records WHERE patient_id = ANY($1::uuid[])`, [
            oldPatientIds,
          ])
        ).rows.map((r) => r.id);
        if (oldEmrIds.length > 0) {
          await client.query(`DELETE FROM emr_demographics WHERE emr_id = ANY($1::uuid[])`, [
            oldEmrIds,
          ]);
          await client.query(`DELETE FROM emr_records WHERE id = ANY($1::uuid[])`, [oldEmrIds]);
        }
      }
      await client.query(`DELETE FROM patients WHERE user_id = ANY($1::uuid[])`, [oldUserIds]);

      // misc
      await client.query(`DELETE FROM refresh_tokens WHERE user_id = ANY($1::uuid[])`, [
        oldUserIds,
      ]);
      await client.query(`DELETE FROM users WHERE id = ANY($1::uuid[])`, [oldUserIds]);
    }

    // ----- Insert fresh -----
    const allUsers: SeededUser[] = [ADMIN, ...DOCTORS, ...PATIENTS];
    const hashes = await Promise.all(allUsers.map((u) => hash(u.password)));
    const userIdByEmail = new Map<string, string>();

    for (let i = 0; i < allUsers.length; i++) {
      const u = allUsers[i];
      const res = await client.query(
        `INSERT INTO users
           (email, first_name, last_name, password, role, status,
            email_verified_at, mfa_enabled, locale, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5::users_role_enum, 'active'::users_status_enum,
                 NOW(), false, 'en', NOW(), NOW())
         RETURNING id`,
        [u.email, u.firstName, u.lastName, hashes[i], u.role],
      );
      const id: string = res.rows[0].id;
      userIdByEmail.set(u.email, id);
      console.log(`[seed]  + user ${u.role.padEnd(7)} ${u.email}`);
    }

    // ----- Doctor profiles -----
    const doctorProfileIdByEmail = new Map<string, string>();
    for (const d of DOCTORS) {
      const userId = userIdByEmail.get(d.email)!;
      const res = await client.query(
        `INSERT INTO doctors
           (user_id, verification_status, live_status, native_language,
            title, experience, license_verified, consultation_fee, fee_currency,
            slot_duration_minutes, rating_avg, rating_count, created_at, updated_at)
         VALUES ($1, 'approved', true, 'Urdu',
                 'Dr.', 10, true, 2500, 'PKR',
                 30, 4.7, 12, NOW(), NOW())
         RETURNING id`,
        [userId],
      );
      const doctorId: string = res.rows[0].id;
      doctorProfileIdByEmail.set(d.email, doctorId);

      await client.query(
        `INSERT INTO doctor_specialties
           (doctor_id, specialty_name, is_primary, created_at, updated_at)
         VALUES ($1, $2, true, NOW(), NOW())`,
        [doctorId, d.specialty],
      );

      for (const dow of [1, 2, 3, 4, 5]) {
        await client.query(
          `INSERT INTO doctor_schedules
             (doctor_id, day_of_week, start_time, end_time, timezone,
              is_active, created_at, updated_at)
           VALUES ($1, $2, '09:00', '17:00', 'Asia/Karachi', true, NOW(), NOW())`,
          [doctorId, dow],
        );
      }
      console.log(`[seed]  + doctor profile ${d.email} (${d.specialty}, Mon-Fri 09:00-17:00 PKT)`);
    }

    // ----- Patient profiles + EMR -----
    for (const p of PATIENTS) {
      const userId = userIdByEmail.get(p.email)!;
      const patientRes = await client.query(
        `INSERT INTO patients (user_id, created_at, updated_at)
         VALUES ($1, NOW(), NOW())
         RETURNING id`,
        [userId],
      );
      const patientId: string = patientRes.rows[0].id;

      const emrRes = await client.query(
        `INSERT INTO emr_records (patient_id, status, created_at, updated_at)
         VALUES ($1, 'in_progress', NOW(), NOW())
         RETURNING id`,
        [patientId],
      );
      const emrId: string = emrRes.rows[0].id;

      await client.query(
        `INSERT INTO emr_demographics
           (emr_id, first_name, last_name, gender, date_of_birth, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5::date, NOW(), NOW())`,
        [emrId, p.firstName, p.lastName, p.gender, p.dob],
      );
      console.log(`[seed]  + patient profile ${p.email} (EMR shell ready)`);
    }

    // ----- Sample appointment: patient1 with cardiologist, tomorrow 10am PKT -----
    const cardioUserId = userIdByEmail.get('cardio@sehat.local')!;
    const patient1UserId = userIdByEmail.get('patient1@sehat.local')!;
    // Tomorrow 10:00 in Asia/Karachi (UTC+5) → 05:00 UTC
    const tomorrow = new Date();
    tomorrow.setUTCDate(tomorrow.getUTCDate() + 1);
    const scheduledAt = new Date(
      Date.UTC(
        tomorrow.getUTCFullYear(),
        tomorrow.getUTCMonth(),
        tomorrow.getUTCDate(),
        5, // 10:00 PKT
        0,
        0,
        0,
      ),
    );
    await client.query(
      `INSERT INTO appointments
         (patient_id, doctor_id, specialty_name, status, scheduled_at, timezone,
          symptoms, urgency, created_at, updated_at)
       VALUES ($1, $2, 'cardiology', 'pending', $3, 'Asia/Karachi',
               'Occasional chest tightness after exertion', 'routine', NOW(), NOW())`,
      [patient1UserId, cardioUserId, scheduledAt.toISOString()],
    );
    console.log(
      `[seed]  + appointment patient1 -> cardio @ ${scheduledAt.toISOString()} (pending)`,
    );

    // ----- Packages (only if Phase 18 backend has shipped) -----
    if (await tableExists(client, 'packages')) {
      const PKGS = [
        {
          name: 'Pay As You Go',
          slug: 'pay-as-you-go',
          description: 'No commitment - pay only when you book.',
          priceMinor: 3000,
          currency: 'USD',
          durationDays: 30,
          maxConsultations: 0,
          maxEmrRecords: 1,
          maxDevices: 10,
          features: 'Registration for one person,Single patient EMR,Pay per appointment',
          sortOrder: 10,
          tierType: 'payg',
          registrationFeeMinor: 0,
          maxFamilyMembers: 1,
          extraFamilyMemberFeeMinor: 0,
          maxExtraFamilyMembers: 0,
          bundleAppointmentCount: null as number | null,
          bundleDiscountPercent: null as number | null,
          billingCycle: 'one_time',
          minCommitmentMonths: 0,
          firstMonthFeeWaived: false,
        },
        {
          name: 'Credit Bundles',
          slug: 'credit-bundles',
          description: 'Family plan with tiered savings - buy in bulk, save more.',
          priceMinor: 0,
          currency: 'USD',
          durationDays: 90,
          maxConsultations: 3,
          maxEmrRecords: 4,
          maxDevices: 10,
          features: 'Registration for family,3 appointment starter bundle,Discounted follow-ups,Verifiable prescriptions',
          sortOrder: 20,
          tierType: 'bundle',
          registrationFeeMinor: 9000,
          maxFamilyMembers: 4,
          extraFamilyMemberFeeMinor: 0,
          maxExtraFamilyMembers: 0,
          bundleAppointmentCount: 3,
          bundleDiscountPercent: 10,
          billingCycle: 'one_time',
          minCommitmentMonths: 0,
          firstMonthFeeWaived: false,
        },
        {
          name: 'Subscription',
          slug: 'subscription',
          description: 'Unlimited consultations for the whole family - best value.',
          priceMinor: 1000,
          currency: 'USD',
          durationDays: 30,
          maxConsultations: null as number | null,
          maxEmrRecords: null as number | null,
          maxDevices: 10,
          features: 'popular,Family of 4,Unlimited consultations,Unlimited follow-ups,Always-up-to-date EMR',
          sortOrder: 30,
          tierType: 'subscription',
          registrationFeeMinor: 1000,
          maxFamilyMembers: 4,
          extraFamilyMemberFeeMinor: 500,
          maxExtraFamilyMembers: 2,
          bundleAppointmentCount: null as number | null,
          bundleDiscountPercent: null as number | null,
          billingCycle: 'monthly',
          minCommitmentMonths: 12,
          firstMonthFeeWaived: true,
        },
      ];
      await client.query(`DELETE FROM packages WHERE slug = ANY($1::text[])`, [
        ['starter', 'family', 'wellness', ...PKGS.map((p) => p.slug)],
      ]);
      for (const pkg of PKGS) {
        await client.query(
          `INSERT INTO packages
             (name, slug, description, price_minor, currency, duration_days,
              max_consultations, max_emr_records, max_devices, features,
              is_active, sort_order, tier_type, registration_fee_minor,
              max_family_members, extra_family_member_fee_minor,
              max_extra_family_members, bundle_appointment_count,
              bundle_discount_percent, billing_cycle, min_commitment_months,
              first_month_fee_waived, created_at, updated_at)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, true, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, NOW(), NOW())`,
          [
            pkg.name,
            pkg.slug,
            pkg.description,
            pkg.priceMinor,
            pkg.currency,
            pkg.durationDays,
            pkg.maxConsultations,
            pkg.maxEmrRecords,
            pkg.maxDevices,
            pkg.features,
            pkg.sortOrder,
            pkg.tierType,
            pkg.registrationFeeMinor,
            pkg.maxFamilyMembers,
            pkg.extraFamilyMemberFeeMinor,
            pkg.maxExtraFamilyMembers,
            pkg.bundleAppointmentCount,
            pkg.bundleDiscountPercent,
            pkg.billingCycle,
            pkg.minCommitmentMonths,
            pkg.firstMonthFeeWaived,
          ],
        );
        console.log(
          `[seed]  + package ${pkg.name} (${pkg.currency} ${(pkg.priceMinor / 100).toFixed(0)}, ${pkg.durationDays} days)`,
        );
      }
    } else {
      console.log('[seed]  - packages table not present yet (Phase 18) — skipping');
    }

    await client.query('COMMIT');
    console.log('[seed] ✓ done.');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    await client.end();
  }
}

main().catch((err) => {
  console.error('[seed] failed:', err);
  process.exit(1);
});