crazy_test_db.sql

BEGIN;

-- ============================================================
-- CUSTOM ENUM TYPES
-- ============================================================

DO $$ BEGIN CREATE TYPE mood_enum AS ENUM ('happy', 'sad', 'angry', 'excited', '😐 neutral'); EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN CREATE TYPE traffic_light_enum AS ENUM ('🔴 red', '🟡 yellow', '🟢 green'); EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN CREATE TYPE planet_size_enum AS ENUM ('dwarf', 'terrestrial', 'gas_giant', 'ice_giant'); EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ============================================================
-- 1. NAMING NIGHTMARES
-- ============================================================

-- 1a. Empty table with weird column names (no rows — tests empty state)
CREATE TABLE IF NOT EXISTS "table with spaces and-dashes and 'quotes' and 🤯 emoji" (
  id SERIAL PRIMARY KEY,
  "my weird column" TEXT,
  "another-dashed-column" INT,
  "value (USD $)" NUMERIC(10,2),
  "created at 📅" TIMESTAMPTZ DEFAULT now()
);

-- 1b. Emoji-only table name, emoji column names
CREATE TABLE IF NOT EXISTS "🦄✨💫🌈🎭🎪🎨🎬🎤🎸🚀🔥💎👑🌍" (
  "🆔" SERIAL PRIMARY KEY,
  "📛 name" TEXT,
  "💰 value" NUMERIC(12,2),
  "📅 created" TIMESTAMPTZ DEFAULT now(),
  "🗒️ notes" TEXT,
  "🌍 country" TEXT,
  "⭐ rating" INT CHECK ("⭐ rating" BETWEEN 1 AND 5)
);

INSERT INTO "🦄✨💫🌈🎭🎪🎨🎬🎤🎸🚀🔥💎👑🌍" ("📛 name", "💰 value", "🗒️ notes", "🌍 country", "⭐ rating")
SELECT
  (ARRAY['🐉 Dragon','🦊 Fox','🐋 Whale','🦁 Lion','🦋 Butterfly','🐬 Dolphin','🦅 Eagle','🐙 Octopus','🦄 Unicorn','🐺 Wolf'])[1+(i%10)],
  (random()*9999.99)::numeric(12,2),
  '📝 Note #' || i || ': ' || repeat('🔄 ', 1+(i%5)),
  (ARRAY['🇺🇸 USA','🇬🇧 UK','🇯🇵 Japan','🇩🇪 Germany','🇫🇷 France','🇧🇷 Brazil','🇮🇳 India','🇨🇳 China'])[1+(i%8)],
  1+(i%5)
FROM generate_series(1,100) i;

-- 1c. Absurdly long table name
CREATE TABLE IF NOT EXISTS "the_most_absolutely_ridiculously_unnecessarily_long_table_name_that_any_database_administrator_has_ever_had_the_misfortune_of_encountering_in_their_entire_professional_career_god_help_us_all" (
  id SERIAL PRIMARY KEY,
  description TEXT,
  numeric_value NUMERIC,
  created_at TIMESTAMPTZ DEFAULT now(),
  is_active BOOLEAN DEFAULT true
);

INSERT INTO "the_most_absolutely_ridiculously_unnecessarily_long_table_name_that_any_database_administrator_has_ever_had_the_misfortune_of_encountering_in_their_entire_professional_career_god_help_us_all"
  (description, numeric_value, is_active)
SELECT 'Row '||i||': '||md5(i::text), (random()*1000)::numeric(10,2), random()>0.5
FROM generate_series(1,50) i;

-- 1d. Column names: SQL reserved words, special chars, crazy long, emojis
CREATE TABLE IF NOT EXISTS "column_nightmare_reserved_words_and_special_chars" (
  id SERIAL PRIMARY KEY,
  "select" TEXT,
  "from" TEXT,
  "where" TEXT,
  "order" TEXT,
  "limit" INT,
  "group" TEXT,
  "having" TEXT,
  "first name" TEXT,
  "last-name" TEXT,
  "email@address" TEXT,
  "value (USD $)" NUMERIC(10,2),
  "100% complete" BOOLEAN,
  "🔥 fire score 🔥" INT,
  "the longest column name that has ever graced a postgres table in the history of databases since the dawn of relational algebra itself and probably the universe" TEXT,
  "col with 'single' and ""double"" quotes" TEXT
);

INSERT INTO "column_nightmare_reserved_words_and_special_chars"
  ("select","from","where","order","limit","group","having",
   "first name","last-name","email@address","value (USD $)",
   "100% complete","🔥 fire score 🔥",
   "the longest column name that has ever graced a postgres table in the history of databases since the dawn of relational algebra itself and probably the universe",
   "col with 'single' and ""double"" quotes")
