-- =====================================================
-- Base de Datos: Federación Dominicana de Taekwondo
-- Sistema de Administración y Control
-- =====================================================

CREATE DATABASE IF NOT EXISTS federacion_tkd CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE federacion_tkd;

-- =====================================================
-- Tabla de Provincias de República Dominicana
-- =====================================================
CREATE TABLE IF NOT EXISTS provincias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    codigo VARCHAR(10),
    region VARCHAR(50)
) ENGINE=InnoDB;

-- Insertar las 32 provincias de RD
INSERT INTO provincias (nombre, region) VALUES
('Distrito Nacional', 'Ozama'),
('Santo Domingo', 'Ozama'),
('Santiago', 'Cibao Norte'),
('San Cristóbal', 'Valdesia'),
('La Vega', 'Cibao Sur'),
('Puerto Plata', 'Cibao Norte'),
('Duarte', 'Cibao Nordeste'),
('San Pedro de Macorís', 'Yuma'),
('La Romana', 'Yuma'),
('La Altagracia', 'Yuma'),
('Espaillat', 'Cibao Norte'),
('Peravia', 'Valdesia'),
('San Juan', 'El Valle'),
('Azua', 'Valdesia'),
('Barahona', 'Enriquillo'),
('Monte Cristi', 'Cibao Noroeste'),
('Sánchez Ramírez', 'Cibao Sur'),
('Valverde', 'Cibao Noroeste'),
('Monseñor Nouel', 'Cibao Sur'),
('María Trinidad Sánchez', 'Cibao Nordeste'),
('Samaná', 'Cibao Nordeste'),
('Hermanas Mirabal', 'Cibao Nordeste'),
('Monte Plata', 'Ozama'),
('Hato Mayor', 'Yuma'),
('El Seibo', 'Yuma'),
('Elías Piña', 'El Valle'),
('Baoruco', 'Enriquillo'),
('Independencia', 'Enriquillo'),
('Pedernales', 'Enriquillo'),
('Dajabón', 'Cibao Noroeste'),
('Santiago Rodríguez', 'Cibao Noroeste'),
('San José de Ocoa', 'Valdesia');

