CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE IF NOT EXISTS entity_category_links (
    id_link BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    entity_type VARCHAR(64) NOT NULL,
    entity_id BIGINT NOT NULL,
    category_id INTEGER,
    link_role VARCHAR(32) NOT NULL DEFAULT 'ASSIGNMENT',
    payload_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    vigencia_inicio TIMESTAMPTZ,
    vigencia_fin TIMESTAMPTZ,
    prioridad INTEGER NOT NULL DEFAULT 100,
    source_mode VARCHAR(32) NOT NULL DEFAULT 'MANUAL',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_ecl_category FOREIGN KEY (category_id) REFERENCES categorias_combinables_v2(category_id),
    CONSTRAINT chk_ecl_window CHECK (vigencia_fin IS NULL OR vigencia_inicio IS NULL OR vigencia_fin >= vigencia_inicio),
    CONSTRAINT chk_ecl_entity_type CHECK (length(trim(entity_type)) > 0),
    CONSTRAINT chk_ecl_link_role CHECK (length(trim(link_role)) > 0),
    CONSTRAINT chk_ecl_source_mode CHECK (source_mode IN ('MANUAL', 'LEGACY_MAPPED', 'SYSTEM_DERIVED', 'MIGRATION_BACKFILL'))
);

ALTER TABLE entity_category_links
    ADD CONSTRAINT excl_ecl_active_overlap
    EXCLUDE USING gist (
        business_id WITH =,
        entity_type WITH =,
        entity_id WITH =,
        link_role WITH =,
        COALESCE(category_id, -1) WITH =,
        tstzrange(
            COALESCE(vigencia_inicio, '-infinity'::timestamptz),
            COALESCE(vigencia_fin, 'infinity'::timestamptz),
            '[]'
        ) WITH &&
    )
    WHERE (soft_delete IS NOT TRUE AND is_active = TRUE);

CREATE UNIQUE INDEX IF NOT EXISTS idx_ecl_unique_active
    ON entity_category_links (
        business_id,
        entity_type,
        entity_id,
        link_role,
        COALESCE(category_id, -1)
    )
    WHERE soft_delete IS NOT TRUE AND is_active = TRUE;

CREATE INDEX IF NOT EXISTS idx_ecl_lookup_entity
    ON entity_category_links (business_id, entity_type, entity_id, is_active, prioridad)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ecl_lookup_category
    ON entity_category_links (business_id, category_id, is_active)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ecl_lookup_role
    ON entity_category_links (business_id, entity_type, entity_id, link_role)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_ecl_payload_gin
    ON entity_category_links USING gin (payload_jsonb);

INSERT INTO entity_category_links (
    business_id,
    entity_type,
    entity_id,
    category_id,
    link_role,
    payload_jsonb,
    is_active,
    vigencia_inicio,
    vigencia_fin,
    prioridad,
    source_mode,
    created_at,
    created_by_login_id,
    updated_at,
    updated_by_login_id,
    soft_delete,
    soft_delete_date
)
SELECT DISTINCT
    c.business_id,
    'cliente',
    c.id_cliente::bigint,
    cats.category_id,
    'ASSIGNMENT',
    '{}'::jsonb,
    TRUE,
    NULL,
    NULL,
    100,
    'LEGACY_MAPPED',
    NOW(),
    0,
    NOW(),
    NULL,
    FALSE,
    NULL
FROM clientes c
CROSS JOIN LATERAL (
    SELECT DISTINCT value::integer AS category_id
    FROM jsonb_array_elements_text(
        CASE
            WHEN jsonb_typeof(c.categorias) = 'array' THEN c.categorias
            ELSE '[]'::jsonb
        END
    ) AS src(value)
    WHERE value ~ '^-?[0-9]+$'
      AND value::integer > 0
) AS cats
JOIN categorias_combinables_v2 cc
  ON cc.business_id = c.business_id
 AND cc.category_id = cats.category_id
 AND cc.soft_delete IS NOT TRUE
