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

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

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

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

ALTER TABLE IF EXISTS gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_authenticated boolean DEFAULT false;

ALTER TABLE IF EXISTS gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_authorized boolean DEFAULT false;

ALTER TABLE IF EXISTS gateway_request_log
ADD COLUMN IF NOT EXISTS api_key_enforced boolean DEFAULT false;

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

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'gateway_request_log'
          AND column_name = 'api_key_id'
          AND data_type <> 'text'
    ) THEN
        ALTER TABLE gateway_request_log
        ALTER COLUMN api_key_id TYPE text
        USING api_key_id::text;
    END IF;
END $$;

CREATE TABLE IF NOT EXISTS api_key_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    api_key_id bigint,
    api_key_public_id text,
    client_name text,
    method text NOT NULL,
    path text NOT NULL,
    presented_key_hash text,
    presented_key_salt text,
    required_rights jsonb,
    granted_rights jsonb,
    authenticated boolean NOT NULL DEFAULT false,
    authorized boolean NOT NULL DEFAULT false,
    enforced boolean NOT NULL DEFAULT false,
    failure_reason text,
    remote_addr text,
    user_agent text,
    time timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS api_key_auth_log_id uuid DEFAULT gen_random_uuid();

UPDATE api_key_auth_log
SET api_key_auth_log_id = gen_random_uuid()
WHERE api_key_auth_log_id IS NULL;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN api_key_auth_log_id SET DEFAULT gen_random_uuid();

DO $$
DECLARE
    api_key_id_type text;
    api_keys_has_external_id boolean;
BEGIN
    SELECT data_type INTO api_key_id_type
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'api_key_auth_log'
      AND column_name = 'api_key_id';

    IF api_key_id_type IS NULL THEN
        ALTER TABLE api_key_auth_log
        ADD COLUMN api_key_id bigint;
    ELSIF api_key_id_type <> 'bigint' THEN
        ALTER TABLE api_key_auth_log
        ADD COLUMN IF NOT EXISTS api_key_id_v2 bigint;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
              AND table_name = 'api_keys'
        ) THEN
            SELECT EXISTS (
                SELECT 1
                FROM information_schema.columns
                WHERE table_schema = 'public'
                  AND table_name = 'api_keys'
                  AND column_name = 'api_keys_id'
            ) INTO api_keys_has_external_id;

            IF api_keys_has_external_id THEN
                EXECUTE $sql$
                    UPDATE api_key_auth_log AS log
                    SET api_key_id_v2 = k.id
                    FROM api_keys AS k
                    WHERE log.api_key_id IS NOT NULL
                      AND (
                        log.api_key_id::text = k.api_keys_id::text
                        OR log.api_key_id::text = k.id::text
                      )
                $sql$;
            ELSE
                EXECUTE $sql$
                    UPDATE api_key_auth_log AS log
                    SET api_key_id_v2 = k.id
                    FROM api_keys AS k
                    WHERE log.api_key_id IS NOT NULL
                      AND log.api_key_id::text = k.id::text
                $sql$;
            END IF;
        END IF;

        ALTER TABLE api_key_auth_log
        DROP COLUMN api_key_id;

        ALTER TABLE api_key_auth_log
        RENAME COLUMN api_key_id_v2 TO api_key_id;
    END IF;
END $$;

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

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

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

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

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

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

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS required_rights jsonb;

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS granted_rights jsonb;

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS authenticated boolean DEFAULT false;

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS authorized boolean DEFAULT false;

ALTER TABLE IF EXISTS api_key_auth_log
ADD COLUMN IF NOT EXISTS enforced boolean DEFAULT false;

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

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

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

DO $$
DECLARE
    time_type text;
BEGIN
    SELECT data_type INTO time_type
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'api_key_auth_log'
      AND column_name = 'time';

    IF time_type IS NULL THEN
        ALTER TABLE api_key_auth_log
        ADD COLUMN time timestamptz NOT NULL DEFAULT now();
    ELSIF time_type <> 'timestamp with time zone' THEN
        ALTER TABLE api_key_auth_log
        ADD COLUMN IF NOT EXISTS time_v2 timestamptz;

        EXECUTE $sql$
            UPDATE api_key_auth_log
            SET time_v2 = CASE
                WHEN time IS NULL THEN COALESCE(created_at, now())
                ELSE to_timestamp(time::text::double precision)
            END
        $sql$;

        UPDATE api_key_auth_log
        SET time_v2 = COALESCE(time_v2, created_at, now())
        WHERE time_v2 IS NULL;

        ALTER TABLE api_key_auth_log
        DROP COLUMN time;

        ALTER TABLE api_key_auth_log
        RENAME COLUMN time_v2 TO time;
    END IF;
END $$;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN method SET NOT NULL;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN path SET NOT NULL;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN authenticated SET DEFAULT false;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN authorized SET DEFAULT false;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN enforced SET DEFAULT false;

ALTER TABLE IF EXISTS api_key_auth_log
ALTER COLUMN time SET DEFAULT now();

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_name = 'api_keys'
    ) AND NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'api_key_auth_log_api_key_id_fkey'
    ) THEN
        ALTER TABLE api_key_auth_log
        ADD CONSTRAINT api_key_auth_log_api_key_id_fkey
        FOREIGN KEY (api_key_id)
        REFERENCES api_keys(id)
        ON DELETE SET NULL;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_api_key_id
ON api_key_auth_log(api_key_id);

CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_client_name
ON api_key_auth_log(client_name);

CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_time
ON api_key_auth_log(time);