SELECT
  'SELECT * FROM table_'||i,
  'schema_'||(i%10),
  'id = '||i,
  'ORDER BY col_'||(i%5),
  i%1000,
  'group_'||(i%20),
  'COUNT(*) > '||(i%100),
  'FirstName'||i,
  'LastName-'||i,
  'user'||i||'@example'||(i%5)||'.com',
  (random()*99999.99)::numeric(10,2),
  random()>0.5,
  (random()*100)::int,
  'The value for the world''s longest column, row '||i||': '||md5(i::text),
  'it''s a "test" for row '||i
FROM generate_series(1,200) i;

-- ============================================================
-- 2. STRUCTURE EDGE CASES
-- ============================================================

-- 2a. Truly empty table (no rows)
CREATE TABLE IF NOT EXISTS empty_table_zero_rows (
  id SERIAL PRIMARY KEY,
  some_text TEXT,
  some_bool BOOLEAN,
  some_timestamp TIMESTAMPTZ,
  some_numeric NUMERIC(10,2),
  some_uuid UUID,
  some_jsonb JSONB,
  some_text_array TEXT[]
);

-- 2b. Single-column table
CREATE TABLE IF NOT EXISTS table_with_exactly_one_column (
  the_only_column TEXT
);

INSERT INTO table_with_exactly_one_column VALUES
  ('This table has exactly one column. '||repeat('How lonely. ',10)),
  (repeat('Lorem ipsum dolor sit amet, consectetur adipiscing elit. ',5)),
  ('Row 3: '||repeat('Just one column, nothing else to see here. ',8)),
  ('Row 4: The quick brown fox jumps over the lazy dog. '||repeat('Woof. ',20)),
  ('Row 5: '||md5('five')||' '||md5('five_b')||' '||md5('five_c')),
  ('Row 6: 🦄🔥💫✨🌈🎭🎪🎨🎬🎤 '||repeat('emoji! ',15)),
  ('Row 7: '||repeat('a',300)),
  ('Row 8: null values are boring, this has content '||repeat('yes ',30)),
  ('Row 9: '||repeat('z',250)),
  ('Row 10: Final row of the loneliest table.'||repeat(' ok',50));

-- 2c. Table with no primary key (with intentional duplicates)
CREATE TABLE IF NOT EXISTS no_primary_key_whatsoever (
  category TEXT,
  subcategory TEXT,
  value NUMERIC,
  note TEXT
);

INSERT INTO no_primary_key_whatsoever
SELECT
  (ARRAY['alpha','beta','gamma','delta','epsilon'])[1+(i%5)],
  (ARRAY['x','y','z'])[1+(i%3)],
  (random()*1000)::numeric(10,2),
  'note '||i
FROM generate_series(1,500) i;

-- Intentional duplicates
INSERT INTO no_primary_key_whatsoever VALUES
  ('alpha','x',42,'duplicate'),('alpha','x',42,'duplicate'),
  ('beta','y',99.9,'another dup'),('beta','y',99.9,'another dup'),
  ('gamma','z',0,'triple dup'),('gamma','z',0,'triple dup'),('gamma','z',0,'triple dup');

-- 2d. 4-column composite primary key
CREATE TABLE IF NOT EXISTS composite_pk_madness (
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  resource_type TEXT NOT NULL,
  action TEXT NOT NULL,
  granted_at TIMESTAMPTZ DEFAULT now(),
  expires_at TIMESTAMPTZ,
  granted_by TEXT,
  notes TEXT,
  PRIMARY KEY (tenant_id, user_id, resource_type, action)
);

INSERT INTO composite_pk_madness (tenant_id, user_id, resource_type, action, granted_by, notes)
SELECT
  1+(i%10), 1+(i%100),
  (ARRAY['document','folder','project','api','report','dashboard'])[1+(i%6)],
  (ARRAY['read','write','delete','admin','share'])[1+(i%5)],
  'user_'||(i%20),
  CASE WHEN random()<0.3 THEN NULL ELSE 'Auto-granted #'||i END
FROM generate_series(1,1000) i
ON CONFLICT DO NOTHING;

-- 2e. Self-referencing org chart (insert all rows with NULL parent first, then update)
CREATE TABLE IF NOT EXISTS "self_referencing_org_chart_🏢" (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  title TEXT,
  emoji_title TEXT,
  level INT DEFAULT 0,
  parent_id INT REFERENCES "self_referencing_org_chart_🏢"(id)
);

