//! Normalized catalogs for exact-first registration and optimization plans.

pgrx::extension_sql!(
    r#"
CREATE TABLE pgcontext._exact_first_registrations (
    exact_first_registration_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    collection_id bigint NOT NULL
        REFERENCES pgcontext._collections(collection_id) ON DELETE CASCADE,
    registration_revision bigint NOT NULL DEFAULT 1 CHECK (registration_revision > 0),
    registration_system_identifier bigint NOT NULL,
    registration_database_oid oid NOT NULL,
    source_table_oid oid NOT NULL,
    source_schema_name text NOT NULL,
    source_table_name text NOT NULL,
    source_key_column_name text NOT NULL,
    source_key_attnum int2 NOT NULL CHECK (source_key_attnum > 0),
    source_key_type_oid oid NOT NULL,
    source_key_typmod int4 NOT NULL,
    source_key_collation_oid bigint NOT NULL CHECK (source_key_collation_oid BETWEEN 0 AND 4294967295),
    source_key_index_oid oid NOT NULL,
    specification_version text NOT NULL CHECK (specification_version = 'exact_first_registration_v1'),
    specification_sha256 bytea NOT NULL CHECK (pg_catalog.octet_length(specification_sha256) = 32),
    readiness_state text NOT NULL DEFAULT 'exact_only'
        CHECK (readiness_state IN ('exact_only','building','indexed','stale','degraded')),
    readiness_reason text NOT NULL DEFAULT 'current_exact_path'
        CHECK (readiness_reason IN (
            'current_exact_path','build_active','optimization_ready',
            'source_relation_changed','source_key_changed','source_column_changed',
            'configuration_changed','exact_path_unavailable','build_failed',
            'build_cancelled','optimization_corrupt','resource_limit','recall_rejected'
        )),
    current_plan_revision bigint CHECK (current_plan_revision IS NULL OR current_plan_revision > 0),
    created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    UNIQUE (collection_id)
);

CREATE TABLE pgcontext._exact_first_columns (
    exact_first_registration_id bigint NOT NULL
        REFERENCES pgcontext._exact_first_registrations(exact_first_registration_id)
        ON DELETE CASCADE,
    binding_ordinal int2 NOT NULL CHECK (binding_ordinal BETWEEN 0 AND 255),
    binding_name text NOT NULL,
    binding_kind text NOT NULL CHECK (binding_kind IN (
        'dense','half','sparse','int8','uint8','bit','vector_array',
        'lexical','tsvector','fuzzy','filter','payload'
    )),
    column_name text NOT NULL,
    column_attnum int2 NOT NULL CHECK (column_attnum > 0),
    column_type_oid oid NOT NULL,
    column_typmod int4 NOT NULL,
    column_collation_oid bigint NOT NULL CHECK (column_collation_oid BETWEEN 0 AND 4294967295),
    dimensions int4 CHECK (dimensions IS NULL OR dimensions > 0),
    metric text CHECK (metric IS NULL OR metric IN (
        'l2','inner_product','cosine','l1','hamming','jaccard'
    )),
    text_configuration_oid oid,
    normalization text,
    configuration_revision bigint NOT NULL DEFAULT 1 CHECK (configuration_revision > 0),
    PRIMARY KEY (exact_first_registration_id, binding_ordinal),
    UNIQUE (exact_first_registration_id, binding_name)
);

CREATE TABLE pgcontext._exact_first_plans (
    exact_first_plan_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exact_first_registration_id bigint NOT NULL
        REFERENCES pgcontext._exact_first_registrations(exact_first_registration_id)
        ON DELETE CASCADE,
    plan_revision bigint NOT NULL CHECK (plan_revision > 0),
    registration_revision bigint NOT NULL CHECK (registration_revision > 0),
    plan_sha256 bytea NOT NULL CHECK (pg_catalog.octet_length(plan_sha256) = 32),
    apply_policy text NOT NULL CHECK (apply_policy IN (
        'recommend_only','exact_only','enqueue','apply_foreground'
    )),
    recommendation text NOT NULL,
    evidence jsonb NOT NULL CHECK (pg_catalog.jsonb_typeof(evidence) = 'object'),
    generated_ddl text,
    created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    UNIQUE (exact_first_registration_id, plan_revision),
    UNIQUE (exact_first_registration_id, plan_sha256)
);

CREATE TABLE pgcontext._exact_first_plan_jobs (
    exact_first_plan_id bigint PRIMARY KEY
        REFERENCES pgcontext._exact_first_plans(exact_first_plan_id) ON DELETE CASCADE,
    build_job_id bigint NOT NULL UNIQUE
        REFERENCES pgcontext._build_jobs(build_job_id) ON DELETE CASCADE,
    status text NOT NULL DEFAULT 'queued' CHECK (status IN (
        'queued','building','validating','cancel_requested','cancelled','failed'
    )),
    attempt int4 NOT NULL DEFAULT 0 CHECK (attempt BETWEEN 0 AND 3),
    lease_token bigint,
    lease_expires_at timestamptz,
    worker_id text CHECK (worker_id IS NULL OR pg_catalog.octet_length(worker_id) BETWEEN 1 AND 128),
    error_code text CHECK (error_code IS NULL OR pg_catalog.octet_length(error_code) BETWEEN 1 AND 64),
    updated_at timestamptz NOT NULL DEFAULT pg_catalog.now()
);

CREATE TABLE pgcontext._exact_first_targets (
    exact_first_target_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exact_first_registration_id bigint NOT NULL
        REFERENCES pgcontext._exact_first_registrations(exact_first_registration_id)
        ON DELETE CASCADE,
    exact_first_plan_id bigint NOT NULL
        REFERENCES pgcontext._exact_first_plans(exact_first_plan_id) ON DELETE CASCADE,
    index_oid oid,
    index_fingerprint_sha256 bytea NOT NULL
        CHECK (pg_catalog.octet_length(index_fingerprint_sha256) = 32),
    lifecycle_state text NOT NULL CHECK (lifecycle_state IN (
        'shadow','current','retiring','retired','failed'
    )),
    structurally_validated boolean NOT NULL DEFAULT false,
    recall_bps int4 CHECK (recall_bps BETWEEN 0 AND 10000),
    published_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT pg_catalog.now()
);

CREATE UNIQUE INDEX exact_first_targets_one_current
    ON pgcontext._exact_first_targets (exact_first_registration_id)
    WHERE lifecycle_state = 'current';

CREATE TABLE pgcontext._exact_first_invalid_samples (
    exact_first_registration_id bigint NOT NULL
        REFERENCES pgcontext._exact_first_registrations(exact_first_registration_id)
        ON DELETE CASCADE,
    sample_ordinal int2 NOT NULL CHECK (sample_ordinal BETWEEN 0 AND 63),
    binding_ordinal int2,
    reason_code text NOT NULL CHECK (pg_catalog.octet_length(reason_code) BETWEEN 1 AND 64),
    source_key_sha256 bytea CHECK (pg_catalog.octet_length(source_key_sha256) = 32),
    observed_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
    PRIMARY KEY (exact_first_registration_id, sample_ordinal)
);

CREATE VIEW pgcontext._visible_exact_first_registrations
WITH (security_barrier = true) AS
SELECT registrations.*
  FROM pgcontext._exact_first_registrations AS registrations
  JOIN pgcontext._collections AS collections USING (collection_id)
 WHERE pg_catalog.pg_has_role(SESSION_USER, collections.owner_role, 'MEMBER');

CREATE VIEW pgcontext._visible_exact_first_columns
WITH (security_barrier = true) AS
SELECT columns.*
  FROM pgcontext._exact_first_columns AS columns
  JOIN pgcontext._exact_first_registrations AS registrations
    USING (exact_first_registration_id)
  JOIN pgcontext._collections AS collections USING (collection_id)
 WHERE pg_catalog.pg_has_role(SESSION_USER, collections.owner_role, 'MEMBER');

CREATE VIEW pgcontext._visible_exact_first_plans
WITH (security_barrier = true) AS
SELECT plans.*,
       CASE WHEN EXISTS (
                SELECT 1
                  FROM pgcontext._exact_first_targets AS targets
                  JOIN pg_catalog.pg_index AS target_index
                    ON target_index.indexrelid = targets.index_oid
                 WHERE targets.exact_first_plan_id = plans.exact_first_plan_id
                   AND targets.lifecycle_state = 'current'
                   AND targets.structurally_validated
                   AND target_index.indisvalid
                   AND target_index.indisready
                   AND target_index.indislive
            ) THEN 'published'
            ELSE COALESCE(plan_jobs.status, 'frozen') END AS status,
       plan_jobs.build_job_id, COALESCE(plan_jobs.attempt, 0) AS attempt,
       plan_jobs.lease_token, plan_jobs.lease_expires_at,
       plan_jobs.worker_id, plan_jobs.error_code, plan_jobs.updated_at
  FROM pgcontext._exact_first_plans AS plans
  LEFT JOIN pgcontext._exact_first_plan_jobs AS plan_jobs USING (exact_first_plan_id)
  JOIN pgcontext._exact_first_registrations AS registrations
    USING (exact_first_registration_id)
  JOIN pgcontext._collections AS collections USING (collection_id)
 WHERE pg_catalog.pg_has_role(SESSION_USER, collections.owner_role, 'MEMBER');

CREATE VIEW pgcontext._visible_exact_first_targets
WITH (security_barrier = true) AS
SELECT targets.*
  FROM pgcontext._exact_first_targets AS targets
  JOIN pgcontext._exact_first_registrations AS registrations
    USING (exact_first_registration_id)
  JOIN pgcontext._collections AS collections USING (collection_id)
 WHERE pg_catalog.pg_has_role(SESSION_USER, collections.owner_role, 'MEMBER');

CREATE VIEW pgcontext._visible_exact_first_invalid_samples
WITH (security_barrier = true) AS
SELECT samples.exact_first_registration_id,
       samples.sample_ordinal,
       samples.binding_ordinal,
       samples.reason_code,
       samples.observed_at
  FROM pgcontext._exact_first_invalid_samples AS samples
  JOIN pgcontext._exact_first_registrations AS registrations
    USING (exact_first_registration_id)
  JOIN pgcontext._collections AS collections USING (collection_id)
 WHERE pg_catalog.pg_has_role(SESSION_USER, collections.owner_role, 'MEMBER');

REVOKE ALL ON pgcontext._exact_first_registrations FROM PUBLIC;
REVOKE ALL ON pgcontext._exact_first_columns FROM PUBLIC;
REVOKE ALL ON pgcontext._exact_first_plans FROM PUBLIC;
REVOKE ALL ON pgcontext._exact_first_plan_jobs FROM PUBLIC;
REVOKE ALL ON pgcontext._exact_first_targets FROM PUBLIC;
REVOKE ALL ON pgcontext._exact_first_invalid_samples FROM PUBLIC;
GRANT SELECT ON pgcontext._visible_exact_first_registrations TO PUBLIC;
GRANT SELECT ON pgcontext._visible_exact_first_columns TO PUBLIC;
GRANT SELECT ON pgcontext._visible_exact_first_plans TO PUBLIC;
GRANT SELECT ON pgcontext._visible_exact_first_targets TO PUBLIC;
GRANT SELECT ON pgcontext._visible_exact_first_invalid_samples TO PUBLIC;

SELECT pg_catalog.pg_extension_config_dump('pgcontext._exact_first_registrations', '');
SELECT pg_catalog.pg_extension_config_dump(
    'pgcontext._exact_first_registrations_exact_first_registration_id_seq', '');
SELECT pg_catalog.pg_extension_config_dump('pgcontext._exact_first_columns', '');
SELECT pg_catalog.pg_extension_config_dump('pgcontext._exact_first_plans', '');
SELECT pg_catalog.pg_extension_config_dump(
    'pgcontext._exact_first_plans_exact_first_plan_id_seq', '');
"#,
    name = "create_exact_first_catalog_tables",
    requires = ["create_catalog_tables"]
);
