
CREATE SCHEMA IF NOT EXISTS catalog;
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE IF NOT EXISTS catalog.inventario (
    LIKE public.inventario INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS catalog.categorias_combinables_v2 (
    LIKE public.categorias_combinables_v2 INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS catalog.category_rules_v2 (
    LIKE public.category_rules_v2 INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS catalog.entity_category_links (
    LIKE public.entity_category_links INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS catalog.inventario_variantes_categoria (
    LIKE public.inventario_variantes_categoria INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS catalog.inventario_variantes_resolucion_log (
    LIKE public.inventario_variantes_resolucion_log INCLUDING ALL
);

-- Reset data in deterministic order (children first) for idempotency.
TRUNCATE TABLE
    catalog.inventario_variantes_categoria,
    catalog.entity_category_links,
    catalog.category_rules_v2,
    catalog.inventario_variantes_resolucion_log,
    catalog.categorias_combinables_v2,
    catalog.inventario
;

INSERT INTO catalog.inventario
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.inventario;

INSERT INTO catalog.categorias_combinables_v2
SELECT
    *
FROM public.categorias_combinables_v2;

INSERT INTO catalog.category_rules_v2
SELECT
    *
FROM public.category_rules_v2;

INSERT INTO catalog.entity_category_links
SELECT
    *
FROM public.entity_category_links;

INSERT INTO catalog.inventario_variantes_categoria
SELECT
    *
FROM public.inventario_variantes_categoria;

INSERT INTO catalog.inventario_variantes_resolucion_log
SELECT
    *
FROM public.inventario_variantes_resolucion_log;

-- Reinstate IDENTITY/SEQUENCE strategy in catalog.
SELECT setval(
    pg_get_serial_sequence('catalog.inventario', 'id_item'),
    COALESCE((SELECT MAX(id_item) FROM catalog.inventario), 1),
    COALESCE((SELECT MAX(id_item) FROM catalog.inventario), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS catalog.categorias_combinables_v2_category_id_seq
    AS integer
    OWNED BY catalog.categorias_combinables_v2.category_id;

ALTER TABLE catalog.categorias_combinables_v2
    ALTER COLUMN category_id
    SET DEFAULT nextval('catalog.categorias_combinables_v2_category_id_seq'::regclass);

SELECT setval(
    'catalog.categorias_combinables_v2_category_id_seq',
    COALESCE((SELECT MAX(category_id) FROM catalog.categorias_combinables_v2), 1),
    COALESCE((SELECT MAX(category_id) FROM catalog.categorias_combinables_v2), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS catalog.category_rules_v2_rule_id_seq
    AS integer
    OWNED BY catalog.category_rules_v2.rule_id;

ALTER TABLE catalog.category_rules_v2
    ALTER COLUMN rule_id
    SET DEFAULT nextval('catalog.category_rules_v2_rule_id_seq'::regclass);

SELECT setval(
    'catalog.category_rules_v2_rule_id_seq',
    COALESCE((SELECT MAX(rule_id) FROM catalog.category_rules_v2), 1),
    COALESCE((SELECT MAX(rule_id) FROM catalog.category_rules_v2), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS catalog.entity_category_links_id_link_seq
    AS bigint
    OWNED BY catalog.entity_category_links.id_link;

ALTER TABLE catalog.entity_category_links
    ALTER COLUMN id_link
    SET DEFAULT nextval('catalog.entity_category_links_id_link_seq'::regclass);

SELECT setval(
    'catalog.entity_category_links_id_link_seq',
    COALESCE((SELECT MAX(id_link) FROM catalog.entity_category_links), 1),
    COALESCE((SELECT MAX(id_link) FROM catalog.entity_category_links), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS catalog.inventario_variantes_categoria_id_variante_seq
    AS bigint
    OWNED BY catalog.inventario_variantes_categoria.id_variante;

ALTER TABLE catalog.inventario_variantes_categoria
    ALTER COLUMN id_variante
    SET DEFAULT nextval('catalog.inventario_variantes_categoria_id_variante_seq'::regclass);

SELECT setval(
    'catalog.inventario_variantes_categoria_id_variante_seq',
    COALESCE((SELECT MAX(id_variante) FROM catalog.inventario_variantes_categoria), 1),
    COALESCE((SELECT MAX(id_variante) FROM catalog.inventario_variantes_categoria), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS catalog.inventario_variantes_resolucion_log_id_log_seq
    AS bigint
    OWNED BY catalog.inventario_variantes_resolucion_log.id_log;

ALTER TABLE catalog.inventario_variantes_resolucion_log
    ALTER COLUMN id_log
    SET DEFAULT nextval('catalog.inventario_variantes_resolucion_log_id_log_seq'::regclass);

SELECT setval(
    'catalog.inventario_variantes_resolucion_log_id_log_seq',
    COALESCE((SELECT MAX(id_log) FROM catalog.inventario_variantes_resolucion_log), 1),
    COALESCE((SELECT MAX(id_log) FROM catalog.inventario_variantes_resolucion_log), 0) > 0
);

-- Rebind FK constraints to catalog domain (safe idempotent reset + recreate).
ALTER TABLE catalog.category_rules_v2
    DROP CONSTRAINT IF EXISTS category_rules_v2_source_category_id_fkey;

ALTER TABLE catalog.category_rules_v2
    ADD CONSTRAINT category_rules_v2_source_category_id_fkey
    FOREIGN KEY (source_category_id)
    REFERENCES catalog.categorias_combinables_v2 (category_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE catalog.category_rules_v2
    DROP CONSTRAINT IF EXISTS category_rules_v2_target_category_id_fkey;

ALTER TABLE catalog.category_rules_v2
    ADD CONSTRAINT category_rules_v2_target_category_id_fkey
    FOREIGN KEY (target_category_id)
    REFERENCES catalog.categorias_combinables_v2 (category_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE catalog.entity_category_links
    DROP CONSTRAINT IF EXISTS fk_ecl_category;

ALTER TABLE catalog.entity_category_links
    ADD CONSTRAINT fk_ecl_category
    FOREIGN KEY (category_id)
    REFERENCES catalog.categorias_combinables_v2 (category_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE catalog.inventario_variantes_categoria
    DROP CONSTRAINT IF EXISTS fk_ivc_categoria;

ALTER TABLE catalog.inventario_variantes_categoria
    ADD CONSTRAINT fk_ivc_categoria
    FOREIGN KEY (category_id)
    REFERENCES catalog.categorias_combinables_v2 (category_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE catalog.inventario_variantes_categoria
    DROP CONSTRAINT IF EXISTS fk_ivc_inventario;

ALTER TABLE catalog.inventario_variantes_categoria
    ADD CONSTRAINT fk_ivc_inventario
    FOREIGN KEY (id_item)
    REFERENCES catalog.inventario (id_item)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ANALYZE catalog.inventario;
ANALYZE catalog.categorias_combinables_v2;
ANALYZE catalog.category_rules_v2;
ANALYZE catalog.entity_category_links;
ANALYZE catalog.inventario_variantes_categoria;
ANALYZE catalog.inventario_variantes_resolucion_log;
