-- =============================================================================
-- PostgreSQL 16+ Planner Regression Reproducer
-- =============================================================================
-- 
-- DESCRIPTION:
--   This script creates a synthetic dataset that demonstrates a join ordering
--   regression introduced in PostgreSQL 16. The same query runs in ~70ms on
--   PG 15 but ~1400ms on PG 16/17/18 due to the planner choosing a
--   significantly worse join order.
--
-- TABLES:
--   product_options    (~1.39M rows) - master options catalogue
--   pricelist_options  (~11.8M rows) - options within price lists
--   option_rules       (~628K rows)  - rules linking primary→secondary options
--   tax_codes          (88 rows)     - VAT/tax code lookup
--
-- USAGE:
--   psql -f reproducer_pg16_join_regression.sql
--   (Takes a few minutes to generate ~14M rows)
--
-- EXPECTED BEHAVIOUR:
--   PG 15:  Execution time ~70ms   (tblaunoptionals0011 scanned 216 times)
--   PG 16+: Execution time ~1400ms (tblaunoptionals0011 scanned 17,325 times)
--
-- The regression is caused by the planner choosing a different join order.
-- Setting join_collapse_limit = 1 restores PG 15 performance on PG 16+.
-- =============================================================================

-- Clean up if re-running

-- =============================================================================
-- 1. SCHEMA DEFINITION
-- =============================================================================

CREATE TABLE tax_codes (
    tax_id          integer       NOT NULL,
    description     varchar(100),
    tax_rate        numeric(5,2),
    created_at      timestamp     DEFAULT now(),
    created_by_company integer    DEFAULT 1,
    created_by_user integer       DEFAULT 1,
    modified_at     timestamp     DEFAULT now(),
    modified_by_user integer      DEFAULT 1,
    operation_type  varchar(10),
    row_version     bigint        DEFAULT 1,
    long_description varchar(200),
    tax_base        numeric(5,2),
    flag_1          boolean       DEFAULT false,
    flag_2          boolean       DEFAULT false,
    flag_3          boolean       DEFAULT false,
    flag_4          boolean       DEFAULT false,
    flag_5          boolean       DEFAULT false,
    flag_6          boolean       DEFAULT false,
    flag_7          boolean       DEFAULT false,
    flag_8          boolean       DEFAULT false,
    flag_9          boolean       DEFAULT false,
    CONSTRAINT pk_tax_codes PRIMARY KEY (tax_id)
);

CREATE TABLE product_options (
    brand_id        integer       NOT NULL,
    line_code       varchar(20)   NOT NULL,
    model_year      varchar(10)   NOT NULL,
    model_code      varchar(20)   NOT NULL,
    version_code    varchar(30)   NOT NULL,
    option_type     varchar(10)   NOT NULL,
    option_code     varchar(20)   NOT NULL,
    created_at      timestamp     DEFAULT now(),
    created_by_company integer    DEFAULT 1,
    created_by_user integer       DEFAULT 1,
    modified_at     timestamp     DEFAULT now(),
    modified_by_user integer      DEFAULT 1,
    prod_end_date   date,
    row_version     bigint        DEFAULT 1,
    is_contract     boolean       DEFAULT false,
    note_id         integer,
    description     varchar(200),
    CONSTRAINT pk_product_options PRIMARY KEY (brand_id, line_code, model_year, model_code, version_code, option_type, option_code)
);