INSERT INTO "self_referencing_org_chart_🏢" (name, title, emoji_title, level, parent_id)
VALUES ('Alice CEO', 'Chief Executive Officer', '👑 CEO', 0, NULL);

INSERT INTO "self_referencing_org_chart_🏢" (name, title, emoji_title, level)
SELECT
  'Employee #'||i,
  (ARRAY['Manager 🏆','Engineer ⚙️','Designer 🎨','Analyst 📊','Lead 🚀','Senior 👴','Junior 🌱','Intern 🐣'])[1+(i%8)],
  (ARRAY['🏆','⚙️','🎨','📊','🚀','👴','🌱','🐣'])[1+(i%8)]||' Employee #'||i,
  CASE WHEN i<=5 THEN 1 WHEN i<=20 THEN 2 WHEN i<=60 THEN 3 ELSE 4 END
FROM generate_series(2,200) i;

-- Set parent_ids in phases
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 1 WHERE level = 1;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 2 WHERE level = 2 AND id <= 12;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 3 WHERE level = 2 AND id > 12;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 6  WHERE level = 3 AND id <= 35;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 7  WHERE level = 3 AND id > 35 AND id <= 50;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 8  WHERE level = 3 AND id > 50;
UPDATE "self_referencing_org_chart_🏢" SET parent_id = 21 WHERE level = 4;

-- 2f. Circular foreign keys (deferrable)
CREATE TABLE IF NOT EXISTS circular_reference_a (
  id SERIAL PRIMARY KEY,
  name TEXT,
  description TEXT,
  b_id INT
);

CREATE TABLE IF NOT EXISTS circular_reference_b (
  id SERIAL PRIMARY KEY,
  name TEXT,
  description TEXT,
  a_id INT REFERENCES circular_reference_a(id) DEFERRABLE INITIALLY DEFERRED
);

ALTER TABLE circular_reference_a
  ADD CONSTRAINT fk_circular_a_to_b
  FOREIGN KEY (b_id) REFERENCES circular_reference_b(id) DEFERRABLE INITIALLY DEFERRED;

INSERT INTO circular_reference_a (name, description)
SELECT 'a_'||i, 'Circular A row '||i FROM generate_series(1,50) i;
INSERT INTO circular_reference_b (name, description, a_id)
SELECT 'b_'||i, 'Circular B row '||i, i FROM generate_series(1,50) i;
UPDATE circular_reference_a SET b_id = id WHERE id <= 50;

-- 2g. Generated columns (Postgres 12+)
CREATE TABLE IF NOT EXISTS generated_columns_showcase (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  price_cents INT,
  price_dollars NUMERIC GENERATED ALWAYS AS (price_cents / 100.0) STORED,
  celsius NUMERIC,
  fahrenheit NUMERIC GENERATED ALWAYS AS (celsius * 9.0/5.0 + 32) STORED,
  emoji_name TEXT GENERATED ALWAYS AS ('🏷️ ' || first_name) STORED
);

INSERT INTO generated_columns_showcase (first_name, last_name, price_cents, celsius)
SELECT
  (ARRAY['Alice','Bob','Charlie','Diana','Eve','Frank','Grace','Hank','Iris','Jack'])[1+(i%10)],
  (ARRAY['Smith','Jones','Williams','Brown','Davis','Miller','Wilson','Moore','Taylor','Anderson'])[1+(i%10)],
  (random()*100000)::int,
  (random()*120-20)::numeric(5,2)
FROM generate_series(1,500) i;

