-- /database/schema.sql

START TRANSACTION;

-- 2. 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;

-- 3. 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;

-- 4. Órdenes
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)
) ENGINE=InnoDB;

-- 5. Items de Orden
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;

-- 6. 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(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
) ENGINE=InnoDB;

-- 7. Configuración
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;

-- 8. 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
) ENGINE=InnoDB;

-- 9. 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') DEFAULT 'confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (table_id) REFERENCES tables(id)
) ENGINE=InnoDB;

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');

INSERT INTO products (name, category, price, cost, stock, printer_id) VALUES 
('Coca-Cola', 'Bebidas', 2.50, 0.80, 100, 2),
('Cerveza', 'Bebidas', 3.00, 1.00, 80, 2),
('Patatas Bravas', 'Entrantes', 5.50, 2.00, 50, 1),
('Hamburguesa', 'Principales', 12.00, 4.50, 30, 1),
('Tarta Queso', 'Postres', 5.00, 1.80, 20, 1);

INSERT INTO config (business_name, currency) VALUES ('TPV Pro', 'EUR');
INSERT INTO printers (name, type) VALUES ('Cocina', 'kitchen'), ('Barra', 'bar');

COMMIT;