-- Adds canonical ipv4_address alongside legacy IP columns, ip_addresses rollup tables,
-- and backfills ipv4_address from existing data where possible.

ALTER TABLE IF EXISTS gateway_request_log
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS gateway_connection_registry_realtime
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS api_key_auth_log
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS database_audit_log
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS function_ddl_audit_log
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS api_key_ip_seen
    ADD COLUMN IF NOT EXISTS ipv4_address text;

-- Optional auth-store tables when provision-auth.sql is applied.
ALTER TABLE IF EXISTS session
    ADD COLUMN IF NOT EXISTS ipv4_address text;

ALTER TABLE IF EXISTS audit_log
    ADD COLUMN IF NOT EXISTS ipv4_address text;

CREATE TABLE IF NOT EXISTS ip_addresses (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now (),
    ipv4_address text NOT NULL,
    first_seen_at timestamptz NOT NULL DEFAULT now (),
    last_seen_at timestamptz NOT NULL DEFAULT now (),
    CONSTRAINT ip_addresses_ipv4_address_unique UNIQUE (ipv4_address)
);

CREATE INDEX IF NOT EXISTS idx_ip_addresses_last_seen ON ip_addresses (last_seen_at DESC);

CREATE TABLE IF NOT EXISTS ip_addresses_statistics (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now (),
    ip_address_id bigint NOT NULL REFERENCES ip_addresses (id) ON DELETE CASCADE,
    total_requests bigint NOT NULL DEFAULT 0,
    successful_requests bigint NOT NULL DEFAULT 0,
    failed_requests bigint NOT NULL DEFAULT 0,
    total_cached_requests bigint NOT NULL DEFAULT 0,
    last_request_at timestamptz,
    updated_at timestamptz NOT NULL DEFAULT now (),
    CONSTRAINT ip_addresses_statistics_ip_address_id_unique UNIQUE (ip_address_id)
);

CREATE INDEX IF NOT EXISTS idx_ip_addresses_statistics_updated ON ip_addresses_statistics (updated_at DESC);

-- Backfill ipv4_address (gateway_request_log) for very large tables (~10M+ rows).
-- Uses PRIMARY KEY id ranges so each statement scans a bounded slice (not repeated LIMIT scans).
-- Phase 1: legacy ipv4 only. Phase 2: X-Forwarded-For (jsonb) in smaller slices.
-- Lower chunk sizes if a single UPDATE still hits statement_timeout.
DO $$
DECLARE
    grl_min_id bigint;
    grl_max_id bigint;
    cur bigint;
    chunk_p1 bigint := 250000;
    chunk_p2 bigint := 50000;
BEGIN
    SELECT
        COALESCE(min(id), 0),
        COALESCE(max(id), 0)
    INTO grl_min_id, grl_max_id
    FROM
        gateway_request_log;

    IF grl_max_id = 0 THEN
        RETURN;
    END IF;

    cur := grl_min_id;
    WHILE cur <= grl_max_id LOOP
        UPDATE gateway_request_log g
        SET
            ipv4_address = NULLIF(NULLIF(trim(g.ipv4), ''), 'NOT_FOUND')
        WHERE
            g.id >= cur
            AND g.id < cur + chunk_p1
            AND (
                g.ipv4_address IS NULL
                OR btrim(g.ipv4_address) = ''
            )
            AND NULLIF(NULLIF(trim(g.ipv4), ''), 'NOT_FOUND') IS NOT NULL;

        cur := cur + chunk_p1;
    END LOOP;

    cur := grl_min_id;
    WHILE cur <= grl_max_id LOOP
        UPDATE gateway_request_log g
        SET
            ipv4_address = NULLIF(
                NULLIF(
                    trim(
                        split_part(
                            COALESCE(
                                g.headers ->> 'x-forwarded-for',
                                g.headers ->> 'X-Forwarded-For',
                                ''
                            ),
                            ',',
                            1
                        )
                    ),
                    ''
                ),
                'NOT_FOUND'
            )
        WHERE
            g.id >= cur
            AND g.id < cur + chunk_p2
            AND (
                g.ipv4_address IS NULL
                OR btrim(g.ipv4_address) = ''
            )
            AND NULLIF(NULLIF(trim(g.ipv4), ''), 'NOT_FOUND') IS NULL
            AND NULLIF(
                NULLIF(
                    trim(
                        split_part(
                            COALESCE(
                                g.headers ->> 'x-forwarded-for',
                                g.headers ->> 'X-Forwarded-For',
                                ''
                            ),
                            ',',
                            1
                        )
                    ),
                    ''
                ),
                'NOT_FOUND'
            ) IS NOT NULL;

        cur := cur + chunk_p2;
    END LOOP;
