-- Esquema MySQL para la plataforma de jugadores de baloncesto
-- Importar vía phpMyAdmin en la base de datos creada desde cPanel

SET NAMES utf8mb4;

CREATE TABLE admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol VARCHAR(30) NOT NULL DEFAULT 'admin',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE players (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(190) NOT NULL UNIQUE,
  publicado TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_by INT UNSIGNED NULL,

  -- perfil
  nombre VARCHAR(100) NOT NULL,
  apellido VARCHAR(100) NOT NULL,
  fecha_nacimiento DATE NULL,
  altura_cm SMALLINT UNSIGNED NULL,
  peso_kg SMALLINT UNSIGNED NULL,
  envergadura_cm SMALLINT UNSIGNED NULL,
  posicion VARCHAR(30) NULL,
  posicion_secundaria VARCHAR(30) NULL,
  dorsal SMALLINT UNSIGNED NULL,
  ciudad VARCHAR(100) NULL,
  pais VARCHAR(100) NULL DEFAULT 'Ecuador',
  mano_dominante VARCHAR(20) NULL,
  bio TEXT NULL,

  -- stats (promedios por partido, temporada actual)
  ppg DECIMAL(5,1) NULL,
  rpg DECIMAL(5,1) NULL,
  apg DECIMAL(5,1) NULL,
  spg DECIMAL(5,1) NULL,
  bpg DECIMAL(5,1) NULL,
  ft_pct DECIMAL(5,1) NULL,
  fg2_pct DECIMAL(5,1) NULL,
  fg3_pct DECIMAL(5,1) NULL,
  partidos_jugados SMALLINT UNSIGNED NULL,
  temporada VARCHAR(20) NULL,

  -- multimedia
  foto_perfil VARCHAR(255) NULL,
  foto_hero VARCHAR(255) NULL,

  -- auspicio / contacto
  busca_tipos TEXT NULL,          -- coma-separado
  marcas_actuales TEXT NULL,      -- coma-separado
  email_contacto VARCHAR(190) NULL,
  telefono VARCHAR(30) NULL,
  whatsapp VARCHAR(30) NULL,
  instagram VARCHAR(60) NULL,
  dossier_url VARCHAR(255) NULL,

  -- tema visual
  color_acento VARCHAR(10) NOT NULL DEFAULT '#CCFF00',
  color_secundario VARCHAR(10) NOT NULL DEFAULT '#7B2FFF',

  FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_gallery (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  url VARCHAR(255) NOT NULL,
  caption VARCHAR(255) NULL,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_videos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  plataforma VARCHAR(20) NOT NULL,   -- youtube | vimeo
  video_id VARCHAR(50) NOT NULL,
  titulo VARCHAR(190) NULL,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_equipos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(190) NOT NULL,
  temporada VARCHAR(20) NULL,
  liga VARCHAR(190) NULL,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_campeonatos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  titulo VARCHAR(190) NOT NULL,
  anio SMALLINT UNSIGNED NULL,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_premios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  titulo VARCHAR(190) NOT NULL,
  anio SMALLINT UNSIGNED NULL,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE player_mensajes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(190) NOT NULL,
  organizacion VARCHAR(190) NULL,
  email VARCHAR(190) NOT NULL,
  tipo_interes VARCHAR(30) NULL,
  mensaje TEXT NOT NULL,
  fecha DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  leido TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;