-- Create the lostandfound table
CREATE TABLE lostandfound (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Unique identifier for each record
    date_of_submission DATE NOT NULL, -- Date the form was submitted
    item_type ENUM('Credit/Debit Card', 'Wallet/Purse', 'ID', 'Cash', 'Sunglasses') NOT NULL, -- Type of item
    name_on_card VARCHAR(255), -- Name on the card (only for credit/debit card or ID)
    card_type ENUM('Visa', 'MasterCard', 'Discover', 'Amex', 'Target', 'SNAP/EBT'), -- Type of card (only for credit/debit card)
    id_type ENUM('Drivers License', 'Passport/Passport Card'), -- Type of ID (only for ID)
    name VARCHAR(255), -- Name (optional for wallet/purse)
    amount DECIMAL(10, 2), -- Amount of cash (only for cash)
    brand VARCHAR(255), -- Brand (only for purse/wallet or sunglasses)
    notes TEXT -- Additional notes
);

-- Example entries to test the table
INSERT INTO lostandfound (date_of_submission, item_type, name_on_card, card_type, notes)
VALUES 
('2024-11-15', 'Credit/Debit Card', 'John Doe', 'Visa', 'Card found near Register 1.');

INSERT INTO lostandfound (date_of_submission, item_type, id_type, name, notes)
VALUES 
('2024-11-15', 'ID', 'Drivers License', 'Jane Doe', 'ID found at the entrance.');

INSERT INTO lostandfound (date_of_submission, item_type, amount, notes)
VALUES 
('2024-11-15', 'Cash', 100.00, 'Cash found in the parking lot.');

INSERT INTO lostandfound (date_of_submission, item_type, brand, notes)
VALUES 
('2024-11-15', 'Sunglasses', 'Ray-Ban', 'Sunglasses left at the food court.');
