/**
 * Seeds SYNTHETIC demo data (dev/testing only — TEST_PLAN §3):
 *  - demo users clearly marked with is_demo=true and "DEMO" prefix in their names
 *  - role assignments using the documented dev bypass for governance-unconfirmed roles
 *
 * Demo accounts (all with must_change_password=false and password "Bousla#Demo2026"):
 *   demo_reviewer              — reviewer (read-only)
 *   demo_gov_secretary_cairo   — governorate_secretary scoped to Cairo (governorate id 1)
 *   demo_org_secretary         — org_secretary
 *
 * Never run against production. Refuses unless PGDATABASE contains "bousla".
 */
import { one, pool } from "../src/db/client.js";
import { hashPassword } from "../src/lib/crypto.js";
import { randomUUID as cryptoRandomUUID } from "node:crypto";

const crypto = { randomUUID: cryptoRandomUUID };

const DEMO_PASSWORD = "Bousla#Demo2026";

interface DemoUser {
  username: string;
  fullName: string;
  roleCode: string;
  governorateIds?: number[];
  permissions?: string[]; // extra dev permissions granted directly via a dev-only demo role
}

const DEMO_USERS: DemoUser[] = [
  {
    username: "demo_reviewer",
    fullName: "DEMO مراجع تجريبي",
    roleCode: "reviewer",
    permissions: ["dashboard.view", "reports.view"],
  },
  {
    username: "demo_gov_secretary_cairo",
    fullName: "DEMO سكرتير القاهرة التجريبي",
    roleCode: "governorate_secretary",
    governorateIds: [1],
    permissions: ["dashboard.view", "members.view", "structure.view"],
  },
  {
    username: "demo_org_secretary",
    fullName: "DEMO أمين تنظيم تجريبي",
    roleCode: "org_secretary",
    permissions: ["dashboard.view", "members.view", "members.manage", "structure.view", "structure.manage"],
  },
];