END
$$;

UPDATE gateway_connection_registry_realtime r
SET
    ipv4_address = COALESCE(
        NULLIF(trim(r.ipv4_address), ''),
        NULLIF(NULLIF(trim(r.ipv4), ''), 'NOT_FOUND')
    )
WHERE
    r.ipv4_address IS NULL
    AND NULLIF(NULLIF(trim(r.ipv4), ''), 'NOT_FOUND') IS NOT NULL;

UPDATE api_key_auth_log l
SET
    ipv4_address = COALESCE(NULLIF(trim(l.ipv4_address), ''), NULLIF(trim(l.remote_addr), ''))
WHERE
    l.ipv4_address IS NULL
    AND l.remote_addr IS NOT NULL
    AND trim(l.remote_addr) <> '';

UPDATE database_audit_log d
SET
    ipv4_address = COALESCE(NULLIF(trim(d.ipv4_address), ''), NULLIF(trim(d.remote_addr), ''))
WHERE
    d.ipv4_address IS NULL
    AND d.remote_addr IS NOT NULL
    AND trim(d.remote_addr) <> '';

UPDATE function_ddl_audit_log f
SET
    ipv4_address = COALESCE(NULLIF(trim(f.ipv4_address), ''), NULLIF(trim(f.remote_addr), ''))
WHERE
    f.ipv4_address IS NULL
    AND f.remote_addr IS NOT NULL
    AND trim(f.remote_addr) <> '';

UPDATE api_key_ip_seen s
SET
    ipv4_address = COALESCE(NULLIF(trim(s.ipv4_address), ''), split_part(s.addr::text, '/', 1))
WHERE
    s.ipv4_address IS NULL;

UPDATE session s
SET
    ipv4_address = COALESCE(NULLIF(trim(s.ipv4_address), ''), NULLIF(trim(s.ip_address), ''))
WHERE
    s.ipv4_address IS NULL
    AND s.ip_address IS NOT NULL;

UPDATE audit_log a
SET
    ipv4_address = COALESCE(NULLIF(trim(a.ipv4_address), ''), NULLIF(trim(a.ipv4), ''))
WHERE
    a.ipv4_address IS NULL
    AND COALESCE(NULLIF(trim(a.ipv4), ''), '') <> '';

-- Seed ip_addresses from historical gateway_request_log (chunked by id; merges on conflict).
-- Re-run merges first_seen_at / last_seen_at only; do not run twice on production if you rely
-- on exact counts in ip_addresses_statistics below (that roll-up is additive per chunk).
DO $$
DECLARE
    grl_min_id bigint;
    grl_max_id bigint;
    cur bigint;
    chunk bigint := 250000;
