BEGIN;

-- Ensure required extensions and schemas exist.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE SCHEMA IF NOT EXISTS legacy;

-- Mapping tables (definitions duplicated here to keep the script idempotent).
CREATE TABLE IF NOT EXISTS legacy.company_org_map (
    legacy_company_id INTEGER PRIMARY KEY,
    org_id            UUID    NOT NULL,
    generated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.user_map (
    legacy_user_id INTEGER PRIMARY KEY,
    user_id        UUID    NOT NULL,
    org_id         UUID    NOT NULL,
    generated_at   timestamptz NOT NULL DEFAULT now()
);

-- Biosecurity Authority of Fiji org seed.
INSERT INTO orgs (org_id, parent_org_id, name, kind, custom_domain, dashboard_name, product_name)
VALUES (
    '0199b208-8943-7ca3-ba31-781efbe25058'::uuid,
    NULL,
    'Biosecurity Authority of Fiji',
    'tenant',
    'biosecurity.cybexpte.com',
    'Biosecurity Authority of Fiji',
    'Biosecurity Authority of Fiji'
)
ON CONFLICT (org_id) DO UPDATE
    SET name = EXCLUDED.name,
        kind = EXCLUDED.kind,
        custom_domain = EXCLUDED.custom_domain,
        dashboard_name = EXCLUDED.dashboard_name,
        product_name = EXCLUDED.product_name,
        updatedat = now();

-- Map the legacy company id to the new org id.
INSERT INTO legacy.company_org_map (legacy_company_id, org_id)
VALUES (1, '0199b208-8943-7ca3-ba31-781efbe25058'::uuid)
ON CONFLICT (legacy_company_id) DO UPDATE
    SET org_id = EXCLUDED.org_id,
        generated_at = now();

-- Prepare UUIDs for every legacy user and upsert into public.users.
WITH source_users AS (
    SELECT u.id                AS legacy_user_id,
           u.full_name,
           u.password,
           u.company_id,
           u.is_admin,
           u.isdeleted,
           u.updatedat,
           COALESCE(um.user_id, uuid_generate_v7()) AS user_id
      FROM taf_legacy.users u
      LEFT JOIN legacy.user_map um
             ON um.legacy_user_id = u.id
),
upsert_map AS (
    INSERT INTO legacy.user_map (legacy_user_id, user_id, org_id)
    SELECT su.legacy_user_id,
           su.user_id,
           '0199b208-8943-7ca3-ba31-781efbe25058'::uuid
      FROM source_users su
    ON CONFLICT (legacy_user_id) DO UPDATE
        SET user_id = EXCLUDED.user_id,
            org_id  = EXCLUDED.org_id,
            generated_at = now()
    RETURNING legacy_user_id, user_id
)
INSERT INTO public.users (user_id, full_name, status, is_superadmin, isdeleted, updatedat)
SELECT su.user_id,
       NULLIF(trim(su.full_name), ''),
       CASE WHEN su.isdeleted THEN 'inactive' ELSE 'active' END,
       su.is_admin,
       su.isdeleted,
       COALESCE(su.updatedat, now())
  FROM source_users su
ON CONFLICT (user_id) DO UPDATE
    SET full_name     = COALESCE(EXCLUDED.full_name, public.users.full_name),
        status        = EXCLUDED.status,
        is_superadmin = EXCLUDED.is_superadmin,
        isdeleted     = EXCLUDED.isdeleted,
        updatedat     = EXCLUDED.updatedat;

-- Upsert user email addresses and mark the earliest address as primary.
WITH email_source AS (
    SELECT ue.id,
           ue.user_id        AS legacy_user_id,
           lower(ue.email)   AS email,
           ue.isdeleted,
           ue.updatedat,
           um.user_id        AS user_id
      FROM taf_legacy.user_emails ue
      JOIN legacy.user_map um ON um.legacy_user_id = ue.user_id
     WHERE ue.email IS NOT NULL
       AND trim(ue.email) <> ''
), ranked_emails AS (
    SELECT es.*,
           ROW_NUMBER() OVER (PARTITION BY es.user_id ORDER BY es.id) AS rownum
      FROM email_source es
)
INSERT INTO public.user_emails (user_id, email, is_primary, isdeleted, updatedat)
SELECT re.user_id,
       re.email::citext,
       (re.rownum = 1),
       re.isdeleted,
       COALESCE(re.updatedat, now())
  FROM ranked_emails re
ON CONFLICT (user_id, email) DO UPDATE
    SET is_primary = user_emails.is_primary OR EXCLUDED.is_primary,
        isdeleted  = EXCLUDED.isdeleted,
        updatedat  = EXCLUDED.updatedat;

-- Persist password hashes.
INSERT INTO public.user_passwords (user_id, password_hash, algo, updatedat)
SELECT um.user_id,
       u.password,
       'bcrypt',
       COALESCE(u.updatedat, now())
  FROM taf_legacy.users u
  JOIN legacy.user_map um ON um.legacy_user_id = u.id
 WHERE u.password IS NOT NULL
   AND trim(u.password) <> ''
ON CONFLICT (user_id) DO UPDATE
    SET password_hash = EXCLUDED.password_hash,
        algo          = EXCLUDED.algo,
        updatedat     = EXCLUDED.updatedat;

-- Attach every user to the Biosecurity org.
INSERT INTO public.user_org_memberships (
    membership_id,
    user_id,
    org_id,
    role,
    options,
    joined_at,
    isdeleted,
    updatedat
)
SELECT uuid_generate_v7(),
       um.user_id,
       '0199b208-8943-7ca3-ba31-781efbe25058'::uuid,
       CASE WHEN u.is_admin THEN 'admin' ELSE 'member' END,
       jsonb_build_object(
           'legacy_user_id', u.id,
           'legacy_company_id', u.company_id
       ),
       COALESCE(u.updatedat, now()),
       u.isdeleted,
       COALESCE(u.updatedat, now())
  FROM taf_legacy.users u
  JOIN legacy.user_map um ON um.legacy_user_id = u.id
ON CONFLICT (user_id, org_id) DO UPDATE
    SET role      = EXCLUDED.role,
        options   = COALESCE(user_org_memberships.options, '{}'::jsonb) || EXCLUDED.options,
        isdeleted = EXCLUDED.isdeleted,
        updatedat = EXCLUDED.updatedat;

COMMIT;