-- 2h. Partitioned table (by quarter)
CREATE TABLE IF NOT EXISTS partitioned_events_by_month (
  id BIGINT NOT NULL,
  event_time TIMESTAMPTZ NOT NULL,
  event_type TEXT,
  user_id INT,
  payload JSONB,
  PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

CREATE TABLE IF NOT EXISTS partitioned_events_by_month_2024_q1
  PARTITION OF partitioned_events_by_month FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE IF NOT EXISTS partitioned_events_by_month_2024_q2
  PARTITION OF partitioned_events_by_month FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE IF NOT EXISTS partitioned_events_by_month_2024_q3
  PARTITION OF partitioned_events_by_month FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE IF NOT EXISTS partitioned_events_by_month_2024_q4
  PARTITION OF partitioned_events_by_month FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE IF NOT EXISTS partitioned_events_by_month_2025_q1
  PARTITION OF partitioned_events_by_month FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

INSERT INTO partitioned_events_by_month (id, event_time, event_type, user_id, payload)
SELECT
  i,
  '2024-01-01'::timestamptz + ((i%455)*interval'1 day'),
  (ARRAY['click','view','purchase','login','logout','error','signup','search'])[1+(i%8)],
  1+(i%200),
  jsonb_build_object('action','event_'||i,'value',(random()*100)::int,'tags',ARRAY['tag'||(i%5),'tag'||(i%10)])
FROM generate_series(1,2000) i;

-- ============================================================
-- 3. DATA VOLUME STRESS TESTS
-- ============================================================

-- 3a. 10,000-row table
CREATE TABLE IF NOT EXISTS "massive_rows_table_📊" (
  id BIGSERIAL PRIMARY KEY,
  category TEXT,
  subcategory TEXT,
  value NUMERIC(12,4),
  flag BOOLEAN,
  ts TIMESTAMPTZ,
  label TEXT
);

INSERT INTO "massive_rows_table_📊" (category, subcategory, value, flag, ts, label)
SELECT
  'category_'||(i%10),
  'sub_'||(i%50),
  (random()*99999.9999)::numeric(12,4),
  random()>0.5,
  now()-((random()*1000)::int*interval'1 day'),
  '📦 Item #'||i||' - '||md5(i::text)
FROM generate_series(1,10000) i;

-- 3b. Wide table: 60 columns × 5,000 rows
CREATE TABLE IF NOT EXISTS wide_table_of_absolute_doom (
  id SERIAL PRIMARY KEY,
  col_001 TEXT, col_002 INT, col_003 NUMERIC, col_004 BOOLEAN, col_005 TIMESTAMPTZ,
  col_006 TEXT, col_007 INT, col_008 NUMERIC, col_009 BOOLEAN, col_010 TIMESTAMPTZ,
  col_011 TEXT, col_012 INT, col_013 NUMERIC, col_014 BOOLEAN, col_015 TIMESTAMPTZ,
  col_016 TEXT, col_017 INT, col_018 NUMERIC, col_019 BOOLEAN, col_020 TIMESTAMPTZ,
  col_021 TEXT, col_022 INT, col_023 NUMERIC, col_024 BOOLEAN, col_025 TIMESTAMPTZ,
  col_026 TEXT, col_027 INT, col_028 NUMERIC, col_029 BOOLEAN, col_030 TIMESTAMPTZ,
  col_031 TEXT, col_032 INT, col_033 NUMERIC, col_034 BOOLEAN, col_035 TIMESTAMPTZ,
  col_036 TEXT, col_037 INT, col_038 NUMERIC, col_039 BOOLEAN, col_040 TIMESTAMPTZ,
  col_041 TEXT, col_042 INT, col_043 NUMERIC, col_044 BOOLEAN, col_045 TIMESTAMPTZ,
  col_046 TEXT, col_047 INT, col_048 NUMERIC, col_049 BOOLEAN, col_050 TIMESTAMPTZ,
  col_051 TEXT, col_052 INT, col_053 NUMERIC, col_054 BOOLEAN, col_055 TIMESTAMPTZ,
  col_056 TEXT, col_057 INT, col_058 NUMERIC, col_059 BOOLEAN
);

INSERT INTO wide_table_of_absolute_doom SELECT
  i,
  md5((i*1)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*2)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*3)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*4)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*5)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*6)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*7)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*8)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*9)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*10)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*11)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5,now()-((random()*500)::int*interval'1 day'),
  md5((i*12)::text),(random()*1e6)::int,(random()*1e4)::numeric(10,2),random()>0.5
FROM generate_series(1,5000) i;

-- 3c. Long text values
CREATE TABLE IF NOT EXISTS "long_text_torture_🔤" (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  metadata_json TEXT,
  tags TEXT,
  base64_blob TEXT
);

INSERT INTO "long_text_torture_🔤" (title, body, metadata_json, tags, base64_blob)
SELECT
  repeat('LongTitle'||i||'_',30)||'🔚',
  repeat('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 🌊 ',25),
  '{"id":'||i||',"level":{"nested":{"deeply":{"very":{"extremely":{"data":"'||md5(i::text)||'","count":'||(i*7)||'}}}}}, "tags":["'||md5((i+1)::text)||'","'||md5((i+2)::text)||'","'||md5((i+3)::text)||'"], "description":"'||repeat('This is a very long description field that goes on and on forever. ',10)||'"}',
  repeat('tag-'||(i%50)||', ',30)||'final-tag',
  repeat(encode(('row'||i)::bytea,'base64'),50)
FROM generate_series(1,1000) i;

