CREATE EXTENSION pgproto;

-- Create table with custom type
CREATE TABLE pb_test (id serial, data protobuf);

-- Register test schema first to avoid session caching issues in tests
INSERT INTO pb_schemas (name, data) VALUES ('test_data_new/test.proto', decode('0AFE010A12736372617463682F746573742E70726F746F22170A05496E6E6572120E0A0269641801200128055202696422C6010A054F75746572121C0A05696E6E657218012001280B32062E496E6E65725205696E6E657212240A047461677318022003280B32102E4F757465722E54616773456E74727952047461677312160A0673636F726573180320032805520673636F726573120E0A01611804200128054800520161120E0A016218052001280948005201621A370A0954616773456E74727912100A036B657918012001280952036B657912140A0576616C7565180220012805520576616C75653A02380142080A0663686F696365620670726F746F33', 'hex'));
INSERT INTO pb_schemas (name, data) VALUES ('test_data_new/coverage_test.proto', decode('0ae3020a13636f7665726167655f746573742e70726f746f22c3020a0b436f7665726167654d7367120c0a0166180120012802520166120c0a016218022001280852016212170a077374725f617272180320032809520673747241727212310a077374725f6d617018042003280b32182e436f7665726167654d73672e5374724d6170456e74727952067374724d6170121b0a09666c6f61745f6172721805200328025208666c6f617441727212370a09666c6f61745f6d617018062003280b321a2e436f7665726167654d73672e466c6f61744d6170456e7472795208666c6f61744d61701a390a0b5374724d6170456e74727912100a036b657918012001280952036b657912140a0576616c7565180220012809520576616c75653a0238011a3b0a0d466c6f61744d6170456e74727912100a036b657918012001280952036b657912140a0576616c7565180220012802520576616c75653a023801620670726f746f33', 'hex'));


-- Insert valid protobuf (Tag 1, Varint 42 => 0x08 0x2a)
INSERT INTO pb_test (data) VALUES ('\x082a');

-- Select it back (Hex output)
SELECT data FROM pb_test;

-- Extract Tag 1
SELECT pb_get_int32(data, 1) FROM pb_test;

