

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    printer_zone ENUM('bar', 'kitchen') DEFAULT 'bar'
);

CREATE TABLE IF NOT EXISTS tables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status ENUM('free', 'occupied', 'waiting_payment') DEFAULT 'free',
    current_order_id INT NULL
);

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_id INT NOT NULL,
    status ENUM('open', 'completed', 'cancelled') DEFAULT 'open',
    total DECIMAL(10,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT DEFAULT 1,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

INSERT INTO products (name, price, category, printer_zone) VALUES 
('Caña Mahou', 2.50, 'bebidas', 'bar'),
('Vino Rioja', 3.50, 'bebidas', 'bar'),
('Bravas', 6.50, 'entrantes', 'kitchen'),
('Croquetas', 8.00, 'entrantes', 'kitchen'),
('Hamburguesa', 12.50, 'platos', 'kitchen'),
('Entrecot', 18.00, 'platos', 'kitchen'),
('Café Solo', 1.50, 'cafe', 'bar'),
('Tarta Queso', 6.00, 'postres', 'kitchen');

INSERT INTO tables (name) VALUES 
('Mesa 1'), ('Mesa 2'), ('Mesa 3'), ('Mesa 4'),
('Mesa 5'), ('Mesa 6'), ('Mesa 7'), ('Mesa 8');