-- 3d. JSON blob festival
CREATE TABLE IF NOT EXISTS "json_blob_festival_🎪" (
  id SERIAL PRIMARY KEY,
  simple_json JSONB,
  nested_json JSONB,
  json_array JSONB,
  config JSONB,
  stats JSONB
);

INSERT INTO "json_blob_festival_🎪" (simple_json, nested_json, json_array, config, stats)
SELECT
  jsonb_build_object('id',i,'name','item_'||i,'active',random()>0.5,'score',(random()*100)::int),
  jsonb_build_object('level1',jsonb_build_object('level2',jsonb_build_object('level3',jsonb_build_object('level4',jsonb_build_object('level5',jsonb_build_object('value',md5(i::text),'number',i,'flag',true)),'arr','[1,2,3]'::jsonb),'meta',jsonb_build_object('created',now()::text,'tags','["a","b","c"]'::jsonb)),'items','[{"x":1},{"x":2},{"x":3}]'::jsonb),'root_id',i),
  (SELECT jsonb_agg(jsonb_build_object('idx',j,'val',md5((i*j)::text),'num',(random()*100)::int)) FROM generate_series(1,20) j),
  jsonb_build_object('feature_flags',jsonb_build_object('dark_mode',random()>0.5,'beta',random()>0.5,'premium',random()>0.5),'limits',jsonb_build_object('max_items',(random()*1000)::int,'rate_limit',100),'theme',(ARRAY['dark','light','auto','solarized','dracula'])[1+(i%5)]),
  jsonb_build_object('total',(random()*10000)::int,'avg',(random()*100)::numeric(5,2),'min',(random()*10)::numeric(5,2),'max',(random()*1000)::numeric(5,2),'p95',(random()*900)::numeric(5,2),'count',i*7,'nulls',(random()*i)::int)
FROM generate_series(1,2000) i;

-- ============================================================
-- 4. DATA TYPE SHOWCASE
-- ============================================================

-- 4a. Every standard Postgres type
CREATE TABLE IF NOT EXISTS "every_postgres_type_ever_🎉" (
  id SERIAL PRIMARY KEY,
  -- Integers & Numerics
  col_smallint     SMALLINT,
  col_integer      INTEGER,
  col_bigint       BIGINT,
  col_numeric      NUMERIC(20,5),
  col_real         REAL,
  col_double       DOUBLE PRECISION,
  col_money        MONEY,
  -- Strings
  col_char         CHAR(10),
  col_varchar      VARCHAR(255),
  col_text         TEXT,
  -- Boolean
  col_boolean      BOOLEAN,
  -- Date/Time
  col_date         DATE,
  col_time         TIME,
  col_timetz       TIMETZ,
  col_timestamp    TIMESTAMP,
  col_timestamptz  TIMESTAMPTZ,
  col_interval     INTERVAL,
  -- UUID
  col_uuid         UUID,
  -- JSON
  col_json         JSON,
  col_jsonb        JSONB,
  -- Binary
  col_bytea        BYTEA,
  -- Network
  col_inet         INET,
  col_cidr         CIDR,
  col_macaddr      MACADDR,
  col_macaddr8     MACADDR8,
  -- Geometric
  col_point        POINT,
  col_line         LINE,
  col_lseg         LSEG,
  col_box          BOX,
  col_path         PATH,
  col_polygon      POLYGON,
  col_circle       CIRCLE,
  -- Arrays
  col_int_array    INT[],
  col_text_array   TEXT[],
  col_jsonb_array  JSONB[],
  col_bool_array   BOOLEAN[],
  -- Text search
  col_tsvector     TSVECTOR,
  col_tsquery      TSQUERY,
  -- Bit strings
  col_bit          BIT(8),
  col_varbit       BIT VARYING(16),
  -- XML
  col_xml          XML,
  -- OID
  col_oid          OID,
  -- Enum
  col_mood         mood_enum,
  -- Range types
  col_int4range    INT4RANGE,
  col_tstzrange    TSTZRANGE,
  col_numrange     NUMRANGE
);