-- Extract Tag 2 (it doesn't exist, should return NULL)
SELECT pb_get_int32(data, 2) FROM pb_test;

-- Insert multi-field protobuf: Tag 1 = 42, Tag 2 = Varint 120 (0x10 0x78)
-- Payload: 0x08 0x2a 0x10 0x78
INSERT INTO pb_test (data) VALUES ('\x082a1078');

-- Extract from multi-field
SELECT pb_get_int32(data, 1) AS tag1, pb_get_int32(data, 2) AS tag2 FROM pb_test WHERE id = 2;

-- Negative test for name-based extraction (should fail because message is not registered or found)
-- We wrap it in a function or just run it and expect error. In pg_regress, we can just run it and let the output match expected errors!
SELECT pb_get_int32_by_name(data, 'UnregisteredMessage', 'id') FROM pb_test WHERE id = 1;

-- Test shorthand operator -> (Dot Notation)
SELECT data -> 'UnregisteredMessage.id'::text FROM pb_test WHERE id = 1;


-- Test GIN Indexing (Purity)
CREATE TABLE pb_test_gin (id serial, data protobuf);

-- Insert row with Tag 1 = 42
INSERT INTO pb_test_gin (data) VALUES ('\x082a');

-- Insert row with Tag 1 = 100
INSERT INTO pb_test_gin (data) VALUES ('\x0864');

-- Create GIN index
CREATE INDEX pb_gin_idx ON pb_test_gin USING gin (data);

-- Verify contains operator @> sequentially (or via index if small enough)
SELECT * FROM pb_test_gin WHERE data @> '\x082a'::protobuf;


-- Nested Path Navigation Test

-- Insert valid nested protobuf (Outer.inner.id = 42)
INSERT INTO pb_test (data) VALUES ('\x0a02082a'::protobuf);

-- Test #> with valid path
SELECT data #> '{Outer, inner, id}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test #> with invalid path (unknown field)
SELECT data #> '{Outer, inner, unknown}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test #> with invalid message name
SELECT data #> '{UnknownMessage, id}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;-- Map and Repeated Field Navigation Tests

-- Insert expanded protobuf data
-- Text format: inner { id: 42 } tags { key: "foo", value: 100 } tags { key: "bar", value: 200 } scores: 10 scores: 20 choice { a: 30 }
-- Hex: \x0A02082A12070A03666F6F106412080A0362617210C8011A020A14201E
INSERT INTO pb_test (data) VALUES ('\x0A02082A12070A03666F6F106412080A0362617210C8011A020A14201E'::protobuf);

-- Test array index (scores[0] => 10)
SELECT data #> '{Outer, scores, 0}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test array index (scores[1] => 20)
SELECT data #> '{Outer, scores, 1}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test map key lookup (tags["foo"] => 100)
SELECT data #> '{Outer, tags, foo}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test map key lookup (tags["bar"] => 200)
SELECT data #> '{Outer, tags, bar}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test oneof (choice a => 30)
SELECT data #> '{Outer, a}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Edge Case: Out of bounds array index
SELECT data #> '{Outer, scores, 5}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Edge Case: Typo in map key (should return NULL / empty)
SELECT data #> '{Outer, tags, unknown_key}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Edge Case: Invalid path length (one element only - message name, should return NULL if we expect field)
SELECT data #> '{Outer}'::text[] FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Edge Case: Corrupt protobuf binary (short read / truncated)
INSERT INTO pb_test (data) VALUES ('\x0A0208'::protobuf); -- Truncated inner message
SELECT pb_get_int32(data, 1) FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;


-- 📈 Additional Coverage Tests

-- 1. Test pb_to_json
SELECT pb_to_json(data, 'Outer'::text) FROM pb_test WHERE id = 1;
SELECT pb_to_json(data, 'Outer'::text) FROM pb_test WHERE id = 3;

-- 2. Test pb_register_schema (Explicit)
SELECT pb_register_schema('test_data_new/explicit_test.proto', decode('0AFE010A12736372617463682F746573742E70726F746F22170A05496E6E6572120E0A0269641801200128055202696422C6010A054F75746572121C0A05696E6E657218012001280B32062E496E6E65725205696E6E657212240A047461677318022003280B32102E4F757465722E54616773456E74727952047461677312160A0673636F726573180320032805520673636F726573120E0A01611804200128054800520161120E0A016218052001280948005201621A370A0954616773456E74727912100A036B657918012001280952036B657912140A0576616C7565180220012805520576616C75653A02380142080A0663686F696365620670726F746F33', 'hex'));

-- 3. Test Navigation Edge cases & Error Paths
SELECT pb_get_int32(data, 100) FROM pb_test WHERE id = 1; -- Tag not in message
SELECT data -> 'Outer.inner'::text FROM pb_test WHERE id = 1; -- Field is a message, not a primitive
SELECT data -> 'Outer..id'::text FROM pb_test WHERE id = 1; -- Empty field name
SELECT data -> 'Outer.unknown_field'::text FROM pb_test WHERE id = 1; -- Unknown field


-- 📊 GIN Coverage Tests

-- Insert multiple wire types
-- 0x09: Tag 1, Wire 1 (64-bit) => \x090102030405060708
INSERT INTO pb_test_gin (data) VALUES ('\x090102030405060708');
-- 0x0a: Tag 1, Wire 2 (Length-delimited) => \x0a03666f6f
INSERT INTO pb_test_gin (data) VALUES ('\x0a03666f6f');
-- 0x0d: Tag 1, Wire 5 (32-bit) => \x0d01020304
INSERT INTO pb_test_gin (data) VALUES ('\x0d01020304');

-- Force GIN index scan to trigger gin_consistent and query extractors
SET enable_seqscan = off;

-- Re-run query to hit GIN index consistent logic
SELECT * FROM pb_test_gin WHERE data @> '\x082a'::protobuf;

-- Multi-key query to trigger consistent looping (And query extraction)
-- Query matches Tag 1=42 AND Tag 2=120
SELECT * FROM pb_test_gin WHERE data @> '\x082a1078'::protobuf;

-- Reset seqscan setting
RESET enable_seqscan;

-- Re-allocate logic (Message with > 8 fields)
INSERT INTO pb_test_gin (data) VALUES ('\x0801\x1002\x1803\x2004\x2805\x3006\x3807\x4008\x4809\x500a');
SELECT * FROM pb_test_gin WHERE data @> '\x0801'::protobuf;


-- 🧭 Navigation Skipping & Contains Edge Cases

-- Test skipping Length-delimited field (Field 2) to get Field 3
-- Model: Field 1 (Varint), Field 2 (Length-delimited), Field 3 (Varint)
-- Payload: \x0801 (Tag 1=1) \x1203666f6f (Tag 2="foo") \x182a (Tag 3=42)
INSERT INTO pb_test (data) VALUES ('\x08011203666f6f182a'::protobuf);
SELECT pb_get_int32(data, 3) FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test skipping Fixed64 field (Field 1) to get Field 2
-- Payload: \x090102030405060708 (Tag 1=Fixed64) \x102a (Tag 2=42)
INSERT INTO pb_test (data) VALUES ('\x090102030405060708102a'::protobuf);
SELECT pb_get_int32(data, 2) FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test skipping Fixed32 field (Field 1) to get Field 2
-- Payload: \x0d01020304 (Tag 1=Fixed32) \x102a (Tag 2=42)
INSERT INTO pb_test (data) VALUES ('\x0d01020304102a'::protobuf);
SELECT pb_get_int32(data, 2) FROM (SELECT data FROM pb_test ORDER BY id DESC LIMIT 1) s;

-- Test contains (@>) with non-Varint query
SELECT * FROM pb_test_gin WHERE data @> '\x0a03666f6f'::protobuf; -- Length-delimited string "foo"
SELECT * FROM pb_test_gin WHERE data @> '\x090102030405060708'::protobuf; -- Fixed64
SELECT * FROM pb_test_gin WHERE data @> '\x0d01020304'::protobuf; -- Fixed32


-- 🎯 Name-based Success Paths & Dot Skipping

-- Test pb_get_int32_by_name success
SELECT pb_get_int32_by_name(data, 'Inner', 'id') FROM pb_test WHERE id = 1;

-- Test pb_get_int32_by_name_dot success and skipping
-- Querying 'Outer.a' (Tag 4) on row 4 which has inner, tags, scores before it.
SELECT data -> 'Outer.a'::text FROM pb_test WHERE id = 4;


-- 🧩 Path Navigation Edge Cases & Error Paths

-- 1. Access beyond primitive in array (should error)
SELECT data #> '{Outer, scores, 0, sub_field}'::text[] FROM pb_test WHERE id = 4;

-- 2. Access beyond primitive in map (should error)
SELECT data #> '{Outer, tags, foo, sub_field}'::text[] FROM pb_test WHERE id = 4;

-- 3. Query submessage as primitive (expects varint, gets length-delimited => should error)
SELECT data #> '{Outer, inner}'::text[] FROM pb_test WHERE id = 4;

-- 4. Query invalid wire type for submessage (expects length-delimited, gets varint => should error)
SELECT data #> '{Outer, inner, id}'::text[] FROM pb_test WHERE id = 1;


-- 📊 JSON Error Coverage Tests

-- 1. Unregistered message name
SELECT pb_to_json(data, 'UnregisteredMessage'::text) FROM pb_test WHERE id = 1;

-- 2. Corrupt binary data (Truncated message)
SELECT pb_to_json('\x0A0208'::protobuf, 'Outer'::text);


-- 🎯 Dynamic Registration Success & Querying
-- 1. Register NewOuter schema dynamically
SELECT pb_register_schema('test_data_new/new_test.proto'::text, decode('0a6f0a1c746573745f646174615f6e65772f6e65775f746573742e70726f746f221a0a084e6577496e6e6572120e0a02696418012001280552026964222b0a084e65774f75746572121f0a05696e6e657218012001280b32092e4e6577496e6e65725205696e6e6572620670726f746f33', 'hex'));

-- 2. Query NewOuter using JSON conversion
SELECT pb_to_json('\x0a02082a'::protobuf, 'NewOuter'::text);

-- 3. Query NewOuter using navigation
SELECT '\x0a02082a'::protobuf -> 'NewOuter.inner.id'::text;


-- 🧭 Name-based Skipping of Fixed types & Unsupported Wire Types
-- 1. Register MixedFields schema
SELECT pb_register_schema('test_data_new/mixed_test.proto'::text, decode('0abc010a1e746573745f646174615f6e65772f6d697865645f746573742e70726f746f221c0a0a4d69786564496e6e6572120e0a02696418012001280552026964222f0a0a4d697865644f7574657212210a05696e6e657218012001280b320b2e4d69786564496e6e65725205696e6e657222430a0b4d697865644669656c647312100a03663634180120012806520366363412100a03663332180220012807520366333212100a0376616c180320012805520376616c620670726f746f33', 'hex'));

-- 🧭 Name-based Skipping of All Wire Types
-- 1. Query 'val' (Tag 3) by name, skipping f64 (Tag 1, Wire 1) and f32 (Tag 2, Wire 5)
SELECT pb_get_int32_by_name('\x0901000000000000001502000000182a'::protobuf, 'MixedFields', 'val');

-- 2. Query 'val' (Tag 3) by name, skipping v0 (Tag 1, Wire 0) and v1 (Tag 2, Wire 2)
SELECT pb_get_int32_by_name('\x082a120568656c6c6f1864'::protobuf, 'SkipFields', 'val');

-- 3. Test Unsupported Wire Type error (Wire type 3) in pb_get_int32
SELECT pb_get_int32('\x0b0102'::protobuf, 1);

-- 🛠️ Mutation Tests
-- 1. Test pb_set for int32 field 'a' in Outer
SELECT pb_to_json(pb_set(data, ARRAY['Outer', 'a'], '123'), 'Outer') FROM pb_test WHERE id = 1;

-- 2. Test pb_set for string field 'b' in Outer (oneof override)
SELECT pb_to_json(pb_set(data, ARRAY['Outer', 'b'], 'hello_world'), 'Outer') FROM pb_test WHERE id = 1;

-- 🛠️ pb_insert Tests
-- 1. Test pb_insert for array field 'scores' in Outer (append to empty)
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '0'], '100'), 'Outer');

