-- ============================================================
-- BISCUIT MULTI-COLUMN INDEX TEST SUITE
-- Tests multi-column indexing, composite pattern matching,
-- and type conversion across different PostgreSQL data types
-- ============================================================



DROP TABLE IF EXISTS test_multicolumn CASCADE;
DROP TABLE IF EXISTS test_types_multicolumn CASCADE;
DROP TABLE IF EXISTS test_performance CASCADE;

-- ============================================================
-- TEST 1: Basic Multi-Column Text Index
-- ============================================================
DO $$
BEGIN
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 1: Basic Multi-Column Text Index';
RAISE NOTICE '============================================================';


CREATE TABLE test_multicolumn (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    city TEXT
);

-- Insert test data
INSERT INTO test_multicolumn (first_name, last_name, email, city) VALUES
    ('John', 'Doe', 'john.doe@example.com', 'New York'),
    ('Jane', 'Smith', 'jane.smith@gmail.com', 'Los Angeles'),
    ('Bob', 'Johnson', 'bob.j@hotmail.com', 'Chicago'),
    ('Alice', 'Williams', 'alice.w@yahoo.com', 'Houston'),
    ('Charlie', 'Brown', 'charlie.brown@example.com', 'Phoenix'),
    ('David', 'Jones', 'david.jones@gmail.com', 'Philadelphia'),
    ('Eve', 'Davis', 'eve.davis@example.com', 'San Antonio'),
    ('Frank', 'Miller', 'frank.m@hotmail.com', 'San Diego'),
    ('Grace', 'Wilson', 'grace.wilson@gmail.com', 'Dallas'),
    ('Henry', 'Moore', 'henry.moore@example.com', 'San Jose');

RAISE NOTICE '';
RAISE NOTICE 'Creating single-column index (baseline)...';
CREATE INDEX idx_single_first ON test_multicolumn USING biscuit(first_name);

RAISE NOTICE '';
RAISE NOTICE 'Creating multi-column index (2 columns)...';
CREATE INDEX idx_multi_name ON test_multicolumn USING biscuit(first_name, last_name);

RAISE NOTICE '';
RAISE NOTICE 'Creating multi-column index (3 columns)...';
CREATE INDEX idx_multi_full ON test_multicolumn USING biscuit(first_name, last_name, email);

RAISE NOTICE '';
RAISE NOTICE 'Index creation successful!';
RAISE NOTICE '';

END $$;
-- Verify indexes exist
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes 
WHERE tablename = 'test_multicolumn'
ORDER BY indexname;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 1.1: Single Column Query';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_multicolumn WHERE first_name LIKE 'J%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;
SELECT first_name, last_name FROM test_multicolumn WHERE first_name LIKE 'J%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 1.2: Multi-Column Query (Exact Match)';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_multicolumn WHERE first_name LIKE 'John%' AND last_name LIKE 'Doe%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;

SELECT * FROM test_multicolumn WHERE first_name LIKE 'John%' AND last_name LIKE 'Doe%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 1.3: Multi-Column Query (Pattern Match)';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_multicolumn WHERE first_name LIKE '%o%' AND last_name LIKE '%o%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;

SELECT first_name, last_name FROM test_multicolumn 
WHERE first_name LIKE '%o%' AND last_name LIKE '%o%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 1.4: Three-Column Query';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_multicolumn 
WHERE first_name LIKE 'J%' 
  AND last_name LIKE '%o%' 
  AND email LIKE '%example.com';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;

SELECT first_name, last_name, email FROM test_multicolumn 
WHERE first_name LIKE 'J%' 
  AND last_name LIKE '%o%' 
  AND email LIKE '%example.com';

-- ============================================================
-- TEST 2: Multi-Column with Different Data Types
-- ============================================================

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 2: Multi-Column with Different Data Types';
RAISE NOTICE '============================================================';
END $$;

CREATE TABLE test_types_multicolumn (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary NUMERIC(10,2),
    hire_date DATE,
    is_active BOOLEAN,
    score FLOAT
);

INSERT INTO test_types_multicolumn (name, age, salary, hire_date, is_active, score) VALUES
    ('Alice', 30, 75000.00, '2020-01-15', true, 95.5),
    ('Bob', 25, 55000.00, '2021-03-20', true, 88.2),
    ('Charlie', 35, 85000.00, '2019-06-10', false, 92.7),
    ('David', 28, 65000.00, '2020-11-05', true, 90.1),
    ('Eve', 32, 78000.00, '2018-09-25', true, 94.3),
    ('Frank', 27, 60000.00, '2021-01-30', false, 87.9),
    ('Grace', 31, 72000.00, '2019-12-15', true, 91.5),
    ('Henry', 29, 68000.00, '2020-07-20', true, 89.8),
    ('Ivy', 33, 80000.00, '2018-04-12', true, 93.6),
    ('Jack', 26, 58000.00, '2021-08-05', false, 86.4);