WHERE COALESCE(c.soft_delete, FALSE) = FALSE
ON CONFLICT DO NOTHING;

INSERT INTO entity_category_links (
    business_id,
    entity_type,
    entity_id,
    category_id,
    link_role,
    payload_jsonb,
    is_active,
    vigencia_inicio,
    vigencia_fin,
    prioridad,
    source_mode,
    created_at,
    created_by_login_id,
    updated_at,
    updated_by_login_id,
    soft_delete,
    soft_delete_date
)
SELECT
    c.business_id,
    'cliente',
    c.id_cliente::bigint,
    NULL,
    'RULE_CONTEXT',
    '{"bloquear_generales":true,"legacy_source":"clientes.categorias"}'::jsonb,
    TRUE,
    NULL,
    NULL,
    100,
    'LEGACY_MAPPED',
    NOW(),
    0,
    NOW(),
    NULL,
    FALSE,
    NULL
FROM clientes c
WHERE COALESCE(c.soft_delete, FALSE) = FALSE
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements_text(
          CASE
              WHEN jsonb_typeof(c.categorias) = 'array' THEN c.categorias
              ELSE '[]'::jsonb
          END
      ) AS src(value)
      WHERE value = '-1'
  )
ON CONFLICT DO NOTHING;

INSERT INTO entity_category_links (
    business_id,
    entity_type,
    entity_id,
    category_id,
    link_role,
    payload_jsonb,
    is_active,
    vigencia_inicio,
    vigencia_fin,
    prioridad,
    source_mode,
    created_at,
    created_by_login_id,
    updated_at,
    updated_by_login_id,
    soft_delete,
    soft_delete_date
)
SELECT DISTINCT
    k.business_id,
    'conocimiento',
    k.id::bigint,
    cats.category_id,
    'ASSIGNMENT',
    '{}'::jsonb,
    TRUE,
    NULL,
    NULL,
    100,
    'LEGACY_MAPPED',
    NOW(),
    0,
    NOW(),
    NULL,
    FALSE,
    NULL
FROM conocimientos_empleado_digital k
CROSS JOIN LATERAL (
    SELECT DISTINCT value::integer AS category_id
    FROM jsonb_array_elements_text(
        CASE
            WHEN jsonb_typeof(k.categorias) = 'array' THEN k.categorias
            ELSE '[]'::jsonb
        END
    ) AS src(value)
    WHERE value ~ '^-?[0-9]+$'
      AND value::integer > 0
) AS cats
JOIN categorias_combinables_v2 cc
  ON cc.business_id = k.business_id
 AND cc.category_id = cats.category_id
 AND cc.soft_delete IS NOT TRUE
WHERE COALESCE(k.soft_delete, FALSE) = FALSE
ON CONFLICT DO NOTHING;

INSERT INTO entity_category_links (
    business_id,
    entity_type,
    entity_id,
    category_id,
    link_role,
    payload_jsonb,
    is_active,
    vigencia_inicio,
    vigencia_fin,
    prioridad,
    source_mode,
    created_at,
    created_by_login_id,
    updated_at,
    updated_by_login_id,
    soft_delete,
    soft_delete_date
)
SELECT
    k.business_id,
    'conocimiento',
    k.id::bigint,
    NULL,
    'RULE_CONTEXT',
    '{"es_exclusivo":true,"legacy_source":"conocimientos_empleado_digital.categorias"}'::jsonb,
    TRUE,
    NULL,
    NULL,
    100,
    'LEGACY_MAPPED',
    NOW(),
    0,
    NOW(),
    NULL,
    FALSE,
    NULL
FROM conocimientos_empleado_digital k
WHERE COALESCE(k.soft_delete, FALSE) = FALSE
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements_text(
          CASE
              WHEN jsonb_typeof(k.categorias) = 'array' THEN k.categorias
              ELSE '[]'::jsonb
          END
      ) AS src(value)
      WHERE value = '-1'
  )
ON CONFLICT DO NOTHING;