-- 2. Test pb_insert for array field 'scores' in Outer (insert at middle)
SELECT pb_to_json(pb_insert(pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '0'], '100'), ARRAY['Outer', 'scores', '0'], '50'), 'Outer');

-- 3. Test pb_insert for map field 'tags' in Outer (insert new key)
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'key1'], '200'), 'Outer');

-- 4. Test pb_insert for map field 'tags' in Outer (error on existing key)
SELECT pb_to_json(pb_insert(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'key1'], '200'), ARRAY['Outer', 'tags', 'key1'], '300'), 'Outer');

-- 5. Test pb_insert error: Array index out of bounds
SELECT pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '1'], '100');

-- 6. Test pb_insert error: Field not found
SELECT pb_insert('\x'::protobuf, ARRAY['Outer', 'nonexistent', '0'], '100');

-- 7. Test pb_insert error: Not a repeated or map field
SELECT pb_insert('\x'::protobuf, ARRAY['Outer', 'inner', '0'], '100');

-- 8. Test pb_delete for array field 'scores' in Outer
SELECT pb_to_json(pb_delete(pb_insert(pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '0'], '100'), ARRAY['Outer', 'scores', '1'], '200'), ARRAY['Outer', 'scores', '0']), 'Outer');

-- 9. Test pb_delete for map field 'tags' in Outer
SELECT pb_to_json(pb_delete(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'key1'], '200'), ARRAY['Outer', 'tags', 'key1']), 'Outer');

