-- En hosting compartido la base de datos ya suele existir (wwmtdi_diplomas).
-- Si la creas manualmente, usa este nombre o ajusta según tu panel.

USE wwmtdi_diplomas;

CREATE TABLE IF NOT EXISTS plantillas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL,
    ruta_pdf VARCHAR(500) NOT NULL,
    ancho_pagina DECIMAL(10, 2) NOT NULL DEFAULT 595.28,
    alto_pagina DECIMAL(10, 2) NOT NULL DEFAULT 841.89,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campos_plantilla (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plantilla_id INT UNSIGNED NOT NULL,
    nombre_campo ENUM('nombre', 'diplomado', 'id_documento', 'qr') NOT NULL,
    x DECIMAL(10, 2) NOT NULL DEFAULT 0,
    y DECIMAL(10, 2) NOT NULL DEFAULT 0,
    font_size DECIMAL(6, 2) NOT NULL DEFAULT 14,
    font_family VARCHAR(100) NOT NULL DEFAULT 'helvetica',
    color VARCHAR(7) NOT NULL DEFAULT '#000000',
    alineacion ENUM('left', 'center', 'right') NOT NULL DEFAULT 'left',
    ancho DECIMAL(10, 2) NULL,
    alto DECIMAL(10, 2) NULL,
    CONSTRAINT fk_campos_plantilla_plantilla
        FOREIGN KEY (plantilla_id) REFERENCES plantillas(id)
        ON DELETE CASCADE,
    UNIQUE KEY uq_plantilla_campo (plantilla_id, nombre_campo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lotes_generacion (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plantilla_id INT UNSIGNED NOT NULL,
    nombre_archivo_excel VARCHAR(255) NOT NULL,
    total_diplomas INT UNSIGNED NOT NULL DEFAULT 0,
    ruta_pdf_generado VARCHAR(500) NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lotes_plantilla
        FOREIGN KEY (plantilla_id) REFERENCES plantillas(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS diplomas_generados (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lote_id INT UNSIGNED NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    diplomado VARCHAR(255) NOT NULL,
    id_documento VARCHAR(50) NOT NULL,
    link_validacion VARCHAR(500) NOT NULL,
    generado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_diplomas_lote
        FOREIGN KEY (lote_id) REFERENCES lotes_generacion(id)
        ON DELETE CASCADE,
    INDEX idx_id_documento (id_documento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;