CREATE TABLE pricelist_options (
    pricelist_id    integer       NOT NULL,
    brand_id        integer       NOT NULL,
    line_code       varchar(20)   NOT NULL,
    model_year      varchar(10)   NOT NULL,
    model_code      varchar(20)   NOT NULL,
    version_code    varchar(30)   NOT NULL,
    option_type     varchar(10)   NOT NULL,
    option_code     varchar(20)   NOT NULL,
    price_incl      numeric(12,2),
    price_excl      numeric(12,2),
    dealer_net_excl numeric(12,2),
    tax_id          integer,
    modified_at     timestamp     DEFAULT now(),
    created_by_company integer    DEFAULT 1,
    created_by_user integer       DEFAULT 1,
    created_at      timestamp     DEFAULT now(),
    modified_by_user integer      DEFAULT 1,
    row_version     bigint        DEFAULT 1,
    is_visible      boolean       DEFAULT true,
    sort_order      integer,
    CONSTRAINT pk_pricelist_options PRIMARY KEY (pricelist_id, brand_id, line_code, model_year, model_code, version_code, option_type, option_code)
);

CREATE TABLE option_rules (
    brand_id        integer       NOT NULL,
    line_code       varchar(20)   NOT NULL,
    model_year      varchar(10)   NOT NULL,
    model_code      varchar(20)   NOT NULL,
    version_code    varchar(30)   NOT NULL,
    option_type     varchar(10)   NOT NULL,
    option_code     varchar(20)   NOT NULL,
    sec_option_type varchar(10),
    constraint_type varchar(10),
    created_by_user integer       DEFAULT 1,
    sec_option_code varchar(20),
    created_at      timestamp     DEFAULT now(),
    created_by_company integer    DEFAULT 1,
    modified_at     timestamp     DEFAULT now(),
    modified_by_user integer      DEFAULT 1,
    row_version     bigint        DEFAULT 1,
    seq_number      integer       NOT NULL,
    CONSTRAINT pk_option_rules PRIMARY KEY (brand_id, line_code, model_year, model_code, version_code, option_type, option_code, seq_number)
);

-- =============================================================================
-- 2. INDEX CREATION (matches production)
-- =============================================================================

-- Composite index on pricelist_options used for filtered lookups
-- (brand_id, line_code, model_year, model_code, version_code, pricelist_id)
CREATE INDEX ix_pricelist_options_1 ON pricelist_options (
    brand_id, line_code, model_year, model_code, version_code, pricelist_id
);

-- =============================================================================
-- 3. DATA GENERATION
-- =============================================================================

-- Key dimensions (derived from production statistics):
--   18  distinct brand_id values
--   104 distinct line_code values (not all brands have all lines)
--   18  distinct model_year values
--   161 distinct model_code values
--   5978 distinct version_code values
--   4   distinct option_type values
--   ~14191 distinct option_code values (~2.4 per version)
--   ~12 pricelists per version
--   88  tax codes
--
-- Target row counts:
--   product_options:   ~1,390,585  (~232 options per version)
--   pricelist_options: ~11,836,440 (~165 pricelist entries per version per pricelist)
--   option_rules:      ~627,690   (~105 rules per version)
--   tax_codes:         88

\echo '=== Generating tax_codes (88 rows) ==='

INSERT INTO tax_codes (tax_id, description, tax_rate, operation_type, long_description, tax_base)
SELECT
    g AS tax_id,
    'Tax code ' || g AS description,
    CASE
        WHEN g <= 20 THEN round((random() * 25)::numeric, 2)
        ELSE 0
    END AS tax_rate,
    CASE (g % 3)
        WHEN 0 THEN 'STD'
        WHEN 1 THEN 'RED'
        ELSE 'EXM'
    END AS operation_type,
    'Extended description for tax code ' || g AS long_description,
    100.00 AS tax_base
FROM generate_series(1, 88) g;

\echo '=== Generating version dimension (5978 versions) ==='

-- Create a temporary table with all version combinations
-- We use a hierarchical structure: brand → line → model_year → model → version
-- to mimic the correlated nature of the real data
CREATE TEMPORARY TABLE _versions (
    version_id  serial PRIMARY KEY,
    brand_id    integer,
    line_code   varchar(20),
    model_year  varchar(10),
    model_code  varchar(20),
    version_code varchar(30)
);

