-- Phi Loyalty · esquema de base de datos
-- Correr en phpMyAdmin, dentro de la base ya creada en cPanel (MySQL Wizard)

SET NAMES utf8mb4;

-- Un negocio cliente de Phi Design que usa el sistema de fidelización.
-- El slug identifica el link público de inscripción de cada negocio.
CREATE TABLE negocios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  color_principal VARCHAR(7) DEFAULT '#7C8591',
  logo_url VARCHAR(255),
  icono_sello_url VARCHAR(255),
  google_wallet_class_id VARCHAR(150),
  activo TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Usuarios con acceso al panel. negocio_id NULL identifica al super admin de Phi Design,
-- que ve todos los negocios. Un negocio puede tener más de un usuario (dueño, encargado),
-- igual que los accesos de Phi Tap. El super admin es quien crea y borra estos usuarios.
CREATE TABLE usuarios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  negocio_id INT UNSIGNED DEFAULT NULL,
  usuario VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol ENUM('super_admin','dueno') NOT NULL DEFAULT 'dueno',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (negocio_id) REFERENCES negocios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Empleados que pueden validar visitas en caja. pin_hash con password_hash(), nunca en texto plano.
-- No son usuarios del panel: solo tienen el PIN de caja, no ven ninguna pantalla del admin.
CREATE TABLE empleados (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  negocio_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(150) NOT NULL,
  pin_hash VARCHAR(255) NOT NULL,
  activo TINYINT(1) DEFAULT 1,
  FOREIGN KEY (negocio_id) REFERENCES negocios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Niveles de cada negocio (bronce, plata, oro). orden define la progresión.
CREATE TABLE niveles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  negocio_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(50) NOT NULL,
  puntos_requeridos INT UNSIGNED NOT NULL,
  texto_premio VARCHAR(200) NOT NULL,
  orden INT DEFAULT 0,
  FOREIGN KEY (negocio_id) REFERENCES negocios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Una fila por persona, aunque tenga tarjetas en varios negocios.
-- referido_por es la referencia a sí misma que registra quién trajo a quién.
CREATE TABLE clientes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  telefono VARCHAR(20) NOT NULL UNIQUE,
  telefono_verificado TINYINT(1) DEFAULT 0,
  codigo_referido VARCHAR(12) NOT NULL UNIQUE,
  referido_por INT UNSIGNED DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referido_por) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- El vínculo cliente-negocio-nivel. Es la tarjeta que vive en el wallet del cliente.
-- estado pasa a 'congelada' al llegar a la meta, hasta que el dueño confirma la entrega.
CREATE TABLE tarjetas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  negocio_id INT UNSIGNED NOT NULL,
  nivel_id INT UNSIGNED NOT NULL,
  puntos_actuales INT UNSIGNED DEFAULT 0,
  estado ENUM('activa','congelada') DEFAULT 'activa',
  pass_serial VARCHAR(100) UNIQUE,
  plataforma ENUM('google','apple') DEFAULT 'google',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
  FOREIGN KEY (negocio_id) REFERENCES negocios(id) ON DELETE CASCADE,
  FOREIGN KEY (nivel_id) REFERENCES niveles(id),
  UNIQUE KEY cliente_negocio (cliente_id, negocio_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cada punto ganado (o redimido) queda como una fila separada.
-- estado_revision es donde vive el flujo de aprobar/quitar reseñas y redes.
-- Los puntos de tipo 'visita' y 'referido' nacen en 'aprobado'; 'resena' y 'red_social' en 'pendiente'.
CREATE TABLE transacciones (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tarjeta_id INT UNSIGNED NOT NULL,
  tipo ENUM('visita','resena','red_social','referido','redimido') NOT NULL,
  puntos INT DEFAULT 1,
  estado_revision ENUM('aprobado','pendiente','rechazado') DEFAULT 'aprobado',
  evidencia_url VARCHAR(255),
  empleado_id INT UNSIGNED DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tarjeta_id) REFERENCES tarjetas(id) ON DELETE CASCADE,
  FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;