-- Conformance Test Suite for pgproto
-- This suite validates Protobuf wire-format interpretion and mutation logic.
-- 
-- NOTE ON EXPECTED RESULTS:
-- 1. NULL (empty columns): Returned when a field is missing from the wire format 
--    (standard Protobuf behavior) or when a path traversal fails.
-- 2. ERROR: Raised when there is a wire-type mismatch (e.g., accessing a string as int32)
--    or when the schema registry cannot find the requested message type.
-- 3. Accessors: Use #> for integer/numeric extraction and #>> for text/string extraction.

CREATE EXTENSION IF NOT EXISTS pgproto;
CREATE TABLE pb_conformance (id serial, data protobuf);

-- Register conformance schema
-- This schema includes all scalar types, repeated fields (packed/unpacked), maps, oneofs, and nested messages.
INSERT INTO pb_schemas (name, data) VALUES ('conformance.proto', '\x0a8d080a1f746573745f646174615f6e65772f6c6f6e666f726d616e63652e70726f746f120b636f6e666f726d616e636522d4070a0e436f6e666f726d616e63654d736712190a08665f646f75626c65180120012801520766446f75626c6512170a07665f666c6f6174180220012802520666466c6f617412170a07665f696e743634180320012803520666496e74363412190a08665f75696e74363418042001280452076655696e74363412170a07665f696e743332180520012805520666496e743332121b0a09665f6669786564363418062001280652086646697865643634121b0a09665f666978656433321807200128075208664669786564333212150a06665f626f6f6c180820012808520566426f6f6c12190a08665f737472696e67180920012809520766537472696e6712170a07665f6279746573180a2001280c520666427974657312190a08665f75696e743332180b2001280d52076655696e743332121d0a0a665f7366697865643332180c2001280f5209665366697865643332121d0a0a665f7366697865643634180d20012810520966536669786564363412190a08665f73696e743332180e2001281152076653696e74333212190a08665f73696e743634180f2001281252076653696e74363412170a07725f696e743332181020032805520672496e74333212280a0e725f696e7433325f7061636b656418112003280542021001520c72496e7433325061636b656412190a08725f737472696e67181220032809520772537472696e6712440a096d5f7374725f73747218132003280b32282e636f6e666f726d616e63652e436f6e666f726d616e63654d73672e4d537472537472456e74727952076d53747253747212440a096d5f696e745f696e7418142003280b32282e636f6e666f726d616e63652e436f6e666f726d616e63654d73672e4d496e74496e74456e74727952076d496e74496e7412150a05635f696e741815200128054800520463496e7412150a05635f7374721816200128094800520463537472123d0a08665f6e657374656418172001280b32222e636f6e666f726d616e63652e436f6e666f726d616e63654d73672e4e65737465645207664e65737465641a3a0a0c4d537472537472456e74727912100a036b657918012001280952036b657912140a0576616c7565180220012809520576616c75653a0238011a3a0a0c4d496e74496e74456e74727912100a036b657918012001280552036b657912140a0576616c7565180220012805520576616c75653a0238011a540a064e657374656412100a0376616c180120012805520376616c12380a05696e6e657218022001280b32222e636f6e666f726d616e63652e436f6e666f726d616e63654d73672e4e65737465645205696e6e657242080a0663686f696365620670726f746f33');

-- 1. All Scalars Test
-- This test verifies that the scanner correctly interprets all Protobuf wire types
-- and scalar types, including floating point, 64-bit integers, and strings.
INSERT INTO pb_conformance (data) VALUES ('\x09ae47e17a14aef33f1585eb914018d285d8cc0420eaadc0e524282a314e61bc00000000003db17f390540014a0b68656c6c6f20776f726c64520b62696e617279206461746158646585ffffff6938feffffffffffff70a90c78e50f');

SELECT pb_get_int32(data, 5) AS f_int32 FROM pb_conformance WHERE id = 1;
SELECT pb_get_int32(data, 11) AS f_uint32 FROM pb_conformance WHERE id = 1;
SELECT pb_get_int32(data, 8) AS f_bool FROM pb_conformance WHERE id = 1;
SELECT data -> 'conformance.ConformanceMsg.f_string'::text AS f_string FROM pb_conformance WHERE id = 1;