-- 10. Test pb_delete to clear field
SELECT pb_to_json(pb_delete(pb_set('\x'::protobuf, ARRAY['Outer', 'a'], '42'), ARRAY['Outer', 'a']), 'Outer');

-- 11. Test pb_merge (||) for scalar fields
SELECT pb_to_json(pb_set('\x'::protobuf, ARRAY['Outer', 'a'], '10') || pb_set('\x'::protobuf, ARRAY['Outer', 'a'], '20'), 'Outer');

-- 12. Test pb_merge (||) for arrays (append)
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '0'], '10') || pb_insert('\x'::protobuf, ARRAY['Outer', 'scores', '0'], '20'), 'Outer');

-- 13. Test pb_merge (||) for maps (different keys)
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'k1'], '100') || pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'k2'], '200'), 'Outer');

-- 14. Test pb_merge (||) for maps (overlapping keys)
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'k1'], '100') || pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'k1'], '200'), 'Outer');

-- 15. Test pb_delete error: Array index out of bounds
SELECT pb_delete('\x'::protobuf, ARRAY['Outer', 'scores', '0']);

-- 16. Test pb_delete error: Not a repeated or map field
SELECT pb_delete('\x'::protobuf, ARRAY['Outer', 'a', '0']);

-- 17. Test pb_delete error: Field not found
SELECT pb_delete('\x'::protobuf, ARRAY['Outer', 'nonexistent']);