BEGIN
    SELECT
        COALESCE(min(id), 0),
        COALESCE(max(id), 0)
    INTO grl_min_id, grl_max_id
    FROM
        gateway_request_log;

    IF grl_max_id = 0 THEN
        RETURN;
    END IF;

    cur := grl_min_id;
    WHILE cur <= grl_max_id LOOP
        INSERT INTO ip_addresses (ipv4_address, first_seen_at, last_seen_at)
        SELECT
            v.ip_norm,
            MIN(v.created_at),
            MAX(v.created_at)
        FROM (
            SELECT
                g.created_at,
                NULLIF(
                    NULLIF(
                        trim(
                            COALESCE(
                                NULLIF(trim(g.ipv4_address), ''),
                                NULLIF(trim(g.ipv4), ''),
                                trim(
                                    split_part(
                                        COALESCE(
                                            g.headers ->> 'x-forwarded-for',
                                            g.headers ->> 'X-Forwarded-For',
                                            ''
                                        ),
                                        ',',
                                        1
                                    )
                                )
                            )
                        ),
                        ''
                    ),
                    'NOT_FOUND'
                ) AS ip_norm
            FROM
                gateway_request_log g
            WHERE
                g.id >= cur
                AND g.id < cur + chunk
        ) v
        WHERE
            v.ip_norm IS NOT NULL
        GROUP BY
            v.ip_norm
        ON CONFLICT (ipv4_address) DO UPDATE
        SET
            first_seen_at = LEAST(ip_addresses.first_seen_at, EXCLUDED.first_seen_at),
            last_seen_at = GREATEST(ip_addresses.last_seen_at, EXCLUDED.last_seen_at);

        cur := cur + chunk;
    END LOOP;
END
$$;

-- Roll-up request counts per IP (chunked). ON CONFLICT adds chunk totals; one full pass only.
DO $$
DECLARE
    grl_min_id bigint;
    grl_max_id bigint;
    cur bigint;
    chunk bigint := 250000;
BEGIN
    SELECT
        COALESCE(min(id), 0),
        COALESCE(max(id), 0)
    INTO grl_min_id, grl_max_id
    FROM
        gateway_request_log;

    IF grl_max_id = 0 THEN
        RETURN;
    END IF;

    cur := grl_min_id;
    WHILE cur <= grl_max_id LOOP
        INSERT INTO ip_addresses_statistics (
            ip_address_id,
            total_requests,
            successful_requests,
            failed_requests,
            total_cached_requests,
            last_request_at,
            updated_at
        )
        SELECT
            a.id,
            COUNT(*)::bigint,
            SUM(
                CASE
                    WHEN g.status_code >= 200
                    AND g.status_code < 400 THEN 1
                    ELSE 0
                END
            )::bigint,
            SUM(
                CASE
                    WHEN g.status_code >= 400 THEN 1
                    ELSE 0
                END
            )::bigint,
            SUM(
                CASE
                    WHEN g.cached IS TRUE THEN 1
                    ELSE 0
                END
            )::bigint,
            MAX(g.created_at),
            now()
        FROM
            gateway_request_log g
            INNER JOIN ip_addresses a ON a.ipv4_address = NULLIF(
                NULLIF(
                    trim(
                        COALESCE(
                            NULLIF(trim(g.ipv4_address), ''),
                            NULLIF(trim(g.ipv4), ''),
                            trim(
                                split_part(
                                    COALESCE(
                                        g.headers ->> 'x-forwarded-for',
                                        g.headers ->> 'X-Forwarded-For',
                                        ''
                                    ),
                                    ',',
                                    1
                                )
                            )
                        )
                    ),
                    ''
                ),
                'NOT_FOUND'
            )
        WHERE
            g.id >= cur
            AND g.id < cur + chunk
        GROUP BY
            a.id
        ON CONFLICT (ip_address_id) DO UPDATE
        SET
            total_requests = ip_addresses_statistics.total_requests + EXCLUDED.total_requests,
            successful_requests = ip_addresses_statistics.successful_requests
            + EXCLUDED.successful_requests,
            failed_requests = ip_addresses_statistics.failed_requests + EXCLUDED.failed_requests,
            total_cached_requests = ip_addresses_statistics.total_cached_requests
            + EXCLUDED.total_cached_requests,
            last_request_at = GREATEST(
                ip_addresses_statistics.last_request_at,
                EXCLUDED.last_request_at
            ),
            updated_at = now();

        cur := cur + chunk;
    END LOOP;
END
$$;
