-- ============================================
-- BASE DE DATOS TPV HOSTELERÍA PROFESIONAL
-- ============================================

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

-- ============================================
-- TABLA: user_sessions (Registro de sesiones)
-- ============================================
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    login_time DATETIME NOT NULL,
    logout_time DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

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

-- Mesas de ejemplo
INSERT INTO tables (name, capacity, zone, status) VALUES
('Mesa 1', 4, 'terraza', 'free'),
('Mesa 2', 4, 'terraza', 'free'),
('Mesa 3', 2, 'interior', 'free'),
('Mesa 4', 2, 'interior', 'free'),
('Mesa 5', 6, 'interior', 'free'),
('Mesa 6', 6, 'interior', 'free'),
('Mesa 7', 4, 'barra', 'free'),
('Mesa 8', 4, 'barra', 'free');

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

-- Impresoras por defecto
INSERT INTO printers (name, type, ip_address, active) VALUES
('Cocina Principal', 'kitchen', '192.168.1.100', 1),
('Barra', 'bar', '192.168.1.101', 1),
('Tickets', 'receipt', '192.168.1.102', 1);

-- ============================================
-- TABLA: products (Productos del menú)
-- ============================================
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    cost DECIMAL(10,2) DEFAULT 0,
    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,
    FOREIGN KEY (printer_id) REFERENCES printers(id)
) ENGINE=InnoDB;

-- Productos de ejemplo
INSERT INTO products (name, category, price, cost, stock, printer_id, active) VALUES
-- BEBIDAS (Barra)
('Cerveza Estrella Galicia', 'Bebidas', 2.50, 0.80, 100, 2, 1),
('Vino Tinto Copa', 'Bebidas', 3.00, 1.20, 50, 2, 1),
('Coca Cola', 'Bebidas', 2.00, 0.60, 100, 2, 1),
('Agua Mineral', 'Bebidas', 1.50, 0.40, 100, 2, 1),
('Café Solo', 'Bebidas', 1.20, 0.30, 200, 2, 1),
('Café con Leche', 'Bebidas', 1.50, 0.40, 200, 2, 1),

-- ENTRANTES (Cocina)
('Croquetas Caseras (6 uds)', 'Entrantes', 8.50, 3.00, 50, 1, 1),
('Jamón Ibérico', 'Entrantes', 18.00, 9.00, 20, 1, 1),
('Patatas Bravas', 'Entrantes', 6.00, 2.00, 50, 1, 1),
('Ensalada Mixta', 'Entrantes', 7.50, 2.50, 50, 1, 1),
('Pulpo a la Gallega', 'Entrantes', 16.00, 8.00, 15, 1, 1),

-- PRINCIPALES (Cocina)
('Paella Valenciana', 'Principales', 14.00, 6.00, 30, 1, 1),
('Entrecot de Ternera', 'Principales', 18.00, 10.00, 20, 1, 1),
('Lubina a la Plancha', 'Principales', 15.00, 8.00, 15, 1, 1),
('Pizza Margarita', 'Principales', 9.00, 3.00, 40, 1, 1),
('Hamburguesa Completa', 'Principales', 10.00, 4.00, 30, 1, 1),

-- POSTRES (Cocina)
('Tarta de Queso', 'Postres', 5.50, 2.00, 20, 1, 1),
('Flan Casero', 'Postres', 4.00, 1.50, 30, 1, 1),
('Helado (3 bolas)', 'Postres', 4.50, 1.80, 50, 1, 1),
('Tiramisú', 'Postres', 5.00, 2.20, 15, 1, 1);

-- ============================================
-- TABLA: orders (Pedidos)
-- ============================================
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    user_id INT NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    tip DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    final_total DECIMAL(10,2) DEFAULT 0,
    status ENUM('open', 'completed', 'cancelled') DEFAULT 'open',
    payment_method ENUM('cash', 'card', 'bizum') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    closed_at DATETIME NULL,
    closed_by INT NULL,
    cancelled_at DATETIME NULL,
    cancelled_by INT NULL,
    cancel_reason TEXT NULL,
    FOREIGN KEY (table_id) REFERENCES tables(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (closed_by) REFERENCES users(id),
    FOREIGN KEY (cancelled_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================
-- TABLA: order_items (Productos del pedido)
-- ============================================
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(150) 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;

-- ============================================
-- TABLA: kitchen_queue (Cola de cocina)
-- ============================================
CREATE TABLE kitchen_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    table_id INT NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    quantity INT NOT NULL,
    notes TEXT NULL,
    printer_id INT NOT NULL,
    status ENUM('pending', 'preparing', 'ready', 'delivered') DEFAULT 'pending',
    priority INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (table_id) REFERENCES tables(id),
    FOREIGN KEY (printer_id) REFERENCES printers(id)
) ENGINE=InnoDB;

-- ============================================
-- TABLA: cash_register (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,
    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;

-- ============================================
-- TABLA: config (Configuración del negocio)
-- ============================================
CREATE TABLE config (
    id INT PRIMARY KEY DEFAULT 1,
    business_name VARCHAR(200) NOT NULL,
    address TEXT NULL,
    tax_id VARCHAR(50) NULL,
    phone VARCHAR(50) NULL,
    tax_rate DECIMAL(5,2) DEFAULT 21.00,
    currency VARCHAR(10) DEFAULT 'EUR',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Configuración por defecto
INSERT INTO config (id, business_name, address, tax_id, phone, tax_rate, currency) VALUES
(1, 'Restaurante Demo', 'Calle Principal 123, Melilla', 'B12345678', '952 123 456', 21.00, 'EUR');

-- ============================================
-- ÍNDICES PARA OPTIMIZACIÓN
-- ============================================
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_table ON orders(table_id, status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_kitchen_status ON kitchen_queue(status);
CREATE INDEX idx_cash_date ON cash_register(created_at);
CREATE INDEX idx_products_category ON products(category, active);

-- Vista de productos más vendidos
CREATE VIEW v_top_products AS
SELECT 
    oi.product_name,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.price * oi.quantity) as total_revenue,
    COUNT(DISTINCT oi.order_id) as times_ordered
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY oi.product_name
ORDER BY total_quantity DESC;

-- ============================================
-- DATOS DE PRUEBA (OPCIONAL)
-- ============================================

-- Pedido de ejemplo en mesa 1
INSERT INTO orders (table_id, user_id, subtotal, total, status) VALUES (1, 2, 25.00, 25.00, 'open');
SET @order_id = LAST_INSERT_ID();

UPDATE tables SET status = 'occupied', current_order_id = @order_id WHERE id = 1;

INSERT INTO order_items (order_id, product_id, product_name, price, quantity) VALUES
(@order_id, 1, 'Cerveza Estrella Galicia', 2.50, 2),
(@order_id, 7, 'Croquetas Caseras (6 uds)', 8.50, 1),
(@order_id, 12, 'Paella Valenciana', 14.00, 1);

INSERT INTO kitchen_queue (order_id, table_id, product_name, quantity, printer_id, status) VALUES
(@order_id, 1, 'Croquetas Caseras (6 uds)', 1, 1, 'pending'),
(@order_id, 1, 'Paella Valenciana', 1, 1, 'pending'),
(@order_id, 1, 'Cerveza Estrella Galicia', 2, 2, 'pending');

-- ============================================
-- PRIVILEGIOS (Ejecutar si es necesario)
-- ============================================
-- GRANT ALL PRIVILEGES ON tpv_hosteleria.* TO 'tu_usuario'@'localhost';
-- FLUSH PRIVILEGES;

SELECT 'Base de datos TPV Hostelería creada correctamente' as status;