DO $$ BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Creating multi-type index (TEXT + INTEGER)...';
CREATE INDEX idx_types_name_age ON test_types_multicolumn USING biscuit(name, age);

RAISE NOTICE '';
RAISE NOTICE 'Creating multi-type index (TEXT + DATE)...';
CREATE INDEX idx_types_name_date ON test_types_multicolumn USING biscuit(name, hire_date);

RAISE NOTICE '';
RAISE NOTICE 'Creating multi-type index (TEXT + BOOLEAN)...';
CREATE INDEX idx_types_name_active ON test_types_multicolumn USING biscuit(name, is_active);

RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 2.1: TEXT + INTEGER Query';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_types_multicolumn 
WHERE name LIKE 'A%' AND age::text LIKE '3%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;
SELECT name, age FROM test_types_multicolumn 
WHERE name LIKE 'A%' AND age::text LIKE '3%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 2.2: TEXT + DATE Query';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_types_multicolumn 
WHERE name LIKE '%e%' AND hire_date::text LIKE '2020%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;

SELECT name, hire_date FROM test_types_multicolumn 
WHERE name LIKE '%e%' AND hire_date::text LIKE '2020%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 2.3: TEXT + BOOLEAN Query';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_types_multicolumn 
WHERE name LIKE '%a%' AND is_active = true;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Results:';
END $$;

SELECT name, is_active FROM test_types_multicolumn 
WHERE name LIKE '%a%' AND is_active = true;

-- ============================================================
-- TEST 3: Index Statistics and Health
-- ============================================================

DO $$ 
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 3: Index Statistics and Health';
RAISE NOTICE '============================================================';

RAISE NOTICE '';
RAISE NOTICE 'Multi-column name index stats:';
END $$;

SELECT biscuit_index_stats('idx_multi_name'::regclass::oid);

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Multi-column full index stats:';
END $$;

SELECT biscuit_index_stats('idx_multi_full'::regclass::oid);

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Multi-type name+age index stats:';
END $$;

SELECT biscuit_index_stats('idx_types_name_age'::regclass::oid);

-- ============================================================
-- TEST 4: CRUD Operations on Multi-Column Indexes
-- ============================================================

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 4: CRUD Operations on Multi-Column Indexes';
RAISE NOTICE '============================================================';

RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 4.1: INSERT into table with multi-column index';
RAISE NOTICE '------------------------------------------------------------';

INSERT INTO test_multicolumn (first_name, last_name, email, city) VALUES
    ('Michael', 'Scott', 'michael.scott@dundermifflin.com', 'Scranton'),
    ('Jim', 'Halpert', 'jim.halpert@dundermifflin.com', 'Scranton'),
    ('Pam', 'Beesly', 'pam.beesly@dundermifflin.com', 'Scranton');

RAISE NOTICE '';
RAISE NOTICE 'Querying newly inserted data:';
END $$;

SELECT first_name, last_name, email FROM test_multicolumn 
WHERE first_name LIKE 'M%' AND last_name LIKE 'S%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 4.2: UPDATE with multi-column index';
RAISE NOTICE '------------------------------------------------------------';

UPDATE test_multicolumn 
SET last_name = 'Halpert-Beesly' 
WHERE first_name = 'Pam';

RAISE NOTICE '';
RAISE NOTICE 'Verifying update:';
END $$;

SELECT first_name, last_name FROM test_multicolumn WHERE first_name = 'Pam';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 4.3: DELETE with multi-column index';
RAISE NOTICE '------------------------------------------------------------';

DELETE FROM test_multicolumn 
WHERE first_name LIKE 'M%' AND last_name LIKE 'S%';

RAISE NOTICE '';
RAISE NOTICE 'Verifying deletion:';
END $$;

SELECT COUNT(*) as remaining_records FROM test_multicolumn 
WHERE first_name LIKE 'M%' AND last_name LIKE 'S%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Index stats after CRUD operations:';
END $$;

SELECT biscuit_index_stats('idx_multi_full'::regclass::oid);

-- ============================================================
-- TEST 5: Performance Comparison
-- ============================================================

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 5: Performance Comparison (Single vs Multi-Column)';
RAISE NOTICE '============================================================';
END $$;

CREATE TABLE test_performance (
    id SERIAL PRIMARY KEY,
    col1 TEXT,
    col2 TEXT,
    col3 TEXT
);

-- Insert larger dataset
INSERT INTO test_performance (col1, col2, col3)
SELECT 
    'user_' || (random() * 1000)::int,
    'dept_' || (random() * 50)::int,
    'city_' || (random() * 100)::int