-- =====================================================
-- Tabla de Asociaciones
-- =====================================================
CREATE TABLE IF NOT EXISTS asociaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    direccion TEXT,
    provincia_id INT,
    email VARCHAR(100),
    telefono VARCHAR(20),
    presidente VARCHAR(150),
    presidente_celular VARCHAR(20),
    vice_presidente_1 VARCHAR(150),
    vice_presidente_1_celular VARCHAR(20),
    vice_presidente_2 VARCHAR(150),
    vice_presidente_2_celular VARCHAR(20),
    secretario_general VARCHAR(150),
    secretario_general_celular VARCHAR(20),
    tesorero VARCHAR(150),
    tesorero_celular VARCHAR(20),
    relaciones_publicas VARCHAR(150),
    relaciones_publicas_celular VARCHAR(20),
    sec_acta_corresp VARCHAR(150),
    sec_acta_corresp_celular VARCHAR(20),
    vocal_1 VARCHAR(150),
    vocal_1_celular VARCHAR(20),
    vocal_2 VARCHAR(150),
    vocal_2_celular VARCHAR(20),
    vocal_3 VARCHAR(150),
    vocal_3_celular VARCHAR(20),
    vocal_4 VARCHAR(150),
    vocal_4_celular VARCHAR(20),
    vocal_5 VARCHAR(150),
    vocal_5_celular VARCHAR(20),
    nota TEXT,
    logo VARCHAR(255),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_fundacion DATE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (provincia_id) REFERENCES provincias(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Grados/Cinturones (Kup y Dan)
-- =====================================================
CREATE TABLE IF NOT EXISTS grados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    color VARCHAR(50),
    nivel INT NOT NULL,
    tipo ENUM('kup', 'dan', 'poom') NOT NULL,
    descripcion TEXT,
    tiempo_minimo_meses INT DEFAULT 0,
    edad_minima INT DEFAULT 0,
    orden INT NOT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insertar grados Kup (cinturones de color)
INSERT INTO grados (nombre, color, nivel, tipo, orden, tiempo_minimo_meses) VALUES
('10° Kup', 'Blanco', 10, 'kup', 1, 0),
('9° Kup', 'Blanco Punta Amarilla', 9, 'kup', 2, 2),
('8° Kup', 'Amarillo', 8, 'kup', 3, 2),
('7° Kup', 'Amarillo Punta Verde', 7, 'kup', 4, 3),
('6° Kup', 'Verde', 6, 'kup', 5, 3),
('5° Kup', 'Verde Punta Azul', 5, 'kup', 6, 4),
('4° Kup', 'Azul', 4, 'kup', 7, 4),
('3° Kup', 'Azul Punta Roja', 3, 'kup', 8, 5),
('2° Kup', 'Rojo', 2, 'kup', 9, 6),
('1° Kup', 'Rojo Punta Negra', 1, 'kup', 10, 6);

-- Insertar grados Poom (menores de 15 años)
INSERT INTO grados (nombre, color, nivel, tipo, orden, tiempo_minimo_meses, edad_minima) VALUES
('1° Poom', 'Negro/Rojo', 1, 'poom', 11, 12, 0),
('2° Poom', 'Negro/Rojo', 2, 'poom', 12, 24, 0),
('3° Poom', 'Negro/Rojo', 3, 'poom', 13, 36, 0);

-- Insertar grados Dan (cinturón negro)
INSERT INTO grados (nombre, color, nivel, tipo, orden, tiempo_minimo_meses, edad_minima) VALUES
('1° Dan', 'Negro', 1, 'dan', 14, 12, 15),
('2° Dan', 'Negro', 2, 'dan', 15, 24, 17),
('3° Dan', 'Negro', 3, 'dan', 16, 36, 20),
('4° Dan', 'Negro', 4, 'dan', 17, 48, 24),
('5° Dan', 'Negro', 5, 'dan', 18, 60, 29),
('6° Dan', 'Negro', 6, 'dan', 19, 72, 35),
('7° Dan', 'Negro', 7, 'dan', 20, 84, 42),
('8° Dan', 'Negro', 8, 'dan', 21, 96, 50),
('9° Dan', 'Negro', 9, 'dan', 22, 108, 59);

-- =====================================================
-- Tabla de Clubes/Escuelas
-- =====================================================
CREATE TABLE IF NOT EXISTS escuelas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(20) UNIQUE,
    nombre VARCHAR(200) NOT NULL,
    direccion TEXT,
    provincia_id INT,
    asociacion_id INT,
    email VARCHAR(100),
    telefono VARCHAR(20),
    director VARCHAR(150),
    director_celular VARCHAR(20),
    secretario_general VARCHAR(150),
    secretario_general_celular VARCHAR(20),
    vocal_1 VARCHAR(150),
    vocal_1_celular VARCHAR(20),
    vocal_2 VARCHAR(150),
    vocal_2_celular VARCHAR(20),
    vocal_3 VARCHAR(150),
    vocal_3_celular VARCHAR(20),
    nota TEXT,
    logo VARCHAR(255),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_fundacion DATE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (provincia_id) REFERENCES provincias(id) ON DELETE SET NULL,
    FOREIGN KEY (asociacion_id) REFERENCES asociaciones(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Maestros/Instructores
-- =====================================================
CREATE TABLE IF NOT EXISTS maestros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    cedula VARCHAR(20),
    fecha_nacimiento DATE,
    sexo ENUM('M', 'F') DEFAULT 'M',
    email VARCHAR(100),
    telefono VARCHAR(20),
    celular VARCHAR(20),
    direccion TEXT,
    foto VARCHAR(255),
    escuela_id INT,
    grado_id INT,
    especialidad VARCHAR(200),
    certificaciones TEXT,
    fecha_inicio DATE,
    nota TEXT,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (escuela_id) REFERENCES escuelas(id) ON DELETE SET NULL,
    FOREIGN KEY (grado_id) REFERENCES grados(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Estudiantes/Atletas
-- =====================================================
CREATE TABLE IF NOT EXISTS estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matricula VARCHAR(20) UNIQUE,
    carnet VARCHAR(30),
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    cedula VARCHAR(20),
    fecha_nacimiento DATE,
    sexo ENUM('M', 'F') DEFAULT 'M',
    email VARCHAR(100),
    telefono VARCHAR(20),
    celular VARCHAR(20),
    direccion TEXT,
    foto VARCHAR(255),
    asociacion_id INT,
    escuela_id INT,
    grado_id INT,
    fecha_ingreso DATE,
    
    -- Datos del padre
    padre_nombre VARCHAR(150),
    padre_celular VARCHAR(20),
    padre_email VARCHAR(100),
    padre_profesion VARCHAR(100),
    
    -- Datos de la madre
    madre_nombre VARCHAR(150),
    madre_celular VARCHAR(20),
    madre_email VARCHAR(100),
    madre_profesion VARCHAR(100),
    
    -- Contacto de emergencia
    emergencia_nombre VARCHAR(150),
    emergencia_celular VARCHAR(20),
    emergencia_parentesco VARCHAR(50),
    
    -- Datos físicos
    estatura DECIMAL(5,2),
    peso DECIMAL(5,2),
    
    -- Información adicional
    institucion VARCHAR(100),
    aula VARCHAR(20),
    disciplina VARCHAR(100),
    enfermedad TEXT,
    tipo_sangre VARCHAR(10),
    
    -- Estados y clasificaciones
    seleccion_nacional TINYINT(1) DEFAULT 0,
    estudiante_becado TINYINT(1) DEFAULT 0,
    nivel_academico ENUM('primaria', 'bachiller', 'universitario', 'otro') DEFAULT 'primaria',
    estado ENUM('activo', 'inactivo', 'externo') DEFAULT 'activo',
    
    nota TEXT,
    pago_mensual DECIMAL(10,2) DEFAULT 0.00,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (asociacion_id) REFERENCES asociaciones(id) ON DELETE SET NULL,
    FOREIGN KEY (escuela_id) REFERENCES escuelas(id) ON DELETE SET NULL,
    FOREIGN KEY (grado_id) REFERENCES grados(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Tipos de Árbitros
-- =====================================================
CREATE TABLE IF NOT EXISTS tipos_arbitros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    nivel INT DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO tipos_arbitros (nombre, descripcion, nivel) VALUES
('Árbitro Nacional Clase C', 'Árbitro de nivel inicial', 1),
('Árbitro Nacional Clase B', 'Árbitro de nivel intermedio', 2),
('Árbitro Nacional Clase A', 'Árbitro de nivel avanzado', 3),
('Árbitro Internacional Clase 2', 'Árbitro internacional nivel 2', 4),
('Árbitro Internacional Clase 1', 'Árbitro internacional nivel 1', 5),
('Referee Internacional', 'Referee de competencias internacionales', 6);

-- =====================================================
-- Tabla de Árbitros
-- =====================================================
CREATE TABLE IF NOT EXISTS arbitros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    direccion TEXT,
    provincia_id INT,
    email VARCHAR(100),
    telefono VARCHAR(20),
    celular VARCHAR(20),
    tipo_arbitro_id INT,
    grado_id INT,
    foto VARCHAR(255),
    certificacion_mundial VARCHAR(100),
    fecha_certificacion DATE,
    nota TEXT,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (provincia_id) REFERENCES provincias(id) ON DELETE SET NULL,
    FOREIGN KEY (tipo_arbitro_id) REFERENCES tipos_arbitros(id) ON DELETE SET NULL,
    FOREIGN KEY (grado_id) REFERENCES grados(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Categorías de Peso
-- =====================================================
CREATE TABLE IF NOT EXISTS categorias_peso (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    peso_minimo DECIMAL(5,2),
    peso_maximo DECIMAL(5,2),
    sexo ENUM('M', 'F', 'Mixto') DEFAULT 'Mixto',
    tipo ENUM('kyorugi', 'poomsae') DEFAULT 'kyorugi',
    categoria_edad VARCHAR(50),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo'
) ENGINE=InnoDB;

-- Insertar categorías de peso masculino
INSERT INTO categorias_peso (nombre, peso_minimo, peso_maximo, sexo, tipo, categoria_edad) VALUES
('Fin', 0, 54, 'M', 'kyorugi', 'Senior'),
('Fly', 54, 58, 'M', 'kyorugi', 'Senior'),
('Bantam', 58, 63, 'M', 'kyorugi', 'Senior'),
('Feather', 63, 68, 'M', 'kyorugi', 'Senior'),
('Light', 68, 74, 'M', 'kyorugi', 'Senior'),
('Welter', 74, 80, 'M', 'kyorugi', 'Senior'),
('Middle', 80, 87, 'M', 'kyorugi', 'Senior'),
('Heavy', 87, 999, 'M', 'kyorugi', 'Senior');

-- Insertar categorías de peso femenino
INSERT INTO categorias_peso (nombre, peso_minimo, peso_maximo, sexo, tipo, categoria_edad) VALUES
('Fin', 0, 46, 'F', 'kyorugi', 'Senior'),
('Fly', 46, 49, 'F', 'kyorugi', 'Senior'),
('Bantam', 49, 53, 'F', 'kyorugi', 'Senior'),
('Feather', 53, 57, 'F', 'kyorugi', 'Senior'),
('Light', 57, 62, 'F', 'kyorugi', 'Senior'),
('Welter', 62, 67, 'F', 'kyorugi', 'Senior'),
('Middle', 67, 73, 'F', 'kyorugi', 'Senior'),
('Heavy', 73, 999, 'F', 'kyorugi', 'Senior');

-- =====================================================
-- Tabla de Torneos
-- =====================================================
CREATE TABLE IF NOT EXISTS torneos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    tipo ENUM('kyorugi', 'poomsae', 'mixto') DEFAULT 'kyorugi',
    nivel ENUM('municipal', 'provincial', 'regional', 'nacional', 'internacional') DEFAULT 'nacional',
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE,
    hora_inicio TIME,
    provincia_id INT,
    direccion TEXT,
    lugar VARCHAR(200),
    organizador VARCHAR(200),
    contacto_nombre VARCHAR(150),
    contacto_telefono VARCHAR(20),
    contacto_email VARCHAR(100),
    cuota_inscripcion DECIMAL(10,2) DEFAULT 0,
    nota TEXT,
    imagen VARCHAR(255),
    estado ENUM('planificado', 'activo', 'finalizado', 'cancelado') DEFAULT 'planificado',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (provincia_id) REFERENCES provincias(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Atletas en Torneos (Inscripciones)
-- =====================================================
CREATE TABLE IF NOT EXISTS torneo_atletas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    torneo_id INT NOT NULL,
    estudiante_id INT NOT NULL,
    categoria_peso_id INT,
    peso_registro DECIMAL(5,2),
    fecha_inscripcion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado ENUM('inscrito', 'confirmado', 'descalificado', 'retirado') DEFAULT 'inscrito',
    nota TEXT,
    
    FOREIGN KEY (torneo_id) REFERENCES torneos(id) ON DELETE CASCADE,
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (categoria_peso_id) REFERENCES categorias_peso(id) ON DELETE SET NULL,
    UNIQUE KEY unique_torneo_atleta (torneo_id, estudiante_id)
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Resultados/Ganadores de Torneos
-- =====================================================
CREATE TABLE IF NOT EXISTS torneo_resultados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    torneo_id INT NOT NULL,
    torneo_atleta_id INT NOT NULL,
    categoria_peso_id INT,
    medalla ENUM('oro', 'plata', 'bronce') NOT NULL,
    posicion INT,
    puntos INT DEFAULT 0,
    observaciones TEXT,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (torneo_id) REFERENCES torneos(id) ON DELETE CASCADE,
    FOREIGN KEY (torneo_atleta_id) REFERENCES torneo_atletas(id) ON DELETE CASCADE,
    FOREIGN KEY (categoria_peso_id) REFERENCES categorias_peso(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Árbitros en Torneos
-- =====================================================
CREATE TABLE IF NOT EXISTS torneo_arbitros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    torneo_id INT NOT NULL,
    arbitro_id INT NOT NULL,
    rol VARCHAR(50),
    fecha_asignacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (torneo_id) REFERENCES torneos(id) ON DELETE CASCADE,
    FOREIGN KEY (arbitro_id) REFERENCES arbitros(id) ON DELETE CASCADE,
    UNIQUE KEY unique_torneo_arbitro (torneo_id, arbitro_id)
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Historial de Grados de Estudiantes
-- =====================================================
CREATE TABLE IF NOT EXISTS historial_grados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    estudiante_id INT NOT NULL,
    grado_anterior_id INT,
    grado_nuevo_id INT NOT NULL,
    fecha_examen DATE NOT NULL,
    lugar_examen VARCHAR(200),
    examinador VARCHAR(150),
    resultado ENUM('aprobado', 'reprobado') DEFAULT 'aprobado',
    certificado_numero VARCHAR(50),
    nota TEXT,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (grado_anterior_id) REFERENCES grados(id) ON DELETE SET NULL,
    FOREIGN KEY (grado_nuevo_id) REFERENCES grados(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================
-- Tabla de Logs del Sistema
-- =====================================================
CREATE TABLE IF NOT EXISTS logs_sistema (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT,
    accion VARCHAR(100) NOT NULL,
    tabla_afectada VARCHAR(50),
    registro_id INT,
    datos_anteriores TEXT,
    datos_nuevos TEXT,
    ip_address VARCHAR(45),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================
-- Índices adicionales para optimización
-- =====================================================
CREATE INDEX idx_estudiantes_nombre ON estudiantes(nombre, apellido);
CREATE INDEX idx_estudiantes_matricula ON estudiantes(matricula);
CREATE INDEX idx_estudiantes_escuela ON estudiantes(escuela_id);
CREATE INDEX idx_estudiantes_grado ON estudiantes(grado_id);
CREATE INDEX idx_torneos_fecha ON torneos(fecha_inicio);
CREATE INDEX idx_torneos_estado ON torneos(estado);
CREATE INDEX idx_torneo_atletas_torneo ON torneo_atletas(torneo_id);
CREATE INDEX idx_torneo_resultados_torneo ON torneo_resultados(torneo_id);
CREATE INDEX idx_torneo_resultados_medalla ON torneo_resultados(medalla);