-- 2. Repeated Packed Test
-- Verified path-based access to packed repeated fields. Protobuf uses a single
-- length-delimited blob for packed arrays, which our scanner traverses by index.
INSERT INTO pb_conformance (data) VALUES ('\x8a01030a141e');
-- Path access to packed repeated
SELECT data #> '{conformance.ConformanceMsg, r_int32_packed, 0}'::text[] FROM pb_conformance WHERE id = 2;
SELECT data #> '{conformance.ConformanceMsg, r_int32_packed, 1}'::text[] FROM pb_conformance WHERE id = 2;
SELECT data #> '{conformance.ConformanceMsg, r_int32_packed, 2}'::text[] FROM pb_conformance WHERE id = 2;

-- 3. Maps Test
-- Verifies lookup of keys in both string-keyed and integer-keyed maps.
-- Map entries are encoded as submessages containing 'key' (tag 1) and 'value' (tag 2).
-- Note: String values require the #>> operator (text extractor).
INSERT INTO pb_conformance (data) VALUES ('\x9a01080a026b31120276319a01080a026b3212027632a2010408011064');
SELECT data #>> '{conformance.ConformanceMsg, m_str_str, k1}'::text[] FROM pb_conformance WHERE id = 3;
SELECT data #>> '{conformance.ConformanceMsg, m_str_str, k2}'::text[] FROM pb_conformance WHERE id = 3;
SELECT data #> '{conformance.ConformanceMsg, m_int_int, 1}'::text[] FROM pb_conformance WHERE id = 3;

-- 4. Oneof Test
-- Verifies that setting one field in a 'oneof' correctly overrides others.
-- Our scanner returns NULL for fields in a oneof that are not present in the wire format.
INSERT INTO pb_conformance (data) VALUES ('\xb2010b6f6e656f665f76616c7565');
SELECT data #>> '{conformance.ConformanceMsg, c_str}'::text[] FROM pb_conformance WHERE id = 4;
SELECT data #> '{conformance.ConformanceMsg, c_int}'::text[] FROM pb_conformance WHERE id = 4; -- Returns NULL as expected (not in wire)

-- 5. Nested Message Test
-- Verifies recursive traversal of nested messages. The scanner nests its depth
-- by following the length-delimited submessage boundaries.
INSERT INTO pb_conformance (data) VALUES ('\xba0107087b120308c803');
SELECT data #> '{conformance.ConformanceMsg, f_nested, val}'::text[] FROM pb_conformance WHERE id = 5;
SELECT data #> '{conformance.ConformanceMsg, f_nested, inner, val}'::text[] FROM pb_conformance WHERE id = 5;

-- 6. Compaction & Bloat Verification
-- Original size of id=1
SELECT length(data::bytea) AS original_size FROM pb_conformance WHERE id = 1;
-- Multiple updates to same field
UPDATE pb_conformance SET data = pb_set(data, ARRAY['conformance.ConformanceMsg', 'f_int32'], '100') WHERE id = 1;
UPDATE pb_conformance SET data = pb_set(data, ARRAY['conformance.ConformanceMsg', 'f_int32'], '200') WHERE id = 1;
UPDATE pb_conformance SET data = pb_set(data, ARRAY['conformance.ConformanceMsg', 'f_int32'], '300') WHERE id = 1;
-- Verify size remains constant (or even shrinks if 300 takes same space as 42)
SELECT length(data::bytea) AS final_size FROM pb_conformance WHERE id = 1;
-- Verify value
SELECT pb_get_int32(data, 5) FROM pb_conformance WHERE id = 1;

-- 7. Deletion & Compaction
UPDATE pb_conformance SET data = pb_delete(data, ARRAY['conformance.ConformanceMsg', 'f_string']) WHERE id = 1;
SELECT length(data::bytea) AS size_after_delete FROM pb_conformance WHERE id = 1;
SELECT data -> 'conformance.ConformanceMsg.f_string'::text FROM pb_conformance WHERE id = 1;