INSERT INTO "every_postgres_type_ever_🎉" (
  col_smallint, col_integer, col_bigint, col_numeric, col_real, col_double, col_money,
  col_char, col_varchar, col_text, col_boolean,
  col_date, col_time, col_timetz, col_timestamp, col_timestamptz, col_interval,
  col_uuid, col_json, col_jsonb, col_bytea,
  col_inet, col_cidr, col_macaddr, col_macaddr8,
  col_point, col_line, col_lseg, col_box, col_path, col_polygon, col_circle,
  col_int_array, col_text_array, col_jsonb_array, col_bool_array,
  col_tsvector, col_tsquery,
  col_bit, col_varbit, col_xml, col_oid, col_mood,
  col_int4range, col_tstzrange, col_numrange
)
SELECT
  (random()*32767)::smallint,
  (random()*2147483647)::int,
  (random()*9007199254740991)::bigint,
  (random()*99999999999999.99999)::numeric(20,5),
  random()::real,
  random()::double precision,
  (random()*99999.99)::numeric::money,
  substr(md5(i::text),1,10),
  'varchar_'||i||'_'||md5(i::text),
  'text value for row '||i||' 🎉',
  random()>0.5,
  current_date-((random()*3650)::int),
  ('00:00:00'::time+((random()*86400)::int*interval'1 second'))::time,
  ('00:00:00+00'::timetz+((random()*86400)::int*interval'1 second')),
  (now()-((random()*3650)::int*interval'1 day'))::timestamp,
  now()-((random()*3650)::int*interval'1 day'),
  ((random()*365)::int||' days '||(random()*24)::int||' hours')::interval,
  gen_random_uuid(),
  ('{"key":"'||md5(i::text)||'","n":'||i||'}')::json,
  jsonb_build_object('key',md5(i::text),'n',i,'flag',random()>0.5),
  decode(md5(i::text),'hex'),
  ('192.168.'||(i%256)||'.'||(i%256))::inet,
  ('10.'||(i%256)||'.0.0/16')::cidr,
  (lpad(to_hex(i%256),2,'0')||':'||lpad(to_hex((i+1)%256),2,'0')||':'||lpad(to_hex((i+2)%256),2,'0')||':'||lpad(to_hex((i+3)%256),2,'0')||':'||lpad(to_hex((i+4)%256),2,'0')||':'||lpad(to_hex((i+5)%256),2,'0'))::macaddr,
  ('08:00:2b:'||lpad(to_hex(i%256),2,'0')||':'||lpad(to_hex((i+1)%256),2,'0')||':ff:fe:'||lpad(to_hex((i+2)%256),2,'0'))::macaddr8,
  point(random()*100-50, random()*100-50),
  ('{'||(random()*2-1)||','||(random()*2-1)||','||(random()*10-5)||'}')::line,
  lseg(point(random()*10,random()*10),point(random()*10+10,random()*10+10)),
  box(point(random()*10,random()*10),point(random()*10+10,random()*10+10)),
  '((0,0),(1,1),(2,0))'::path,
  '((0,0),(1,2),(2,0))'::polygon,
  circle(point(random()*50,random()*50),random()*10+1),
  ARRAY[(random()*100)::int,(random()*100)::int,(random()*100)::int],
  ARRAY['text_'||i,'val_'||(i%10),md5(i::text)],
  ARRAY[('{"x":1}'::jsonb),('{"n":'||i||'}')::jsonb],
  ARRAY[random()>0.5,random()>0.5,random()>0.5],
  to_tsvector('english','hello world row '||i||' quick brown fox jumps over lazy dog'),
  to_tsquery('hello & world'),
  B'10101010',
  (i%65536)::int::bit(16)::varbit,
  ('<row><id>'||i||'</id><data>'||md5(i::text)||'</data></row>')::xml,
  i::oid,
  (ARRAY['happy','sad','angry','excited','😐 neutral']::mood_enum[])[1+(i%5)],
  int4range((i%100)*10,(i%100)*10+(random()*10+1)::int),
  tstzrange(now()-((random()*365)::int*interval'1 day'),now()+((random()*30)::int*interval'1 day')),
  numrange((random()*100)::numeric(5,2),(random()*100+100)::numeric(5,2))
FROM generate_series(1,500) i;

-- 4b. Enum showcase
CREATE TABLE IF NOT EXISTS enum_type_showcase (
  id SERIAL PRIMARY KEY,
  mood mood_enum,
  traffic traffic_light_enum,
  size planet_size_enum,
  description TEXT
);

INSERT INTO enum_type_showcase (mood, traffic, size, description)
SELECT
  (ARRAY['happy','sad','angry','excited','😐 neutral']::mood_enum[])[1+(i%5)],
  (ARRAY['🔴 red','🟡 yellow','🟢 green']::traffic_light_enum[])[1+(i%3)],
  (ARRAY['dwarf','terrestrial','gas_giant','ice_giant']::planet_size_enum[])[1+(i%4)],
  'Enum row #'||i||' - feeling '||(ARRAY['happy','sad','angry','excited','😐 neutral'])[1+(i%5)]
FROM generate_series(1,300) i;