-- Generate 5978 versions with realistic hierarchical distribution
-- ~18 brands, each with varying numbers of lines/models/versions
INSERT INTO _versions (brand_id, line_code, model_year, model_code, version_code)
SELECT
    brand_id,
    line_code,
    model_year,
    model_code,
    version_code
FROM (
    SELECT
        ((g - 1) % 18) + 1 AS brand_id,
        'LN' || lpad((((g - 1) / 18) % 104 + 1)::text, 3, '0') AS line_code,
        'MY' || lpad((((g - 1) / (18 * 104)) % 18 + 1)::text, 2, '0') AS model_year,
        'MD' || lpad((((g - 1) / (18 * 6)) % 161 + 1)::text, 3, '0') AS model_code,
        'VER-' || lpad(g::text, 5, '0') AS version_code,
        g
    FROM generate_series(1, 5978) g
) sub;

-- Ensure our "target" version exists with known identifiers
-- This is the version that will be queried in the reproducer
-- (maps to brand_id=10, line_code='ABC', model_year='YR',
--  model_code='ABC', version_code='VER-001')
UPDATE _versions
SET brand_id = 10,
    line_code = 'ABC',
    model_year = 'YR',
    model_code = 'ABC',
    version_code = 'VER-001'
WHERE version_id = 1;

\echo '=== Generating product_options (~1.39M rows) ==='
\echo '    This may take a minute...'

-- ~232 options per version, using 4 option types with varying option codes
-- Target version (version_id=1) gets exactly 345 options to match production
INSERT INTO product_options (brand_id, line_code, model_year, model_code, version_code,
                             option_type, option_code, description, note_id)
SELECT
    v.brand_id,
    v.line_code,
    v.model_year,
    v.model_code,
    v.version_code,
    opt_type,
    opt_code,
    'Option ' || opt_type || '-' || opt_code AS description,
    (random() * 100)::integer AS note_id
FROM _versions v
CROSS JOIN LATERAL (
    SELECT
        'T' || ((g - 1) % 4 + 1) AS opt_type,
        'OPT' || lpad(g::text, 5, '0') AS opt_code
    FROM generate_series(1,
        CASE WHEN v.version_id = 1 THEN 345 ELSE 232 END
    ) g
) opts(opt_type, opt_code);

\echo '=== Generating option_rules (~628K rows) ==='
\echo '    This may take a minute...'

-- ~105 rules per version, linking primary options to secondary options
-- Each rule points from one option (type+code) to another (sec_type+sec_code)
-- Target version gets exactly 105 rules to match production
INSERT INTO option_rules (brand_id, line_code, model_year, model_code, version_code,
                          option_type, option_code, sec_option_type, sec_option_code,
                          constraint_type, seq_number)
SELECT
    v.brand_id,
    v.line_code,
    v.model_year,
    v.model_code,
    v.version_code,
    rule.opt_type,
    rule.opt_code,
    rule.sec_opt_type,
    rule.sec_opt_code,
    CASE (g % 3) WHEN 0 THEN 'REQ' WHEN 1 THEN 'EXC' ELSE 'INC' END,
    g AS seq_number
FROM _versions v
CROSS JOIN LATERAL (
    SELECT
        g,
        'T' || ((g - 1) % 4 + 1) AS opt_type,
        'OPT' || lpad(((g - 1) % 80 + 1)::text, 5, '0') AS opt_code,
        -- Secondary option references a different option from the same version
        'T' || (((g - 1) + 2) % 4 + 1) AS sec_opt_type,
        'OPT' || lpad(((g - 1) % 80 + 81)::text, 5, '0') AS sec_opt_code
    FROM generate_series(1,
        CASE WHEN v.version_id = 1 THEN 105 ELSE 105 END
    ) g
) rule;

\echo '=== Generating pricelist_options (~11.8M rows) ==='
\echo '    This will take several minutes...'