async function main(): Promise<void> {
  const db = await one<{ current_database: string }>(`SELECT current_database()`);
  const dbName = db?.current_database ?? "";
  if (!dbName.includes("bousla")) {
    console.error(`Refusing to seed demo data into unexpected database "${dbName}".`);
    process.exit(1);
  }

  const passwordHash = await hashPassword(DEMO_PASSWORD);
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    for (const demo of DEMO_USERS) {
      const existing = await client.query<{ id: number }>(`SELECT id FROM users WHERE username = $1`, [demo.username]);
      let userId: number;
      if (existing.rows.length > 0) {
        userId = existing.rows[0]!.id;
        console.log(`- user ${demo.username} already exists (skipping create)`);
      } else {
        const inserted = await client.query<{ id: number }>(
          `INSERT INTO users (username, full_name, password_hash, is_active, must_change_password, is_demo)
           VALUES ($1, $2, $3, TRUE, FALSE, TRUE) RETURNING id`,
          [demo.username, demo.fullName, passwordHash],
        );
        userId = inserted.rows[0]!.id;
        console.log(`- created demo user ${demo.username} (id=${userId}, is_demo=true)`);
      }

      const role = await client.query<{ id: number }>(`SELECT id FROM roles WHERE code = $1`, [demo.roleCode]);
      if (role.rows.length === 0) throw new Error(`Role ${demo.roleCode} not found — run migrations first`);
      const roleId = role.rows[0]!.id;

      const already = await client.query(
        `SELECT id FROM user_roles WHERE user_id = $1 AND role_id = $2 AND revoked_at IS NULL`,
        [userId, roleId],
      );
      if (already.rows.length === 0) {
        await client.query(
          `INSERT INTO user_roles (user_id, role_id, reason) VALUES ($1, $2, 'DEMO seed: synthetic test data')`,
          [userId, roleId],
        );
        console.log(`  + assigned role ${demo.roleCode} (governance bypass — dev only)`);
      }
      if (demo.governorateIds?.length) {
        const ur = await client.query<{ id: number }>(
          `SELECT id FROM user_roles WHERE user_id = $1 AND role_id = $2 AND revoked_at IS NULL`,
          [userId, roleId],
        );
        for (const gid of demo.governorateIds) {
          await client.query(
            `INSERT INTO user_role_scopes (user_role_id, scope_type, scope_id)
             SELECT $1, 'governorate', $2 WHERE NOT EXISTS (
               SELECT 1 FROM user_role_scopes WHERE user_role_id = $1 AND scope_type = 'governorate' AND scope_id = $2
             )`,
            [ur.rows[0]!.id, gid],
          );
        }
        console.log(`  + scoped to governorate ids: ${demo.governorateIds.join(", ")}`);
      }

      // Dev-only demo permissions: create a dedicated demo permission set role per user
      if (demo.permissions?.length) {
        const demoRoleCode = `demo_perm_${demo.username}`;
        const demoRole = await client.query<{ id: number }>(
          `SELECT id FROM roles WHERE code = $1`,
          [demoRoleCode],
        );
        let demoRoleId: number;
        if (demoRole.rows.length === 0) {
          const created = await client.query<{ id: number }>(
            `INSERT INTO roles (code, name_ar, description, is_system, is_enabled, governance_confirmed, sort_order)
             VALUES ($1, $2, 'DEMO فقط — صلاحيات تطويرية اصطناعية', TRUE, TRUE, TRUE, 900) RETURNING id`,
            [demoRoleCode, `DEMO صلاحيات ${demo.username}`],
          );
          demoRoleId = created.rows[0]!.id;
        } else {
          demoRoleId = demoRole.rows[0]!.id;
        }
        for (const perm of demo.permissions) {
          await client.query(
            `INSERT INTO role_permissions (role_id, permission_id)
             SELECT $1, id FROM permissions WHERE code = $2
             ON CONFLICT DO NOTHING`,
            [demoRoleId, perm],
          );
        }
        const hasDemoRole = await client.query(
          `SELECT id FROM user_roles WHERE user_id = $1 AND role_id = $2 AND revoked_at IS NULL`,
          [userId, demoRoleId],
        );
        if (hasDemoRole.rows.length === 0) {
          await client.query(
            `INSERT INTO user_roles (user_id, role_id, reason) VALUES ($1, $2, 'DEMO seed: synthetic dev permissions')`,
            [userId, demoRoleId],
          );
        }
        console.log(`  + demo permissions: ${demo.permissions.join(", ")}`);
      }
    }

    // Synthetic DEMO members across governorates (synthetic data only — TEST_PLAN §3)
    const demoMembers: { name: string; gov: number | null; phone?: string; email?: string }[] = [
      { name: "DEMO عضو تجريبي — القاهرة 1", gov: 1, phone: "10000000001", email: "demo1@bousla.test" },
      { name: "DEMO عضو تجريبي — القاهرة 2", gov: 1, phone: "10000000002" },
      { name: "DEMO عضو تجريبي — القاهرة 3", gov: 1 },
      { name: "DEMO عضو تجريبي — الجيزة 1", gov: 14, phone: "10000000003" },
      { name: "DEMO عضو تجريبي — الإسكندرية 1", gov: 2 },
      { name: "DEMO عضو تجريبي — أسيوط 1", gov: 18 },
      { name: "DEMO عضو تجريبي — بدون محافظة", gov: null },
    ];
    const statusRow = await client.query<{ id: number }>(
      `SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
        WHERE sg.code = 'member_formal_status' AND s.code = 'unspecified'`,
    );
    const statusId = statusRow.rows[0]!.id;
    let createdCount = 0;
    for (const dm of demoMembers) {
      const exists = await client.query(`SELECT id FROM members WHERE full_name = $1 AND is_demo`, [dm.name]);
      if (exists.rows.length > 0) continue;
      const uuid = crypto.randomUUID();
      const inserted = await client.query<{ id: number }>(
        `INSERT INTO members (public_uuid, full_name, governorate_id, formal_membership_status_id, is_demo, record_source)
         VALUES ($1::uuid, $2, $3, $4, TRUE, 'demo_seed') RETURNING id`,
        [uuid, dm.name, dm.gov, statusId],
      );
      const mid = inserted.rows[0]!.id;
      await client.query(
        `INSERT INTO member_status_history (member_id, to_status_id, reason)
         VALUES ($1, $2, 'DEMO seed: تعيين الحالة الافتراضية')`,
        [mid, statusId],
      );
      if (dm.phone || dm.email) {
        await client.query(
          `INSERT INTO member_contacts (member_id, phone_primary, email, consent_contact_at, consent_notice_version, consent_channel)
           VALUES ($1, $2, $3, now(), 'demo-v1', 'phone')`,
          [mid, dm.phone ?? null, dm.email ?? null],
        );
      }
      createdCount++;
    }
    console.log(`- demo members ensured (${createdCount} new, ${demoMembers.length} total) — scope demo: Cairo ids 1`);

    // G-3 demo: one Cairo member suspended for 30 days (shows the duration feature in dev)
    const suspendedStatus = await client.query<{ id: number }>(
      `SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
        WHERE sg.code = 'member_formal_status' AND s.code = 'suspended'`,
    );
    if (suspendedStatus.rows.length > 0) {
      const target = await client.query<{ id: number; sid: number }>(
        `SELECT m.id, m.formal_membership_status_id AS sid FROM members m
          WHERE m.full_name = 'DEMO عضو تجريبي — القاهرة 2' AND m.is_demo`,
      );
      if (target.rows.length > 0) {
        const t = target.rows[0]!;
        if (t.sid !== suspendedStatus.rows[0]!.id) {
          const until = new Date(Date.now() + 30 * 24 * 3600 * 1000).toISOString().slice(0, 10);
          await client.query(
            `UPDATE members SET formal_membership_status_id = $2, suspension_until = $3 WHERE id = $1`,
            [t.id, suspendedStatus.rows[0]!.id, until],
          );
          await client.query(
            `INSERT INTO member_status_history (member_id, from_status_id, to_status_id, reason, suspension_until, created_by)
             VALUES ($1, $2, $3, 'DEMO seed: إيقاف تجريبي بمدة 30 يومًا', $4,
                     (SELECT id FROM users WHERE username = 'demo_org_secretary'))`,
            [t.id, t.sid, suspendedStatus.rows[0]!.id, until],
          );
          console.log(`- demo suspension: القاهرة 2 موقوف حتى ${until}`);
        }
      }
    }

    // Structure demo: one national unit + one Cairo unit with two documented positions
    const orgSec = await client.query<{ id: number }>(`SELECT id FROM users WHERE username = 'demo_org_secretary'`);
    const orgSecId = orgSec.rows[0]?.id ?? null;
    const units: { kind: "national" | "governorate"; gov: number | null; name: string; ref: string }[] = [
      { kind: "national", gov: null, name: "DEMO الأمانة العامة التجريبية", ref: "DEMO-REF-NATIONAL-001" },
      { kind: "governorate", gov: 1, name: "DEMO سكرتارية محافظة القاهرة التجريبية", ref: "DEMO-REF-CAIRO-001" },
    ];
    const unitIds: Record<string, number> = {};
    for (const u of units) {
      const exists = await client.query<{ id: number }>(
        `SELECT id FROM org_units WHERE name_ar = $1 AND is_demo AND deleted_at IS NULL`,
        [u.name],
      );
      if (exists.rows.length === 0) {
        const created = await client.query<{ id: number }>(
          `INSERT INTO org_units (unit_kind, governorate_id, name_ar, formation_decision_ref, is_demo, created_by)
           VALUES ($1, $2, $3, $4, TRUE, $5) RETURNING id`,
          [u.kind, u.gov, u.name, u.ref, orgSecId],
        );
        unitIds[u.name] = created.rows[0]!.id;
        console.log(`- demo unit: ${u.name}`);
      } else {
        unitIds[u.name] = exists.rows[0]!.id;
      }
    }
    const cairoUnit = unitIds["DEMO سكرتارية محافظة القاهرة التجريبية"]!;
    const cairoMember = await client.query<{ id: number }>(
      `SELECT id FROM members WHERE full_name = 'DEMO عضو تجريبي — القاهرة 1' AND is_demo`,
    );
    const demoPositions: { unit: number; title: string; ref: string; memberId: number | null }[] = [
      { unit: cairoUnit, title: "DEMO سكرتير المحافظة (منصب تجريبي)", ref: "DEMO-REF-POS-001", memberId: cairoMember.rows[0]?.id ?? null },
      { unit: cairoUnit, title: "DEMO أمين التنظيم بالمحافظة (منصب تجريبي)", ref: "DEMO-REF-POS-002", memberId: null },
    ];
    for (const p of demoPositions) {
      const exists = await client.query(`SELECT id FROM org_positions WHERE org_unit_id = $1 AND title_ar = $2 AND is_demo`, [p.unit, p.title]);
      if (exists.rows.length === 0) {
        await client.query(
          `INSERT INTO org_positions (org_unit_id, member_id, title_ar, appointment_decision_ref, is_demo, created_by)
           VALUES ($1, $2, $3, $4, TRUE, $5)`,
          [p.unit, p.memberId, p.title, p.ref, orgSecId],
        );
      }
    }
    console.log(`- demo positions ensured on Cairo unit (${demoPositions.length})`);

    await client.query(
      `INSERT INTO audit_logs (action, entity_type, context)
       VALUES ('demo.seed', 'system', jsonb_build_object('users', $1::int))`,
      [DEMO_USERS.length],
    );
    await client.query("COMMIT");
    console.log(`\nDemo seed complete. All demo passwords: ${DEMO_PASSWORD}`);
    console.log("These accounts are synthetic (is_demo=true) for development and testing only.");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
    await pool.end();
  }
}

main().catch((error) => {
  console.error(error instanceof Error ? error.message : error);
  process.exit(1);
});