-- Crear base de datos
CREATE DATABASE IF NOT EXISTS recepciontools CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE recepciontools;

-- Tabla de objetos olvidados
CREATE TABLE IF NOT EXISTS lost_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    receptionist_name VARCHAR(50) NOT NULL CHECK (receptionist_name IN ('Jose', 'Isla', 'Joselyn', 'Mariam')),
    item_name VARCHAR(255) NOT NULL,
    item_description TEXT,
    location_found VARCHAR(100) NOT NULL,
    room_number VARCHAR(20),
    reservation_number VARCHAR(50),
    guest_name VARCHAR(100),
    delivered_by VARCHAR(50) NOT NULL,
    found_date DATETIME NOT NULL,
    checkin_date DATE,
    checkout_date DATE,
    status ENUM('pendiente', 'entregado', 'almacenado') DEFAULT 'pendiente',
    delivered_to VARCHAR(100),
    delivery_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_room (room_number),
    INDEX idx_guest (guest_name),
    INDEX idx_reservation (reservation_number),
    INDEX idx_found_date (found_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar datos de ejemplo
INSERT INTO lost_items (
    receptionist_name, 
    item_name, 
    item_description, 
    location_found, 
    room_number, 
    reservation_number, 
    guest_name, 
    delivered_by, 
    found_date, 
    checkin_date, 
    checkout_date, 
    status
) VALUES 
('Jose', 'iPhone 14 Pro', 'Teléfono negro con funda transparente', 'Habitación 302', '302', 'RES-2023-00123', 'Juan Pérez', 'Camarista', NOW(), '2023-10-15', '2023-10-20', 'pendiente'),
('Isla', 'Laptop Dell', 'Laptop plateada con stickers de viaje', 'Sala de conferencias', NULL, NULL, NULL, 'Bellboy', DATE_SUB(NOW(), INTERVAL 2 DAY), NULL, NULL, 'almacenado'),
('Joselyn', 'Pasaporte', 'Pasaporte mexicano a nombre de María González', 'Recepción', NULL, 'RES-2023-00145', 'María González', 'Camarista', DATE_SUB(NOW(), INTERVAL 1 DAY), '2023-10-18', '2023-10-22', 'entregado');