-- ~12 pricelists per version, each with ~165 entries
-- Target version + pricelist_id=100 gets exactly 165 rows
-- We assign tax_id from the 88 available tax codes
INSERT INTO pricelist_options (pricelist_id, brand_id, line_code, model_year, model_code,
                               version_code, option_type, option_code,
                               price_incl, price_excl, dealer_net_excl,
                               tax_id, is_visible, sort_order)
SELECT
    pl.pricelist_id,
    v.brand_id,
    v.line_code,
    v.model_year,
    v.model_code,
    v.version_code,
    pl.opt_type,
    pl.opt_code,
    round((random() * 10000)::numeric, 2) AS price_incl,
    round((random() * 9000)::numeric, 2) AS price_excl,
    round((random() * 7000)::numeric, 2) AS dealer_net_excl,
    (g % 88) + 1 AS tax_id,
    true AS is_visible,
    g AS sort_order
FROM _versions v
CROSS JOIN LATERAL (
    SELECT
        g,
        -- 12 pricelists: for target version, one of them is pricelist 100
        CASE
            WHEN v.version_id = 1 THEN (g - 1) / 165 + 95   -- pricelists 95..106, includes 100
            ELSE (g - 1) / 165 + 1                           -- pricelists 1..12
        END AS pricelist_id,
        'T' || ((g - 1) % 4 + 1) AS opt_type,
        'OPT' || lpad(((g - 1) % 165 + 1)::text, 5, '0') AS opt_code
    FROM generate_series(1,
        CASE WHEN v.version_id = 1 THEN 165 * 12 ELSE 165 * 12 END
    ) g
) pl;

-- =============================================================================
-- 4. STATISTICS
-- =============================================================================

\echo '=== Running ANALYZE ==='

ANALYZE tax_codes;
ANALYZE product_options;
ANALYZE pricelist_options;
ANALYZE option_rules;

-- =============================================================================
-- 5. VERIFICATION
-- =============================================================================

\echo '=== Row counts ==='
SELECT 'product_options' AS table_name, count(*) AS row_count FROM product_options
UNION ALL SELECT 'pricelist_options', count(*) FROM pricelist_options
UNION ALL SELECT 'option_rules', count(*) FROM option_rules
UNION ALL SELECT 'tax_codes', count(*) FROM tax_codes
ORDER BY table_name;

\echo '=== Target version row counts (should be: 345, 165, 105) ==='
SELECT 'product_options' AS tbl, count(*) AS cnt
FROM product_options
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
  AND model_code = 'ABC' AND version_code = 'VER-001'
UNION ALL
SELECT 'pricelist_options', count(*)
FROM pricelist_options
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
  AND model_code = 'ABC' AND version_code = 'VER-001' AND pricelist_id = 100
UNION ALL
SELECT 'option_rules', count(*)
FROM option_rules
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
  AND model_code = 'ABC' AND version_code = 'VER-001'
ORDER BY tbl;

-- =============================================================================
-- 6. REPRODUCER QUERY
-- =============================================================================

\echo '=== Running reproducer query with EXPLAIN ANALYZE ==='
\echo '    On PG 15: expect ~70ms'
\echo '    On PG 16+: expect ~1400ms'

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    pl.*,
    po.*,
    tc.*,
    rules.*,
    pl_check.*,
    po2.*
FROM pricelist_options pl
JOIN product_options po
  ON po.brand_id = pl.brand_id
 AND po.line_code = pl.line_code
 AND po.model_year = pl.model_year
 AND po.model_code = pl.model_code
 AND po.version_code = pl.version_code
 AND po.option_type = pl.option_type
 AND po.option_code = pl.option_code
LEFT JOIN tax_codes tc
  ON tc.tax_id = pl.tax_id
LEFT JOIN option_rules rules
  ON rules.brand_id = pl.brand_id
 AND rules.line_code = pl.line_code
 AND rules.model_year = pl.model_year
 AND rules.model_code = pl.model_code
 AND rules.version_code = pl.version_code
 AND rules.option_type = pl.option_type
 AND rules.option_code = pl.option_code
