BEGIN;

-- Ensure the UUID extension is available (PostgreSQL 16+ ships uuid_generate_v7 natively)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Staging schema that houses the FDW tables + migration helper maps
CREATE SCHEMA IF NOT EXISTS legacy;

-- ---------------------------------------------------------------------------
-- Mapping tables – persist UUIDs so the migration is 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.location_branch_map (
    legacy_location_id INTEGER PRIMARY KEY,
    branch_id          UUID    NOT NULL,
    org_id             UUID    NOT NULL,
    generated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.gl_journal_entry_map (
    legacy_id         INTEGER PRIMARY KEY,
    journal_entry_id  UUID    NOT NULL,
    org_id            UUID    NOT NULL,
    generated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.gl_transaction_map (
    legacy_id        INTEGER PRIMARY KEY,
    gl_transaction_id UUID   NOT NULL,
    journal_entry_id  UUID   NOT NULL,
    org_id            UUID   NOT NULL,
    generated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.employee_map (
    legacy_employee_id INTEGER PRIMARY KEY,
    employee_id        UUID    NOT NULL,
    org_id             UUID    NOT NULL,
    generated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.case_map (
    legacy_case_id INTEGER PRIMARY KEY,
    case_id        UUID    NOT NULL,
    org_id         UUID    NOT NULL,
    generated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS legacy.appeal_map (
    legacy_id   INTEGER PRIMARY KEY,
    appeal_id   UUID    NOT NULL,
    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()
);

-- ---------------------------------------------------------------------------
-- Companies → orgs (tenant roots)
-- ---------------------------------------------------------------------------
WITH company_source AS (
    SELECT c.id,
        c.name,
        c.updatedAt,
        COALESCE(m.org_id, o.org_id) AS existing_org_id
      FROM legacy.companies c
      LEFT JOIN legacy.company_org_map m
          ON m.legacy_company_id = c.id
      LEFT JOIN orgs o
          ON o.org_id = m.org_id
          OR lower(o.name) = lower(c.name)
),
prepared_companies AS (
    SELECT cs.id,
        cs.name,
        cs.updatedAt,
        COALESCE(cs.existing_org_id, uuid_generate_v7()) AS org_id
      FROM company_source cs
)
INSERT INTO legacy.company_org_map (legacy_company_id, org_id)
SELECT id, org_id
  FROM prepared_companies
ON CONFLICT (legacy_company_id) DO UPDATE
    SET org_id = EXCLUDED.org_id;

INSERT INTO orgs (org_id, parent_org_id, name, kind, updatedAt)
SELECT pc.org_id,
    NULL,
    pc.name,
    'tenant',
    COALESCE(pc.updatedAt AT TIME ZONE 'UTC', now())
  FROM prepared_companies pc
  LEFT JOIN orgs o ON o.org_id = pc.org_id
 WHERE o.org_id IS NULL;

-- ---------------------------------------------------------------------------
-- Locations → branches (child org units)
-- ---------------------------------------------------------------------------
WITH source_locations AS (
    SELECT l.id,
           l.company_id,
           l.name,
           l.latitude,
           l.longitude,
           l.radius_m,
           l.updatedAt
      FROM legacy.locations l
     WHERE l.company_id IS NOT NULL
),
new_locations AS (
    SELECT s.id,
           s.company_id,
           s.name,
           s.latitude,
           s.longitude,
           s.radius_m,
           s.updatedAt,
           m.org_id
      FROM source_locations s
      JOIN legacy.company_org_map m ON m.legacy_company_id = s.company_id
      LEFT JOIN legacy.location_branch_map lbm ON lbm.legacy_location_id = s.id
     WHERE lbm.legacy_location_id IS NULL
)
INSERT INTO legacy.location_branch_map (legacy_location_id, branch_id, org_id)
SELECT id, uuid_generate_v7(), org_id
  FROM new_locations;

INSERT INTO branches (branch_id, org_id, name, latitude, longitude, settings, updatedAt)
SELECT lbm.branch_id,
       lbm.org_id,
       s.name,
       s.latitude,
       s.longitude,
       jsonb_build_object(
           'legacy_location_id', s.id,
           'radius_m',          s.radius_m
       ),
       COALESCE(s.updatedAt AT TIME ZONE 'UTC', now())
  FROM legacy.location_branch_map lbm
  JOIN source_locations s ON s.id = lbm.legacy_location_id
  LEFT JOIN branches b    ON b.branch_id = lbm.branch_id
 WHERE b.branch_id IS NULL;

COMMIT;
