-- =============================================================================
-- Migración:    0031_create_clientes_y_fallecidos_tables
-- Sprint:       1.5a — Necesidad Inmediata (NI)
-- Tablas:       clientes, fallecidos
-- Aplica en:    BD de cada tenant (innovium_<slug>)
-- Dependencias: 0025 (parentescos), 0026 (estados_civiles), 0027 (nacionalidades),
--               0028 (geografía).
--
-- Descripción:
--   Las dos personas centrales de un contrato NI:
--
--   · clientes   — el contratante (titular pagador). Persona viva con
--                  RUT UNIQUE. Puede tener N contratos asociados (cliente
--                  recurrente).
--
--   · fallecidos — el difunto del contrato NI. Persona física fallecida.
--                  RUT NULL acepta el caso real (a veces no lo tienen).
--                  Cada fila se asocia a UN contrato (no se reusa).
--
-- Notas de diseño:
--
--   · clientes.rut UNIQUE: el RUT es el identificador chileno de
--     personas. Si un cliente vuelve, lo encontramos por RUT.
--   · fallecidos.rut NO UNIQUE: caso edge real, dos fallecidos
--     distintos podrían tener RUT registrado de forma confusa
--     (especialmente cuando se desconoce). El UNIQUE bloquearía
--     casos legítimos.
--   · Soft delete (eliminado_en) en clientes: razones GDPR-like
--     (cliente pide eliminación). Fallecidos NO tienen soft delete
--     porque siempre vienen con un contrato vinculado vía FK RESTRICT
--     (contratos.fallecido_id).
--   · FKs SET NULL para parentesco/región/etc.: si el catálogo se
--     desactiva, el cliente preserva la referencia perdida.
-- =============================================================================

CREATE TABLE clientes (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                        COMMENT 'PK auto-incremental.',
    rut                 VARCHAR(15)     NOT NULL
                        COMMENT 'RUT chileno con puntos y guión (ej: "10.231.996-6"). UNIQUE.',
    nombres             VARCHAR(150)    NOT NULL
                        COMMENT 'Nombre/s de pila (ej: "Juan Carlos").',
    apellido_paterno    VARCHAR(100)    NOT NULL
                        COMMENT 'Apellido paterno (ej: "Carrasco").',
    apellido_materno    VARCHAR(100)        NULL
                        COMMENT 'Apellido materno opcional.',
    email               VARCHAR(180)        NULL
                        COMMENT 'Email de contacto.',
    telefono_1          VARCHAR(15)         NULL
                        COMMENT 'Teléfono primario chileno (formato libre).',
    telefono_2          VARCHAR(15)         NULL,
    direccion           VARCHAR(500)        NULL
                        COMMENT 'Dirección postal.',
    region_id           BIGINT UNSIGNED     NULL
                        COMMENT 'FK → regiones.id (SET NULL).',
    provincia_id        BIGINT UNSIGNED     NULL
                        COMMENT 'FK → provincias.id (SET NULL).',
    comuna_id           BIGINT UNSIGNED     NULL
                        COMMENT 'FK → comunas.id (SET NULL).',
    fecha_nacimiento    DATE                NULL,
    parentesco_id       BIGINT UNSIGNED     NULL
                        COMMENT 'FK → parentescos.id (SET NULL). Relación con el fallecido.',
    notas               TEXT                NULL,
    creado_en           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                                 ON UPDATE CURRENT_TIMESTAMP,
    eliminado_en        DATETIME            NULL
                        COMMENT 'UTC del soft-delete (GDPR-like). NULL = activo.',
    PRIMARY KEY (id),
    UNIQUE KEY uq_clientes_rut (rut),
    KEY idx_clientes_eliminado (eliminado_en),
    KEY idx_clientes_apellido_paterno (apellido_paterno),
    CONSTRAINT fk_clientes_region
        FOREIGN KEY (region_id) REFERENCES regiones (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_clientes_provincia
        FOREIGN KEY (provincia_id) REFERENCES provincias (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_clientes_comuna
        FOREIGN KEY (comuna_id) REFERENCES comunas (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_clientes_parentesco
        FOREIGN KEY (parentesco_id) REFERENCES parentescos (id)
        ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Contratantes (titulares pagadores) de los contratos. RUT chileno UNIQUE.';


CREATE TABLE fallecidos (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                            COMMENT 'PK auto-incremental.',
    nombres                 VARCHAR(150)    NOT NULL,
    apellido_paterno        VARCHAR(100)    NOT NULL,
    apellido_materno        VARCHAR(100)        NULL,
    rut                     VARCHAR(15)         NULL
                            COMMENT 'RUT chileno opcional (a veces no lo tienen).',
    fecha_nacimiento        DATE                NULL,
    fecha_defuncion         DATE            NOT NULL
                            COMMENT 'Fecha de muerte. Required. Validar ≤ hoy en código.',
    lugar_defuncion         VARCHAR(200)        NULL
                            COMMENT 'Lugar físico (ej: "Hospital del Trabajador").',
    comuna_defuncion_id     BIGINT UNSIGNED     NULL
                            COMMENT 'FK → comunas.id (SET NULL).',
    causa_muerte            VARCHAR(300)        NULL
                            COMMENT 'Uso interno opcional.',
    estado_civil_id         INT UNSIGNED        NULL
                            COMMENT 'FK → estados_civiles.id (SET NULL).',
    nacionalidad_id         BIGINT UNSIGNED     NULL
                            COMMENT 'FK → nacionalidades.id (SET NULL).',
    profesion               VARCHAR(100)        NULL,
    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_fallecidos_apellido (apellido_paterno),
    KEY idx_fallecidos_fecha_defuncion (fecha_defuncion),
    CONSTRAINT fk_fallecidos_comuna_defuncion
        FOREIGN KEY (comuna_defuncion_id) REFERENCES comunas (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_fallecidos_estado_civil
        FOREIGN KEY (estado_civil_id) REFERENCES estados_civiles (id)
        ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT fk_fallecidos_nacionalidad
        FOREIGN KEY (nacionalidad_id) REFERENCES nacionalidades (id)
        ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Difuntos asociados a contratos NI. RUT NULL acepta caso real.';