-- 4c. Array party
CREATE TABLE IF NOT EXISTS "array_party_🎉" (
  id SERIAL PRIMARY KEY,
  int_array INT[],
  text_array TEXT[],
  bool_array BOOLEAN[],
  float_array FLOAT[],
  uuid_array UUID[],
  jsonb_array JSONB[],
  timestamptz_array TIMESTAMPTZ[]
);

INSERT INTO "array_party_🎉" (int_array, text_array, bool_array, float_array, uuid_array, jsonb_array, timestamptz_array)
SELECT
  array(SELECT (random()*1000)::int FROM generate_series(1,1+(i%20))),
  array(SELECT 'item_'||j||'_'||md5((i*j)::text) FROM generate_series(1,1+(i%15)) j),
  array(SELECT random()>0.5 FROM generate_series(1,1+(i%10))),
  array(SELECT (random()*999.99)::float FROM generate_series(1,1+(i%12))),
  array(SELECT gen_random_uuid() FROM generate_series(1,1+(i%5))),
  array(SELECT jsonb_build_object('k',j,'v',md5((i*j)::text)) FROM generate_series(1,1+(i%8)) j),
  array(SELECT now()-((random()*365)::int*interval'1 day') FROM generate_series(1,1+(i%6)))
FROM generate_series(1,500) i;

-- 4d. UUID world
CREATE TABLE IF NOT EXISTS uuid_primary_key_world (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id UUID,
  ref_id UUID,
  name TEXT,
  value NUMERIC,
  extra_uuid_1 UUID DEFAULT gen_random_uuid(),
  extra_uuid_2 UUID DEFAULT gen_random_uuid()
);

INSERT INTO uuid_primary_key_world (name, value)
SELECT 'uuid_row_'||i, (random()*9999)::numeric(10,2)
FROM generate_series(1,1000) i;

-- 4e. Null everywhere (~70% nulls per column)
CREATE TABLE IF NOT EXISTS "null_null_null_everywhere_😶" (
  id SERIAL PRIMARY KEY,
  col_a TEXT,
  col_b INT,
  col_c NUMERIC,
  col_d BOOLEAN,
  col_e TIMESTAMPTZ,
  col_f UUID,
  col_g JSONB,
  col_h TEXT[],
  col_i TEXT,  -- mix of NULL and empty string
  col_j BIGINT
);

INSERT INTO "null_null_null_everywhere_😶"
  (col_a,col_b,col_c,col_d,col_e,col_f,col_g,col_h,col_i,col_j)
SELECT
  CASE WHEN random()<0.7 THEN NULL ELSE 'value_'||i END,
  CASE WHEN random()<0.7 THEN NULL ELSE (random()*100)::int END,
  CASE WHEN random()<0.7 THEN NULL ELSE (random()*999.99)::numeric(7,2) END,
  CASE WHEN random()<0.7 THEN NULL ELSE random()>0.5 END,
  CASE WHEN random()<0.7 THEN NULL ELSE now()-((random()*365)::int*interval'1 day') END,
  CASE WHEN random()<0.7 THEN NULL ELSE gen_random_uuid() END,
  CASE WHEN random()<0.7 THEN NULL ELSE ('{"v":'||i||'}')::jsonb END,
  CASE WHEN random()<0.7 THEN NULL ELSE ARRAY['a','b','c'] END,
  CASE WHEN random()<0.3 THEN NULL WHEN random()<0.5 THEN '' ELSE 'not empty '||i END,
  CASE WHEN random()<0.7 THEN NULL ELSE i::bigint END
FROM generate_series(1,500) i;

-- 4f. Timestamp / timezone nightmare
CREATE TABLE IF NOT EXISTS "timestamp_timezone_nightmare_🕐" (
  id SERIAL PRIMARY KEY,
  ts_no_tz   TIMESTAMP,
  ts_with_tz TIMESTAMPTZ,
  date_only  DATE,
  time_only  TIME,
  time_tz    TIMETZ,
  interval_val INTERVAL,
  epoch_int  BIGINT,
  label      TEXT
);

INSERT INTO "timestamp_timezone_nightmare_🕐"
  (ts_no_tz,ts_with_tz,date_only,time_only,time_tz,interval_val,epoch_int,label)
SELECT
  ts,
  tz_val,
  ts::date,
  ts::time,
  (ts AT TIME ZONE 'UTC')::timetz,
  ts-'2000-01-01 00:00:00'::timestamp,
  extract(epoch from tz_val)::bigint,
  lbl
