USE pagecaptureservice;

-- Clear existing data
DELETE FROM listed_items;
DELETE FROM listings;

-- Insert sample listing
INSERT INTO listings (listing_id, title, archive, issued_date, status, notes) VALUES
('LST-ARCHIVE-001-20241101', 'Wayne County, Michigan - Birth Records (1850-1875)', 'Michigan State Archive', '2024-10-15 08:00:00', 'In Progress', 'Listing approved for capture project P2024-MI-005.');

-- Get the listing ID for foreign key reference
SET @listing_id = LAST_INSERT_ID();

-- Insert sample listed items
INSERT INTO listed_items (listing_fk, item_identifier, listing_description, record_type, start_date, end_date, volume, expected_count, reconciliation_status, reconciliation_notes, captured_folder_id, captured_sequence_number) VALUES
(@listing_id, 'ITEM-LST-001-VOL1', 'Volume 1 - Births 1850-1855', 'Birth certificate', '1850', '1855', '1', 450, 'Captured', 'Captured as folder 004784308. Appears complete.', '004784308', 1),
(@listing_id, 'ITEM-LST-001-VOL2', 'Volume 2 - Births 1856-1860', 'Birth certificate', '1856', '1860', '2', 480, 'Missing', 'Archivist stated volume 2 was temporarily recalled for exhibit. Will attempt recapture later if available.', NULL, NULL),
(@listing_id, 'ITEM-LST-001-VOL3', 'Volume 3 - Births 1861-1865', 'Birth certificate', '1861', '1865', '3', 510, 'Pending Capture', NULL, NULL, NULL),
(@listing_id, 'ITEM-LST-001-VOL4', 'Volume 4 - Births 1866-1870', 'Birth certificate', '1866', '1870', '4', 530, 'Captured', 'Captured as folder 004784309. Notes about torn pages captured in folder metadata.', '004784309', 1);
