-- Migration 028 — Perfiles de clientes (fase 1: identidad + preferencias)
-- 1 fila por contacto. Solo identidad básica + notas/preferencias libres.
-- Sin scoring, churn, LTV ni sentiment — eso es fase posterior.

DROP PROCEDURE IF EXISTS migrate_028;

DELIMITER //
CREATE PROCEDURE migrate_028()
BEGIN
    DECLARE CONTINUE HANDLER FOR 1050 BEGIN END; -- Table already exists
    DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; -- Duplicate column
    DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; -- Duplicate key

    CREATE TABLE IF NOT EXISTS client_profiles (
        id          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
        tenant_id   BIGINT UNSIGNED  NOT NULL,
        contact_id  BIGINT UNSIGNED  NOT NULL,
        email       VARCHAR(160)     NULL,
        birthday    DATE             NULL,
        notes       TEXT             NULL      COMMENT 'Notas libres del dueño o extraídas por Vale',
        preferences JSON             NULL      COMMENT 'Preferencias estructuradas: horario, servicio, datos rubro-específicos',
        created_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_cp_tenant_contact (tenant_id, contact_id),
        INDEX      idx_cp_tenant      (tenant_id),
        CONSTRAINT fk_cp_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
        CONSTRAINT fk_cp_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

END //
DELIMITER ;

CALL migrate_028();
DROP PROCEDURE IF EXISTS migrate_028;