-- Active: 1782426846920@@thomas.proxy.rlwy.net@19878@railway
-- Historical migration snapshot for Athena-managed S3 catalog and credential store.
-- Canonical source of truth now lives in sql/files/athena-files.sql.
-- Legacy bootstrap/backfill logic now lives in sql/files/storage-files-legacy-compat.sql.
-- Safe to re-run.

CREATE SCHEMA IF NOT EXISTS athena;

CREATE TABLE IF NOT EXISTS athena.s3 (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    description text NOT NULL DEFAULT '',
    endpoint text NOT NULL,
    region text NOT NULL,
    bucket text,
    provider text NOT NULL DEFAULT 's3',
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT athena_s3_name_nonempty CHECK (btrim(name) <> ''),
    CONSTRAINT athena_s3_endpoint_nonempty CHECK (btrim(endpoint) <> ''),
    CONSTRAINT athena_s3_region_nonempty CHECK (btrim(region) <> ''),
    CONSTRAINT athena_s3_provider_nonempty CHECK (btrim(provider) <> ''),
    CONSTRAINT athena_s3_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_name_unique
    ON athena.s3 (lower(name))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_active_created_idx
    ON athena.s3 (is_active, created_at DESC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_bucket_created_idx
    ON athena.s3 (bucket, created_at DESC)
    WHERE bucket IS NOT NULL AND deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.s3_credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_id uuid NOT NULL REFERENCES athena.s3(id) ON DELETE CASCADE,
    description text NOT NULL DEFAULT '',
    access_key_id text NOT NULL,
    secret_key text NOT NULL,
    session_token text,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    rotated_at timestamptz,
    expires_at timestamptz,
    CONSTRAINT athena_s3_credentials_access_key_nonempty CHECK (btrim(access_key_id) <> ''),
    CONSTRAINT athena_s3_credentials_secret_nonempty CHECK (btrim(secret_key) <> ''),
    CONSTRAINT athena_s3_credentials_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_credentials_one_active_per_s3_idx
    ON athena.s3_credentials (s3_id)
    WHERE is_active = true;

CREATE INDEX IF NOT EXISTS athena_s3_credentials_active_created_idx
    ON athena.s3_credentials (is_active, created_at DESC);

CREATE INDEX IF NOT EXISTS athena_s3_credentials_s3_created_idx
    ON athena.s3_credentials (s3_id, created_at DESC);

DO $$
BEGIN
    IF to_regclass('public.s3_credentials') IS NOT NULL THEN
        INSERT INTO athena.s3 (
            id,
            name,
            description,
            endpoint,
            region,
            bucket,
            provider,
            is_active,
            metadata,
            created_at,
            updated_at
        )
        SELECT
            legacy.id,
            CONCAT(
                COALESCE(
                    NULLIF(
                        regexp_replace(lower(btrim(legacy.description)), '[^a-z0-9]+', '-', 'g'),
                        ''
                    ),
                    'legacy-s3'
                ),
                '-',
                substr(legacy.id::text, 1, 8)
            ),
            COALESCE(legacy.description, ''),
            legacy.endpoint,
            legacy.region,
            legacy.bucket,
            's3',
            legacy.is_active,
            jsonb_build_object(
                'migratedFrom', 'public.s3_credentials',
                'legacyId', legacy.id
            ),
            legacy.created_at,
            legacy.updated_at
        FROM public.s3_credentials AS legacy
        ON CONFLICT (id) DO UPDATE
        SET
            description = EXCLUDED.description,
            endpoint = EXCLUDED.endpoint,
            region = EXCLUDED.region,
            bucket = EXCLUDED.bucket,
            is_active = EXCLUDED.is_active,
            metadata = athena.s3.metadata || EXCLUDED.metadata,
            updated_at = EXCLUDED.updated_at;

        INSERT INTO athena.s3_credentials (
            id,
            s3_id,
            description,
            access_key_id,
            secret_key,
            is_active,
            metadata,
            created_at,
            updated_at
        )
        SELECT
            legacy.id,
            legacy.id,
            COALESCE(legacy.description, ''),
            legacy.access_key_id,
            legacy.secret_key,
            legacy.is_active,
            jsonb_build_object(
                'migratedFrom', 'public.s3_credentials',
                'legacyId', legacy.id
            ),
            legacy.created_at,
            legacy.updated_at
        FROM public.s3_credentials AS legacy
        ON CONFLICT (id) DO UPDATE
        SET
            description = EXCLUDED.description,
            access_key_id = EXCLUDED.access_key_id,
            secret_key = EXCLUDED.secret_key,
            is_active = EXCLUDED.is_active,
            metadata = athena.s3_credentials.metadata || EXCLUDED.metadata,
            updated_at = EXCLUDED.updated_at;
    END IF;
END $$;
