CREATE DATABASE IF NOT EXISTS pagecaptureservice;

USE pagecaptureservice;

CREATE TABLE IF NOT EXISTS listings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    archive VARCHAR(255) NOT NULL,
    issued_date DATETIME NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS listed_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_fk INT NOT NULL,
    item_identifier VARCHAR(255) NOT NULL,
    listing_description TEXT NOT NULL,
    record_type VARCHAR(100) NOT NULL,
    start_date VARCHAR(10),
    end_date VARCHAR(10),
    volume VARCHAR(50),
    expected_count INT,
    reconciliation_status VARCHAR(100),
    reconciliation_notes TEXT,
    captured_folder_id VARCHAR(255),
    captured_sequence_number INT,
    FOREIGN KEY (listing_fk) REFERENCES listings(id) ON DELETE CASCADE
);
