-- ============================================
-- BASE DE DATOS TPV HOSTELERÍA - VERSIÓN FINAL
-- ============================================

CREATE DATABASE IF NOT EXISTS tpv_hosteleria CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tpv_hosteleria;

-- Mesas
CREATE TABLE tables (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    capacity INT NOT NULL DEFAULT 4,
    status ENUM('free', 'occupied', 'reserved') DEFAULT 'free',
    current_order_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Productos
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    cost DECIMAL(10,2) DEFAULT 0.00,
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 0,
    printer_id INT DEFAULT 1,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Órdenes/Pedidos
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    user_id INT NOT NULL,
    subtotal DECIMAL(10,2) DEFAULT 0.00,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    total DECIMAL(10,2) DEFAULT 0.00,
    tip DECIMAL(10,2) DEFAULT 0.00,
    final_total DECIMAL(10,2) DEFAULT 0.00,
    payment_method ENUM('cash', 'card', 'bizum') NULL,
    status ENUM('open', 'completed', 'cancelled') DEFAULT 'open',
    closed_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (table_id) REFERENCES tables(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (closed_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Items de las órdenes
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- Cola de cocina (CRITICAL para que la cocina funcione)
CREATE TABLE kitchen_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    table_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    notes TEXT NULL,
    printer_id INT DEFAULT 1,
    status ENUM('pending', 'preparing', 'ready', 'delivered') DEFAULT 'pending',
    priority INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (table_id) REFERENCES tables(id)
) ENGINE=InnoDB;

-- Impresoras
CREATE TABLE printers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    type ENUM('kitchen', 'bar', 'receipt') DEFAULT 'kitchen',
    ip_address VARCHAR(45) NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Reservas
CREATE TABLE reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    customer_email VARCHAR(100) NULL,
    reservation_date DATE NOT NULL,
    reservation_time TIME NOT NULL,
    guests INT NOT NULL,
    notes TEXT NULL,
    status ENUM('confirmed', 'seated', 'cancelled', 'no_show') DEFAULT 'confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (table_id) REFERENCES tables(id)
) ENGINE=InnoDB;

-- Caja registradora
CREATE TABLE cash_register (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash', 'card', 'bizum') NOT NULL,
    tip DECIMAL(10,2) DEFAULT 0.00,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Sesiones de usuario
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Configuración del sistema
CREATE TABLE config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    business_name VARCHAR(100) DEFAULT 'Mi Restaurante',
    address TEXT NULL,
    tax_id VARCHAR(50) NULL,
    phone VARCHAR(20) NULL,
    tax_rate DECIMAL(5,2) DEFAULT 21.00,
    currency VARCHAR(3) DEFAULT 'EUR',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Mesas
INSERT INTO tables (name, capacity, status) VALUES 
('Mesa 1', 4, 'free'),
('Mesa 2', 4, 'free'),
('Mesa 3', 2, 'free'),
('Mesa 4', 2, 'free'),
('Mesa 5', 6, 'free'),
('Mesa 6', 6, 'free'),
('Mesa 7', 4, 'free'),
('Mesa 8', 4, 'free'),
('Barra 1', 2, 'free'),
('Barra 2', 2, 'free');

-- Productos
INSERT INTO products (name, category, price, cost, stock, min_stock, printer_id) VALUES 
-- Bebidas
('Coca-Cola', 'Bebidas', 2.50, 0.80, 100, 20, 2),
('Cerveza', 'Bebidas', 3.00, 1.00, 80, 15, 2),
('Agua', 'Bebidas', 1.50, 0.30, 150, 30, 2),
('Café', 'Bebidas', 1.80, 0.40, 200, 40, 2),
('Vino Tinto', 'Bebidas', 4.50, 1.50, 50, 10, 2),

-- Entrantes
('Patatas Bravas', 'Entrantes', 5.50, 2.00, 50, 10, 1),
('Croquetas (6 uds)', 'Entrantes', 7.00, 2.50, 40, 8, 1),
('Ensalada Mixta', 'Entrantes', 6.50, 2.20, 30, 6, 1),
('Pan con Tomate', 'Entrantes', 3.50, 1.00, 60, 12, 1),

-- Principales
('Hamburguesa Completa', 'Principales', 12.00, 4.50, 30, 6, 1),
('Pizza Margarita', 'Principales', 10.50, 3.80, 25, 5, 1),
('Paella (2 pers)', 'Principales', 24.00, 9.00, 15, 3, 1),
('Filete con Patatas', 'Principales', 15.50, 6.00, 20, 4, 1),

-- Postres
('Tarta de Queso', 'Postres', 5.00, 1.80, 20, 4, 1),
('Helado', 'Postres', 4.50, 1.50, 40, 8, 1),
('Flan Casero', 'Postres', 4.00, 1.20, 25, 5, 1);

-- Impresoras
INSERT INTO printers (name, type) VALUES 
('Cocina Principal', 'kitchen'),
('Barra', 'bar'),
('Tickets Cliente', 'receipt');

-- Configuración inicial
INSERT INTO config (business_name, address, tax_id, phone, tax_rate, currency) VALUES 
('Restaurante El Buen Sabor', 'Calle Principal 123, Málaga', 'B12345678', '952 123 456', 21.00, 'EUR');

-- ============================================
-- ÍNDICES PARA OPTIMIZACIÓN
-- ============================================

CREATE INDEX idx_orders_table ON orders(table_id, status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_closed ON orders(closed_at);
CREATE INDEX idx_kitchen_status ON kitchen_queue(status);
CREATE INDEX idx_reservations_date ON reservations(reservation_date);
CREATE INDEX idx_products_active ON products(active);

-- ============================================
-- FINALIZADO
-- ============================================