-- ============================================================
-- Phi Design — Menú NFC
-- Esquema multi-tenant: un solo sistema sirve a varios restaurantes.
-- Ejecutar completo en phpMyAdmin, dentro de la base ya creada en cPanel.
-- ============================================================

CREATE TABLE restaurants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) UNIQUE NOT NULL,          -- URL pública: phidesignn.com/m/menu.php?r=slug
  name VARCHAR(150) NOT NULL,
  logo_path VARCHAR(255),
  primary_color VARCHAR(7) DEFAULT '#111111',
  whatsapp_number VARCHAR(20),               -- con código de país, ej. 593987654321
  instagram_url VARCHAR(255),
  google_place_id VARCHAR(150),              -- para el botón de reseñas de Google
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT UNSIGNED NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'General',
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price DECIMAL(10,2),                       -- NULL si el precio no aplica (ej. "según mercado")
  image_path VARCHAR(255),
  sort_order INT NOT NULL DEFAULT 0,
  visible TINYINT(1) NOT NULL DEFAULT 1,     -- 0 = "agotado" / oculto, sin borrar
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_items_restaurant FOREIGN KEY (restaurant_id)
    REFERENCES restaurants(id) ON DELETE CASCADE,
  INDEX idx_items_restaurant (restaurant_id, visible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT UNSIGNED NULL,           -- NULL = superadmin (Juan Fernando), ve todos los locales
  username VARCHAR(80) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_admin_restaurant FOREIGN KEY (restaurant_id)
    REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE analytics_events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT UNSIGNED NOT NULL,
  event_type VARCHAR(40) NOT NULL,           -- page_view | item_view | whatsapp_click | review_click | instagram_click
  item_id INT UNSIGNED NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_events_restaurant FOREIGN KEY (restaurant_id)
    REFERENCES restaurants(id) ON DELETE CASCADE,
  CONSTRAINT fk_events_item FOREIGN KEY (item_id)
    REFERENCES items(id) ON DELETE SET NULL,
  INDEX idx_events_restaurant_date (restaurant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Datos de ejemplo: un restaurante de prueba con dos platos.
-- Bórralos cuando cargues el primer cliente real.
-- ============================================================

INSERT INTO restaurants (slug, name, primary_color, whatsapp_number, instagram_url)
VALUES ('demo', 'Restaurante Demo', '#c0392b', '593987654342', 'https://instagram.com/demo');

INSERT INTO items (restaurant_id, category, name, description, price, sort_order)
VALUES
  (1, 'Entradas', 'Empanadas de verde', 'Con relleno de queso, tres unidades', 3.50, 1),
  (1, 'Fuertes', 'Seco de pollo', 'Con arroz y patacones', 7.00, 1);

-- ============================================================
-- Usuario superadmin (tú). El hash de abajo es un valor de ejemplo:
-- reemplázalo por el que genere admin/generate-hash.php antes de subir,
-- y borra ese script del servidor apenas lo uses (ver instrucciones).
-- ============================================================

INSERT INTO admin_users (restaurant_id, username, password_hash)
VALUES (NULL, 'juanfernando', 'REEMPLAZA_ESTE_HASH');