-- 18. Test pb_set error: Unsupported type for modification
SELECT pb_set('\x'::protobuf, ARRAY['Outer', 'tags'], 'some_value');

-- 19. Test pb_set for float field 'f' in CoverageMsg
SELECT pb_to_json(pb_set('\x'::protobuf, ARRAY['CoverageMsg', 'f'], '1.23'), 'CoverageMsg');

-- 20. Test pb_set for bool field 'b' in CoverageMsg (true)
SELECT pb_to_json(pb_set('\x'::protobuf, ARRAY['CoverageMsg', 'b'], 'true'), 'CoverageMsg');

-- 21. Test pb_set for bool field 'b' in CoverageMsg (false)
SELECT pb_to_json(pb_set('\x'::protobuf, ARRAY['CoverageMsg', 'b'], 'false'), 'CoverageMsg');

-- 22. Test pb_set for bool field 'b' in CoverageMsg (invalid)
SELECT pb_set('\x'::protobuf, ARRAY['CoverageMsg', 'b'], 'invalid');

-- 23. Test pb_insert for string array 'str_arr' in CoverageMsg
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['CoverageMsg', 'str_arr', '0'], 'hello'), 'CoverageMsg');

-- 24. Test pb_insert for string map 'str_map' in CoverageMsg
SELECT pb_to_json(pb_insert('\x'::protobuf, ARRAY['CoverageMsg', 'str_map', 'key1'], 'value1'), 'CoverageMsg');

-- 25. Test pb_set error: Decode failure (invalid data)
SELECT pb_set('\xff'::protobuf, ARRAY['CoverageMsg', 'f'], '1.23');

-- 26. Test pb_set error: Invalid path length (too short)
SELECT pb_set('\x'::protobuf, ARRAY['CoverageMsg'], '123');

-- 27. Test pb_insert error: Invalid path length (too short)
SELECT pb_insert('\x'::protobuf, ARRAY['CoverageMsg', 'str_arr'], '123');

-- 28. Test pb_delete error: Invalid path length (too short)
SELECT pb_delete('\x'::protobuf, ARRAY['CoverageMsg']);

-- 29. Test pb_insert error: Unsupported type for array insertion
SELECT pb_insert('\x'::protobuf, ARRAY['CoverageMsg', 'float_arr', '0'], '1.23');

-- 30. Test pb_insert error: Unsupported map value type
SELECT pb_insert('\x'::protobuf, ARRAY['CoverageMsg', 'float_map', 'key1'], '1.23');

-- 31. Test pb_get_int32_by_path with field not found (returns NULL)
SELECT pb_get_int32_by_path('\x'::protobuf, ARRAY['Outer', 'nonexistent']);

-- 32. Test pb_get_int32_by_path error: Map value traversal beyond int32 not supported
SELECT pb_get_int32_by_path(pb_insert('\x'::protobuf, ARRAY['Outer', 'tags', 'key'], '100'), ARRAY['Outer', 'tags', 'key', 'something_else']);

-- 33. Test pb_set error: Empty path
SELECT pb_set('\x'::protobuf, ARRAY[]::text[], '123');

-- 34. Test pb_set error: Field not found
SELECT pb_set('\x'::protobuf, ARRAY['CoverageMsg', 'nonexistent'], '123');

-- 35. Test pb_insert error: Message not found
SELECT pb_insert('\x'::protobuf, ARRAY['NonExistentMessage', 'f', '0'], '1.23');