BEGIN;

-- Ensure required extensions are available.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE SCHEMA IF NOT EXISTS legacy;

-- Mapping table to keep imported clock UUIDs stable across reruns.
CREATE TABLE IF NOT EXISTS legacy.time_clock_map (
    legacy_clock_id INTEGER PRIMARY KEY,
    time_clock_id   UUID    NOT NULL,
    org_id          UUID    NOT NULL,
    branch_id       UUID,
    user_id         UUID    NOT NULL,
    generated_at    timestamptz NOT NULL DEFAULT now()
);

-- Ensure location → branch mapping table exists even if bootstrap has not run.
CREATE TABLE IF NOT EXISTS legacy.location_branch_map (
    legacy_location_id INTEGER PRIMARY KEY,
    branch_id          UUID,
    org_id             UUID,
    generated_at       timestamptz NOT NULL DEFAULT now()
);

-- Align the canonical table with the multi-tenant attendance design.
ALTER TABLE public.time_clocks
    ADD COLUMN IF NOT EXISTS lat numeric(10,6),
    ADD COLUMN IF NOT EXISTS lng numeric(10,6),
    ADD COLUMN IF NOT EXISTS tenant_id UUID,
    ADD COLUMN IF NOT EXISTS branch_id UUID,
    ADD COLUMN IF NOT EXISTS location geography(Point, 4326),
    ADD COLUMN IF NOT EXISTS location_id_legacy INTEGER,
    ADD COLUMN IF NOT EXISTS source jsonb DEFAULT '{}'::jsonb;

-- Import legacy time clock punches.
WITH source AS (
    SELECT
        tc.id                       AS legacy_clock_id,
        tc.user_id                  AS legacy_user_id,
        tc.clock_type,
        tc.lat,
        tc.lng,
        tc.valid_gps,
        tc.location_id              AS legacy_location_id,
        tc.locationApproved,
        tc.security_type,
        tc.clocked_at,
        tc.isDeleted,
        tc.updatedAt           AS updatedat,
        um.user_id                  AS user_id,
        um.org_id                   AS org_id,
        lbm.branch_id,
        lbm.org_id                  AS branch_org_id
    FROM taf_legacy.time_clocks tc
    JOIN legacy.user_map um ON um.legacy_user_id = tc.user_id
    LEFT JOIN legacy.location_branch_map lbm
           ON lbm.legacy_location_id = tc.location_id
), prepared AS (
    SELECT
        s.legacy_clock_id,
        s.legacy_location_id,
        s.clock_type,
        s.lat,
        s.lng,
    s.valid_gps,
    s.locationApproved,
        s.security_type,
        s.clocked_at,
        s.isDeleted,
                s.updatedat,
                s.user_id,
                s.org_id,
                COALESCE(s.branch_id, NULL) AS branch_id,
                COALESCE(tcm.time_clock_id, uuid_generate_v7()) AS time_clock_id,
                (CASE WHEN s.lat IS NOT NULL AND s.lng IS NOT NULL
                            THEN ST_SetSRID(ST_MakePoint(s.lng::double precision, s.lat::double precision), 4326)::geography
                            ELSE NULL END) AS location_geom,
            s.valid_gps::boolean        AS valid_gps_bool,
            s.locationApproved::boolean AS location_approved_bool,
            COALESCE(s.isDeleted::boolean, false) AS is_deleted_bool,
        o.root_org_id
    FROM source s
    LEFT JOIN legacy.time_clock_map tcm
           ON tcm.legacy_clock_id = s.legacy_clock_id
    LEFT JOIN orgs o
           ON o.org_id = s.org_id
), upsert_map AS (
    INSERT INTO legacy.time_clock_map (legacy_clock_id, time_clock_id, org_id, branch_id, user_id)
    SELECT
        p.legacy_clock_id,
        p.time_clock_id,
        p.org_id,
        p.branch_id,
        p.user_id
    FROM prepared p
    ON CONFLICT (legacy_clock_id) DO UPDATE
        SET time_clock_id = EXCLUDED.time_clock_id,
            org_id       = EXCLUDED.org_id,
            branch_id    = EXCLUDED.branch_id,
            user_id      = EXCLUDED.user_id,
            generated_at = now()
    RETURNING legacy_clock_id, time_clock_id
)
INSERT INTO public.time_clocks (
    time_clocks_id,
    org_id,
    tenant_id,
    branch_id,
    user_id,
    clock_type,
    lat,
    lng,
    location,
    valid_gps,
    locationApproved,
    security_type,
    clocked_at,
    isDeleted,
    updatedat,
    location_id,
    location_id_legacy,
    source
)
SELECT
    p.time_clock_id,
    p.org_id,
    COALESCE(p.root_org_id, p.org_id) AS tenant_id,
    p.branch_id,
    p.user_id,
    lower(p.clock_type),
    p.lat,
    p.lng,
    p.location_geom,
    COALESCE(p.valid_gps_bool, false),
    COALESCE(p.location_approved_bool, false),
    p.security_type,
    p.clocked_at,
    p.is_deleted_bool,
    COALESCE(p.updatedat, now()),
    NULL,
    p.legacy_location_id,
    jsonb_strip_nulls(jsonb_build_object(
        'legacy_clock_id', p.legacy_clock_id,
        'legacy_location_id', p.legacy_location_id,
        'lat', p.lat,
        'lng', p.lng
    ))
FROM prepared p
ON CONFLICT (time_clocks_id) DO UPDATE
    SET org_id            = EXCLUDED.org_id,
        tenant_id         = EXCLUDED.tenant_id,
        branch_id         = EXCLUDED.branch_id,
        user_id           = EXCLUDED.user_id,
        clock_type        = EXCLUDED.clock_type,
        lat               = EXCLUDED.lat,
        lng               = EXCLUDED.lng,
        location          = EXCLUDED.location,
        valid_gps         = EXCLUDED.valid_gps,
        locationApproved  = EXCLUDED.locationApproved,
        security_type     = EXCLUDED.security_type,
        clocked_at        = EXCLUDED.clocked_at,
    isDeleted         = EXCLUDED.isDeleted,
    updatedat         = EXCLUDED.updatedat,
        location_id       = EXCLUDED.location_id,
        location_id_legacy= EXCLUDED.location_id_legacy,
        source            = EXCLUDED.source;

COMMIT;