FROM generate_series(1, 10000);

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Creating single-column indexes...';
CREATE INDEX idx_perf_col1 ON test_performance USING biscuit(col1);
CREATE INDEX idx_perf_col2 ON test_performance USING biscuit(col2);

RAISE NOTICE '';
RAISE NOTICE 'Creating multi-column index...';
CREATE INDEX idx_perf_multi ON test_performance USING biscuit(col1, col2);
END $$;

ANALYZE test_performance;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 5.1: Query with single-column index (col1 only)';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SET enable_bitmapscan = off;
SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT * FROM test_performance WHERE col1 LIKE 'user_1%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 5.2: Query with multi-column index (col1 + col2)';
RAISE NOTICE '------------------------------------------------------------';
END $$;

EXPLAIN ANALYZE
SELECT * FROM test_performance 
WHERE col1 LIKE 'user_1%' AND col2 LIKE 'dept_%';

SET enable_bitmapscan = on;
SET enable_seqscan = on;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE 'Multi-column performance index stats:';
END $$;
SELECT biscuit_index_stats('idx_perf_multi'::regclass::oid);

-- ============================================================
-- TEST 6: Edge Cases and Null Handling
-- ============================================================

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 6: Edge Cases and Null Handling';
RAISE NOTICE '============================================================';

INSERT INTO test_multicolumn (first_name, last_name, email, city) VALUES
    ('Test', NULL, 'test@null.com', 'NullCity'),
    (NULL, 'NoFirst', 'nofirst@null.com', 'NoFirstCity'),
    ('', 'Empty', 'empty@test.com', 'EmptyCity');

RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 6.1: Query with NULL in second column';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SELECT first_name, last_name, email FROM test_multicolumn 
WHERE first_name LIKE 'Test%';

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 6.2: Query with NULL in first column';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SELECT first_name, last_name, email FROM test_multicolumn 
WHERE last_name LIKE 'NoFirst%';

-- ============================================================
-- TEST 7: Wildcard Pattern Tests
-- ============================================================
DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 7: Complex Wildcard Patterns';
RAISE NOTICE '============================================================';

RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 7.1: Leading wildcard on both columns';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SELECT first_name, last_name FROM test_multicolumn 
WHERE first_name LIKE '%a%' AND last_name LIKE '%a%'
LIMIT 5;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 7.2: Mixed wildcards';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SELECT first_name, last_name FROM test_multicolumn 
WHERE first_name LIKE 'J%' AND last_name LIKE '%n'
LIMIT 5;

DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '------------------------------------------------------------';
RAISE NOTICE 'TEST 7.3: Underscore wildcard';
RAISE NOTICE '------------------------------------------------------------';
END $$;

SELECT first_name, last_name FROM test_multicolumn 
WHERE first_name LIKE 'J___%' AND last_name LIKE '%o%'
LIMIT 5;

-- ============================================================
-- TEST 8: View All Biscuit Indexes
-- ============================================================
DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 8: Summary of All Biscuit Indexes';
RAISE NOTICE '============================================================';
END $$;

SELECT * FROM biscuit_indexes ORDER BY index_name;

-- ============================================================
-- TEST 9: Verify Multi-Column Index Detection
-- ============================================================
DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'TEST 9: Verify Multi-Column Detection in Index Metadata';
RAISE NOTICE '============================================================';
END $$;

SELECT 
    i.indexrelid::regclass AS index_name,
    array_length(i.indkey, 1) AS num_columns,
    array_agg(a.attname ORDER BY array_position(i.indkey, a.attnum)) AS column_names,
    am.amname AS access_method
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE am.amname = 'biscuit'
  AND i.indrelid IN ('test_multicolumn'::regclass, 'test_types_multicolumn'::regclass)
GROUP BY i.indexrelid, i.indkey, am.amname
ORDER BY index_name;

-- ============================================================
-- CLEANUP
-- ============================================================
DO $$
BEGIN
RAISE NOTICE '';
RAISE NOTICE '============================================================';
RAISE NOTICE 'Test Suite Complete!';
RAISE NOTICE '============================================================';
RAISE NOTICE '';
RAISE NOTICE 'To clean up test objects, run:';
RAISE NOTICE '  DROP TABLE test_multicolumn CASCADE;';
RAISE NOTICE '  DROP TABLE test_types_multicolumn CASCADE;';
RAISE NOTICE '  DROP TABLE test_performance CASCADE;';
RAISE NOTICE '';
RAISE NOTICE 'To keep test data for further investigation, skip cleanup.';
RAISE NOTICE '';

END $$;

-- Optional: Uncomment to auto-cleanup
-- DROP TABLE test_multicolumn CASCADE;
-- DROP TABLE test_types_multicolumn CASCADE;
-- DROP TABLE test_performance CASCADE;