-- ============================================================================
-- AMIGO MX - POS System Database Schema
-- Ejecutar: mysql -u wwsist_mikes -p wwsist_amigo < schema.sql
-- ============================================================================

USE wwsist_amigo;

-- ----------------------------------------------------------------------------
-- USERS & AUTH
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  role ENUM('admin', 'mesero', 'cocina', 'bar') NOT NULL DEFAULT 'mesero',
  lang ENUM('es', 'en') NOT NULL DEFAULT 'es',
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- TABLES (MESAS)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tables_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  capacity INT NOT NULL DEFAULT 4,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  display_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO tables_config (name, capacity, display_order) VALUES
  ('Mesa 1', 4, 1),
  ('Mesa 2', 4, 2),
  ('Mesa 3', 6, 3),
  ('Mesa 4', 4, 4),
  ('Mesa 5', 8, 5),
  ('Barra 1', 2, 6),
  ('Barra 2', 2, 7),
  ('Terraza 1', 6, 8),
  ('Terraza 2', 6, 9),
  ('VIP', 10, 10);

-- ----------------------------------------------------------------------------
-- MENU CATEGORIES
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS menu_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  menu_type ENUM('food', 'drink') NOT NULL,
  emoji VARCHAR(10) DEFAULT NULL,
  display_order INT NOT NULL DEFAULT 0,
  active BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;

INSERT INTO menu_categories (name, menu_type, emoji, display_order) VALUES
  ('Entradas', 'food', '🫔', 1),
  ('Tacos', 'food', '🌮', 2),
  ('Burritos', 'food', '🌯', 3),
  ('Quesadillas', 'food', '🧀', 4),
  ('Margaritas', 'drink', '🍋', 5),
  ('Cocteles', 'drink', '🍹', 6),
  ('Shots', 'drink', '🥃', 7),
  ('Cervezas', 'drink', '🍺', 8),
  ('Sin Alcohol', 'drink', '🥤', 9);

-- ----------------------------------------------------------------------------
-- MENU ITEMS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS menu_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  half_price DECIMAL(8,2) DEFAULT NULL,
  emoji VARCHAR(10) DEFAULT NULL,
  type ENUM('food', 'drink') NOT NULL,
  has_burrito_extra BOOLEAN NOT NULL DEFAULT FALSE,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  display_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES menu_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Entradas
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (1, 'Loaded Nachos', 7.00, '🫔', 'food', 1),
  (1, 'Loaded Papas', 7.00, '🍟', 'food', 2);

-- Tacos
INSERT INTO menu_items (category_id, name, price, half_price, emoji, type, display_order) VALUES
  (2, 'Apotitaco', 11.00, 7.00, '🌮', 'food', 1),
  (2, 'Taco Amigo', 11.00, 7.00, '🌮', 'food', 2),
  (2, 'Pollos Hermanos', 11.00, 7.00, '🌮', 'food', 3),
  (2, 'Taco Papi', 11.00, 7.00, '🌮', 'food', 4),
  (2, 'Tacon Madre', 11.00, 7.00, '🌮', 'food', 5),
  (2, 'Tacon Smurf', 11.00, 7.00, '🌮', 'food', 6);

-- Burritos
INSERT INTO menu_items (category_id, name, price, emoji, type, has_burrito_extra, display_order) VALUES
  (3, 'Burrito Hongos', 9.00, '🌯', 'food', TRUE, 1),
  (3, 'Burrito Pollo', 10.00, '🌯', 'food', TRUE, 2),
  (3, 'Burrito Cerdo', 10.00, '🌯', 'food', TRUE, 3),
  (3, 'Burrito Camarones', 11.00, '🌯', 'food', TRUE, 4),
  (3, 'Burrito Res', 12.00, '🌯', 'food', TRUE, 5);

-- Margaritas
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (5, 'Coconut Margarita', 7.00, '🍋', 'drink', 1),
  (5, 'Spicy Margarita', 7.00, '🌶️', 'drink', 2),
  (5, 'Peach Margarita', 7.00, '🍑', 'drink', 3),
  (5, 'Passion Fruit Margarita', 7.00, '🍋', 'drink', 4),
  (5, 'Margarita Tropical', 7.00, '🌴', 'drink', 5),
  (5, 'Pickle Margarita', 7.00, '🥒', 'drink', 6),
  (5, 'Mezcalita', 10.00, '🥃', 'drink', 7),
  (5, 'Kiwi Margarita', 7.00, '🥝', 'drink', 8),
  (5, 'Margarita Devil', 7.00, '😈', 'drink', 9),
  (5, 'Jagerguerita', 7.00, '🍋', 'drink', 10);

