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

USE parcheb;

-- Tabla de pedidos (orders)
CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(50) PRIMARY KEY,
  type ENUM('mesa', 'domicilio', 'para-llevar') NOT NULL,
  table_number INT NULL,
  delivery_name VARCHAR(255) NULL,
  delivery_address VARCHAR(500) NULL,
  total DECIMAL(10, 2) NOT NULL,
  status ENUM('pendiente', 'preparando', 'listo', 'entregado') DEFAULT 'pendiente',
  waiter_name VARCHAR(255) NOT NULL,
  archived BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_type (type),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de items del pedido (order_items)
CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(50) NOT NULL,
  product_id VARCHAR(50) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  product_description TEXT,
  product_price DECIMAL(10, 2) NOT NULL,
  product_category VARCHAR(50) NOT NULL,
  product_image VARCHAR(500),
  quantity INT NOT NULL,
  notes TEXT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;