LEFT JOIN pricelist_options pl_check
  ON pl_check.pricelist_id = pl.pricelist_id
 AND pl_check.brand_id = rules.brand_id
 AND pl_check.line_code = rules.line_code
 AND pl_check.model_year = rules.model_year
 AND pl_check.model_code = rules.model_code
 AND pl_check.version_code = rules.version_code
 AND pl_check.option_type = rules.sec_option_type
 AND pl_check.option_code = rules.sec_option_code
LEFT JOIN product_options po2
  ON po2.brand_id = rules.brand_id
 AND po2.line_code = rules.line_code
 AND po2.model_year = rules.model_year
 AND po2.model_code = rules.model_code
 AND po2.version_code = rules.version_code
 AND po2.option_type = rules.sec_option_type
 AND po2.option_code = rules.sec_option_code
WHERE pl.brand_id = 10
  AND pl.line_code = 'ABC'
  AND pl.model_year = 'YR'
  AND pl.model_code = 'ABC'
  AND pl.version_code = 'VER-001'
  AND pl.pricelist_id = 100;

-- =============================================================================
-- 7. WORKAROUND VERIFICATION
-- =============================================================================

\echo '=== Same query with join_collapse_limit = 1 (workaround) ==='

SET LOCAL join_collapse_limit = 1;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    pl.*,
    po.*,
    tc.*,
    rules.*,
    pl_check.*,
    po2.*
FROM pricelist_options pl
JOIN product_options po
  ON po.brand_id = pl.brand_id
 AND po.line_code = pl.line_code
 AND po.model_year = pl.model_year
 AND po.model_code = pl.model_code
 AND po.version_code = pl.version_code
 AND po.option_type = pl.option_type
 AND po.option_code = pl.option_code
LEFT JOIN tax_codes tc
  ON tc.tax_id = pl.tax_id
LEFT JOIN option_rules rules
  ON rules.brand_id = pl.brand_id
 AND rules.line_code = pl.line_code
 AND rules.model_year = pl.model_year
 AND rules.model_code = pl.model_code
 AND rules.version_code = pl.version_code
 AND rules.option_type = pl.option_type
 AND rules.option_code = pl.option_code
LEFT JOIN pricelist_options pl_check
  ON pl_check.pricelist_id = pl.pricelist_id
 AND pl_check.brand_id = rules.brand_id
 AND pl_check.line_code = rules.line_code
 AND pl_check.model_year = rules.model_year
 AND pl_check.model_code = rules.model_code
 AND pl_check.version_code = rules.version_code
 AND pl_check.option_type = rules.sec_option_type
 AND pl_check.option_code = rules.sec_option_code
LEFT JOIN product_options po2
  ON po2.brand_id = rules.brand_id
 AND po2.line_code = rules.line_code
 AND po2.model_year = rules.model_year
 AND po2.model_code = rules.model_code
 AND po2.version_code = rules.version_code
 AND po2.option_type = rules.sec_option_type
 AND po2.option_code = rules.sec_option_code
WHERE pl.brand_id = 10
  AND pl.line_code = 'ABC'
  AND pl.model_year = 'YR'
  AND pl.model_code = 'ABC'
  AND pl.version_code = 'VER-001'
  AND pl.pricelist_id = 100;

RESET join_collapse_limit;

-- =============================================================================
-- 8. CLEANUP (uncomment to drop tables after testing)
-- =============================================================================

-- DROP TABLE IF EXISTS pricelist_options CASCADE;
-- DROP TABLE IF EXISTS option_rules CASCADE;
-- DROP TABLE IF EXISTS product_options CASCADE;
-- DROP TABLE IF EXISTS tax_codes CASCADE;

\echo '=== Done ==='
\echo 'Compare EXPLAIN ANALYZE output from sections 6 and 7.'
\echo 'If section 6 is ~20x slower than section 7, the regression is reproduced.'
