-- Test tables for v4 patch: Add sampling statistics to autoanalyze log output

-- =============================================================================
-- 1. Regular table (100,000 rows)
-- =============================================================================
CREATE TABLE regular_table (id serial PRIMARY KEY, data text, value integer);
INSERT INTO regular_table SELECT g, 'data_' || g, g % 100 FROM generate_series(1, 100000) g;

-- =============================================================================
-- 2. Foreign table via postgres_fdw (50,000 rows)
-- =============================================================================
CREATE TABLE ft_source (id serial, data text);
INSERT INTO ft_source SELECT g, 'ft_data_' || g FROM generate_series(1, 50000) g;

DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;
DROP SERVER IF EXISTS loopback CASCADE;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres', port '5433');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
DROP FOREIGN TABLE IF EXISTS ft CASCADE;
CREATE FOREIGN TABLE ft (id integer, data text) SERVER loopback OPTIONS (table_name 'ft_source');

-- =============================================================================
-- 3. Inheritance table (75,000 total rows)
-- =============================================================================
CREATE TABLE parent_tbl (id serial, data text);
CREATE TABLE child_tbl1 () INHERITS (parent_tbl);
CREATE TABLE child_tbl2 () INHERITS (parent_tbl);
CREATE TABLE child_tbl3 () INHERITS (parent_tbl);
INSERT INTO parent_tbl SELECT g, 'parent_' || g FROM generate_series(1, 10000) g;
INSERT INTO child_tbl1 SELECT g, 'child1_' || g FROM generate_series(1, 30000) g;
INSERT INTO child_tbl2 SELECT g, 'child2_' || g FROM generate_series(1, 20000) g;
INSERT INTO child_tbl3 SELECT g, 'child3_' || g FROM generate_series(1, 15000) g;

-- =============================================================================
-- 4. Partitioned table (60,000 total rows)
-- =============================================================================
CREATE TABLE part_tbl (id serial, data text, created_at date) PARTITION BY RANGE (created_at);
CREATE TABLE part_tbl_202501 PARTITION OF part_tbl FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE part_tbl_202502 PARTITION OF part_tbl FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE part_tbl_202503 PARTITION OF part_tbl FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
INSERT INTO part_tbl SELECT g, 'part_' || g, '2025-01-15' FROM generate_series(1, 25000) g;
INSERT INTO part_tbl SELECT g, 'part_' || g, '2025-02-15' FROM generate_series(1, 20000) g;
INSERT INTO part_tbl SELECT g, 'part_' || g, '2025-03-15' FROM generate_series(1, 15000) g;

-- =============================================================================
-- 5. Edge case: Parent with 0 rows, all children are foreign tables
-- =============================================================================
CREATE TABLE ft_child_source1 (id serial, data text);
CREATE TABLE ft_child_source2 (id serial, data text);
INSERT INTO ft_child_source1 SELECT g, 'ftc1_' || g FROM generate_series(1, 20000) g;
INSERT INTO ft_child_source2 SELECT g, 'ftc2_' || g FROM generate_series(1, 15000) g;

CREATE TABLE parent_empty_ft_children (id serial, data text);
-- Parent has 0 rows

DROP FOREIGN TABLE IF EXISTS ft_child1 CASCADE;
DROP FOREIGN TABLE IF EXISTS ft_child2 CASCADE;
CREATE FOREIGN TABLE ft_child1 (id integer NOT NULL, data text)
    SERVER loopback OPTIONS (table_name 'ft_child_source1');
CREATE FOREIGN TABLE ft_child2 (id integer NOT NULL, data text)
    SERVER loopback OPTIONS (table_name 'ft_child_source2');
ALTER FOREIGN TABLE ft_child1 INHERIT parent_empty_ft_children;
ALTER FOREIGN TABLE ft_child2 INHERIT parent_empty_ft_children;

-- =============================================================================
-- 6. Edge case: Parent with data, all children are empty
-- =============================================================================
CREATE TABLE parent_with_data (id serial, data text);
CREATE TABLE empty_child1 () INHERITS (parent_with_data);
CREATE TABLE empty_child2 () INHERITS (parent_with_data);
CREATE TABLE empty_child3 () INHERITS (parent_with_data);
INSERT INTO parent_with_data SELECT g, 'parent_' || g FROM generate_series(1, 50000) g;
-- All children have 0 rows
