-- =============================================================================
-- Migración:    0028_create_geografia_chile_tables
-- Sprint:       1.5a — Necesidad Inmediata (NI)
-- Tablas:       regiones, provincias, comunas
-- Aplica en:    BD de cada tenant (innovium_<slug>)
-- Dependencias: ninguna (catálogo master).
--
-- Descripción:
--   División político-administrativa de Chile (datos públicos SUBDERE):
--     · 16 regiones (incluye Ñuble desde 2018)
--     · 56 provincias
--     · ~346 comunas
--
--   Tres tablas en una sola migración porque sus dependencias FK son
--   estrictamente secuenciales (provincia.region_id → comunas.provincia_id)
--   y siempre se siembran/migran juntas.
--
-- Notas de diseño:
--
--   · Numeración OFICIAL: las 16 regiones se identifican por su número
--     romano del legacy chileno (Tarapacá=I, Antofagasta=II, ...,
--     Metropolitana=XIII en uso interno, aunque la denominación oficial
--     es "Región Metropolitana"). Para evitar collation issues, usamos
--     el id auto-increment para la PK y guardamos el código en
--     `codigo` (ej: "I", "II", "RM", "XV").
--
--   · ON DELETE RESTRICT en todas las FKs internas: nunca se borra
--     físicamente una región/provincia/comuna. Si pasara
--     (escenario muy raro), bloquea automáticamente.
--
--   · Soft delete NO aplica: el catálogo es estructural y estable.
--     Si una comuna deja de existir (cambio administrativo del Estado),
--     se marca activo=0; el histórico de clientes/contratos que la
--     referencian preserva la integridad.
-- =============================================================================

CREATE TABLE regiones (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                    COMMENT 'PK auto-incremental.',
    codigo          VARCHAR(5)      NOT NULL
                    COMMENT 'Código oficial (ej: "I", "II", "XV", "RM"). UNIQUE.',
    nombre          VARCHAR(100)    NOT NULL
                    COMMENT 'Nombre oficial (ej: "Tarapacá", "Metropolitana de Santiago").',
    orden_visual   INT             NOT NULL DEFAULT 0
                    COMMENT 'Orden geográfico norte-a-sur (Tarapacá=1, Magallanes=15, etc.).',
    activo          TINYINT(1)      NOT NULL DEFAULT 1
                    COMMENT '1=disponible, 0=oculta.',
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                             ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_regiones_codigo (codigo),
    KEY idx_regiones_activo_orden (activo, orden_visual)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Catálogo master · 16 regiones de Chile (datos públicos SUBDERE).';


CREATE TABLE provincias (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                    COMMENT 'PK auto-incremental.',
    region_id       BIGINT UNSIGNED NOT NULL
                    COMMENT 'FK → regiones.id (RESTRICT).',
    nombre          VARCHAR(100)    NOT NULL
                    COMMENT 'Nombre de la provincia.',
    activo          TINYINT(1)      NOT NULL DEFAULT 1,
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                             ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_provincias_region (region_id, activo),
    CONSTRAINT fk_provincias_region
        FOREIGN KEY (region_id) REFERENCES regiones (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Catálogo master · 56 provincias de Chile.';


CREATE TABLE comunas (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                    COMMENT 'PK auto-incremental.',
    provincia_id    BIGINT UNSIGNED NOT NULL
                    COMMENT 'FK → provincias.id (RESTRICT).',
    region_id       BIGINT UNSIGNED NOT NULL
                    COMMENT 'FK → regiones.id (RESTRICT). Denormalizado para queries rápidos.',
    nombre          VARCHAR(100)    NOT NULL
                    COMMENT 'Nombre de la comuna.',
    activo          TINYINT(1)      NOT NULL DEFAULT 1,
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                             ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_comunas_provincia (provincia_id, activo),
    KEY idx_comunas_region (region_id, activo),
    KEY idx_comunas_nombre (nombre),
    CONSTRAINT fk_comunas_provincia
        FOREIGN KEY (provincia_id) REFERENCES provincias (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_comunas_region
        FOREIGN KEY (region_id) REFERENCES regiones (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Catálogo master · ~346 comunas de Chile (datos públicos SUBDERE).';