-- Migration: Regional Counselor Configuration Table
-- Purpose: Store province-level counselor allocation for regional elections
-- Date: 2026-01-09

CREATE TABLE IF NOT EXISTS `configuracion_consejeros_provincia` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `region_ubigeo` VARCHAR(10) NOT NULL COMMENT 'Region UBIGEO code (e.g., 13 for La Libertad)',
    `provincia_ubigeo` VARCHAR(10) NOT NULL COMMENT 'Province UBIGEO code (e.g., 1301 for Trujillo)',
    `provincia_nombre` VARCHAR(100) NOT NULL COMMENT 'Province name',
    `consejeros_titulares` INT NOT NULL DEFAULT 0 COMMENT 'Number of titular counselors',
    `consejeros_accesitarios` INT NOT NULL DEFAULT 0 COMMENT 'Number of alternate counselors',
    `total_consejeros_provincia` INT NOT NULL DEFAULT 0 COMMENT 'Total counselors (titulares + accesitarios)',
    `cuota_originarios_min` INT NOT NULL DEFAULT 0 COMMENT 'Minimum native/indigenous quota',
    `fuente_normativa` VARCHAR(255) DEFAULT NULL COMMENT 'Legal source (e.g., JNE Resolution)',
    `url_normativa` TEXT DEFAULT NULL COMMENT 'URL to legal document',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_region_provincia` (`region_ubigeo`, `provincia_ubigeo`),
    INDEX `idx_region` (`region_ubigeo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Province-level counselor configuration for regional elections';