FROM (
  SELECT
    i,
    CASE i%6
      WHEN 0 THEN '1850-06-15 12:00:00'::timestamp+(i*interval'100 days')
      WHEN 1 THEN '1970-01-01 00:00:01'::timestamp
      WHEN 2 THEN now()::timestamp
      WHEN 3 THEN '2100-12-31 23:59:59'::timestamp
      WHEN 4 THEN '2000-01-01 12:00:00'::timestamp+((random()*3650)::int*interval'1 day')
      ELSE    '1969-12-31 23:59:59'::timestamp
    END AS ts,
    CASE i%4
      WHEN 0 THEN '2024-06-15 08:30:00 America/New_York'::timestamptz
      WHEN 1 THEN '2024-06-15 08:30:00 Asia/Tokyo'::timestamptz
      WHEN 2 THEN '2024-06-15 08:30:00 Europe/London'::timestamptz
      ELSE now()-((random()*365)::int*interval'1 day')
    END AS tz_val,
    (ARRAY['🏛️ historic','⏱️ epoch start','📅 present','🚀 far future','🎲 random','🌑 pre-epoch'])[1+(i%6)] AS lbl
  FROM generate_series(1,500) i
) sub;

-- ============================================================
-- 5. CONSTRAINTS & DEFAULTS SHOWCASE
-- ============================================================

CREATE TABLE IF NOT EXISTS constraints_and_defaults_showcase (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  tenant_id INT NOT NULL DEFAULT 1,
  age INT CHECK (age > 0 AND age < 150),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive','pending')),
  score NUMERIC DEFAULT 0,
  display_name TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  UNIQUE (email, tenant_id)
);

INSERT INTO constraints_and_defaults_showcase (email, tenant_id, age, status, score, display_name)
SELECT
  'user'||i||'@tenant'||(i%5+1)||'.com',
  i%5+1,
  18+((random()*62)::int),
  (ARRAY['active','inactive','pending'])[1+(i%3)],
  (random()*100)::numeric(5,2),
  CASE WHEN random()<0.2 THEN NULL ELSE '👤 User #'||i END
FROM generate_series(1,200) i;

-- ============================================================
-- 6. VIEWS
-- ============================================================

CREATE OR REPLACE VIEW v_simple_select_view AS
  SELECT id, category, subcategory, value, flag, ts, label
  FROM "massive_rows_table_📊"
  WHERE flag = true
  ORDER BY ts DESC;

CREATE OR REPLACE VIEW "📊 Executive Analytics Dashboard View 📊" AS
  SELECT
    category,
    subcategory,
    COUNT(*)                          AS total_rows,
    ROUND(AVG(value)::numeric, 2)    AS avg_value,
    ROUND(SUM(value)::numeric, 2)    AS sum_value,
    MIN(ts)                           AS earliest,
    MAX(ts)                           AS latest,
    COUNT(*) FILTER (WHERE flag)      AS flagged_count
  FROM "massive_rows_table_📊"
  GROUP BY category, subcategory;

CREATE OR REPLACE VIEW v_window_functions_showcase AS
  SELECT
    id, category, value,
    RANK()       OVER (PARTITION BY category ORDER BY value DESC) AS rank_in_category,
    ROW_NUMBER() OVER (ORDER BY id)                               AS global_row_num,
    LAG(value,1)  OVER (PARTITION BY category ORDER BY id)        AS prev_value,
    LEAD(value,1) OVER (PARTITION BY category ORDER BY id)        AS next_value,
    NTILE(4)     OVER (PARTITION BY category ORDER BY value)      AS quartile,
    ROUND(AVG(value) OVER (PARTITION BY category)::numeric, 2)   AS category_avg
  FROM "massive_rows_table_📊";

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_materialized_expensive_aggregation AS
  SELECT
    category,
    COUNT(*)                        AS row_count,
    ROUND(AVG(value)::numeric, 2)  AS avg_value,
    MIN(value)                      AS min_value,
    MAX(value)                      AS max_value,
    ROUND(SUM(value)::numeric, 2)  AS total_value,
    COUNT(*) FILTER (WHERE flag)    AS flagged
  FROM "massive_rows_table_📊"
  GROUP BY category;

CREATE MATERIALIZED VIEW IF NOT EXISTS "🔮 Magic Materialized View of Doom 🔮" AS
  SELECT
    "🆔"        AS "🪪 identifier",
    "📛 name"   AS "✏️ what they call themselves",
    "💰 value"  AS "💵 how much they got",
    "🌍 country" AS "🗺️ where theyre from",
    "⭐ rating" AS "🏅 how good they are"
  FROM "🦄✨💫🌈🎭🎪🎨🎬🎤🎸🚀🔥💎👑🌍";

COMMIT;