-- Migration 006: Roles de Juez y asignaciones de autoridades
-- Crear tablas roles_juez y juez_rol

CREATE TABLE IF NOT EXISTS roles_juez (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    descripcion VARCHAR(255) NULL,
    activo      TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at  DATETIME     NULL,
    UNIQUE KEY uq_roles_juez_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Roles iniciales
INSERT IGNORE INTO roles_juez (nombre) VALUES
    ('Coordinador Rosario'),
    ('Referente IPP'),
    ('Referente Juicio'),
    ('Referente Ejecución'),
    ('Coordinador InterDistrital'),
    ('Presidente'),
    ('VicePresidente');

CREATE TABLE IF NOT EXISTS juez_rol (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    juez_id     VARCHAR(10)  NOT NULL,
    rolJuez_id  INT UNSIGNED NOT NULL,
    fecha_desde DATE         NOT NULL,
    fecha_hasta DATE         NULL COMMENT 'NULL = sin vencimiento',
    observaciones TEXT       NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at  DATETIME     NULL,
    CONSTRAINT fk_juez_rol_juez FOREIGN KEY (juez_id)     REFERENCES mrbs_juez(juez_id)    ON UPDATE CASCADE,
    CONSTRAINT fk_juez_rol_rol  FOREIGN KEY (rolJuez_id)  REFERENCES roles_juez(id),
    INDEX idx_juez_rol_juez (juez_id),
    INDEX idx_juez_rol_rol  (rolJuez_id),
    INDEX idx_juez_rol_periodo (rolJuez_id, fecha_desde, fecha_hasta)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DML autoridades 2026
-- Aguirre (J049) → Coordinador Rosario
-- Chiabrera (J031) → Referente IPP
-- Pedrana (J042) → Referente Juicio
-- Lopez Quintana (J001) → VicePresidente
-- Romanini (J904) → Coordinador InterDistrital

INSERT INTO juez_rol (juez_id, rolJuez_id, fecha_desde, fecha_hasta, observaciones)
SELECT 'J049', id, '2026-01-01', '2026-12-31', 'Autoridades 2026' FROM roles_juez WHERE nombre='Coordinador Rosario';

INSERT INTO juez_rol (juez_id, rolJuez_id, fecha_desde, fecha_hasta, observaciones)
SELECT 'J031', id, '2026-01-01', '2026-12-31', 'Autoridades 2026' FROM roles_juez WHERE nombre='Referente IPP';

INSERT INTO juez_rol (juez_id, rolJuez_id, fecha_desde, fecha_hasta, observaciones)
SELECT 'J042', id, '2026-01-01', '2026-12-31', 'Autoridades 2026' FROM roles_juez WHERE nombre='Referente Juicio';

INSERT INTO juez_rol (juez_id, rolJuez_id, fecha_desde, fecha_hasta, observaciones)
SELECT 'J001', id, '2026-01-01', '2026-12-31', 'Autoridades 2026' FROM roles_juez WHERE nombre='VicePresidente';

INSERT INTO juez_rol (juez_id, rolJuez_id, fecha_desde, fecha_hasta, observaciones)
SELECT 'J904', id, '2026-01-01', '2026-12-31', 'Autoridades 2026' FROM roles_juez WHERE nombre='Coordinador InterDistrital';