-- Cocteles
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (6, 'Paloma', 7.00, '🍹', 'drink', 1),
  (6, 'Sunrise', 7.00, '🌅', 'drink', 2),
  (6, 'Mojito', 7.00, '🍃', 'drink', 3),
  (6, 'Piña Colada', 7.00, '🍍', 'drink', 4),
  (6, 'Cuba Libre', 7.00, '🍹', 'drink', 5),
  (6, 'Long Island', 9.00, '🏝️', 'drink', 6),
  (6, 'Michelada', 9.00, '🍺', 'drink', 7),
  (6, 'Sangria (copa)', 7.00, '🍷', 'drink', 8),
  (6, 'Sangria Jarra 1.5L', 15.00, '🫗', 'drink', 9),
  (6, 'Aperol Spritz', 6.00, '🍊', 'drink', 10);

-- Shots
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (7, 'San Jose', 3.00, '🥃', 'drink', 1),
  (7, '1800', 6.00, '🥃', 'drink', 2),
  (7, 'Don Julio', 9.00, '🥃', 'drink', 3),
  (7, 'Jagermeister', 4.00, '🥃', 'drink', 4),
  (7, 'Clase Azul', 17.00, '🥃', 'drink', 5);

-- Cervezas
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (8, 'Corona', 4.00, '🍺', 'drink', 1),
  (8, 'Modelo', 4.00, '🍺', 'drink', 2),
  (8, 'Victoria', 4.00, '🍺', 'drink', 3),
  (8, 'Heineken', 5.00, '🍺', 'drink', 4),
  (8, 'Bucket 5 Cervezas', 18.00, '🪣', 'drink', 5);

-- Sin Alcohol
INSERT INTO menu_items (category_id, name, price, emoji, type, display_order) VALUES
  (9, 'Agua', 2.00, '💧', 'drink', 1),
  (9, 'Refresco', 3.00, '🥤', 'drink', 2),
  (9, 'Limonada', 4.00, '🍋', 'drink', 3),
  (9, 'Cafe', 3.00, '☕', 'drink', 4);

-- ----------------------------------------------------------------------------
-- MENU ITEM EXTRAS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS menu_item_extras (
  id INT AUTO_INCREMENT PRIMARY KEY,
  menu_item_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  included BOOLEAN NOT NULL DEFAULT TRUE,
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO menu_item_extras (menu_item_id, name, price, included) VALUES
  (1, 'Cilantro', 0, TRUE),
  (1, 'Crema', 0, TRUE),
  (2, 'Cilantro', 0, TRUE),
  (2, 'Crema', 0, TRUE),
  (8, 'Queso vegano', 1.50, FALSE);

-- ----------------------------------------------------------------------------
-- ORDERS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_id INT NOT NULL,
  created_by INT NOT NULL,
  status ENUM('open', 'closed', 'cancelled') NOT NULL DEFAULT 'open',
  payment_method ENUM('cash', 'card', 'mixed') DEFAULT NULL,
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  tip DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  closed_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (table_id) REFERENCES tables_config(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- ORDER ITEMS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  menu_item_id INT NOT NULL,
  person_name VARCHAR(100) DEFAULT NULL,
  quantity INT NOT NULL DEFAULT 1,
  half_order BOOLEAN NOT NULL DEFAULT FALSE,
  unit_price DECIMAL(8,2) NOT NULL,
  notes TEXT DEFAULT NULL,
  status ENUM('pending', 'preparing', 'ready', 'delivered') NOT NULL DEFAULT 'pending',
  routed_to ENUM('kitchen', 'bar') NOT NULL,
  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 (menu_item_id) REFERENCES menu_items(id)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- ORDER ITEM EXTRAS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS order_item_extras (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_item_id INT NOT NULL,
  extra_name VARCHAR(100) NOT NULL,
  extra_price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  removed BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- DAILY SUMMARIES
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS daily_summaries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  summary_date DATE NOT NULL UNIQUE,
  total_orders INT NOT NULL DEFAULT 0,
  total_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_tips DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  cash_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  card_revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  top_item_name VARCHAR(150) DEFAULT NULL,
  top_item_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- INDEXES
-- ----------------------------------------------------------------------------
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_table ON orders(table_id);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_order_items_status ON order_items(status);
CREATE INDEX idx_order_items_routed ON order_items(routed_to);
CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_menu_items_type ON menu_items(type);