-- ============================================================================
-- MIGRACION: Sistema de Planes de Pago
-- Fecha: 2026-01-21
-- Descripcion: Crea las tablas planes_pago, pagos_planes, configuraciones_planes_pago
--              y modifica notas_internas y configuraciones
-- ============================================================================
-- IMPORTANTE: Este script es idempotente - puede ejecutarse multiples veces sin error
-- ============================================================================

-- ============================================================================
-- SECCION 0: FUNCION PARA TRIGGER DE UPDATED_AT (si no existe)
-- ============================================================================

CREATE OR REPLACE FUNCTION set_updated_at_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION set_updated_at_timestamp() IS 'Actualiza automaticamente updated_at en cada UPDATE';

-- ============================================================================
-- SECCION 1: TABLA planes_pago (42 columnas)
-- ============================================================================

CREATE TABLE IF NOT EXISTS planes_pago (
    -- Identificacion (1-4)
    id_plan                     SERIAL          NOT NULL,
    business_id                 VARCHAR(100)    NOT NULL,
    sucursal                    INT4[]          NULL,
    id_cliente                  INTEGER         NOT NULL,

    -- Invitados/Familia (5)
    invitados                   JSONB           NULL        DEFAULT '[]'::jsonb,

    -- Descripcion del Plan (6-7)
    nombre_plan                 VARCHAR(255)    NOT NULL,
    descripcion                 TEXT            NULL,

    -- Productos (8-9)
    productos_ids               INT4[]          NULL,
    productos_snapshot          JSONB           NOT NULL,

    -- Montos Financieros (10-17)
    monto_total                 NUMERIC(12,2)   NOT NULL,
    monto_enganche              NUMERIC(12,2)   NULL        DEFAULT 0,
    monto_financiado            NUMERIC(12,2)   NOT NULL,
    numero_parcialidades        INTEGER         NOT NULL,
    monto_parcialidad           NUMERIC(12,2)   NOT NULL,
    total_abonado               NUMERIC(12,2)   NULL        DEFAULT 0,
    saldo_pendiente             NUMERIC(12,2)   NOT NULL,
    porcentaje_cubierto         NUMERIC(5,2)    NULL        DEFAULT 0,

    -- Configuracion de Pagos (18-20)
    periodicidad                VARCHAR(20)     NOT NULL    DEFAULT 'mensual',
    dia_pago                    INTEGER         NULL,
    calendario_pagos            JSONB           NOT NULL,

    -- Mora (21-23)
    monto_mora_acumulada        NUMERIC(12,2)   NULL        DEFAULT 0,
    historial_mora              JSONB           NULL        DEFAULT '[]'::jsonb,
    politica_mora               VARCHAR(20)     NULL        DEFAULT 'mora_siguiente',

    -- Estado del Plan (24-25)
    estado                      INTEGER         NOT NULL    DEFAULT 1,
    elegible_entregables        BOOLEAN         NULL        DEFAULT FALSE,

    -- Fechas Importantes (26-33)
    fecha_inicio                TIMESTAMPTZ     NOT NULL,
    fecha_primer_pago           TIMESTAMPTZ     NULL,
    fecha_ultimo_pago           TIMESTAMPTZ     NULL,
    fecha_limite                TIMESTAMPTZ     NULL,
    fecha_liquidacion           TIMESTAMPTZ     NULL,
    fecha_pausa_inicio          TIMESTAMPTZ     NULL,
    fecha_pausa_fin             TIMESTAMPTZ     NULL,
    dias_pausados               INTEGER         NULL        DEFAULT 0,

    -- Alertas (34-35)
    alertas_activas             JSONB           NULL        DEFAULT '[]'::jsonb,
    ultima_evaluacion_alertas   TIMESTAMPTZ     NULL,

    -- Auditoria (36-40)
    created_at                  TIMESTAMPTZ     NOT NULL    DEFAULT NOW(),
    created_by_login_id         INTEGER         NOT NULL,
    updated_at                  TIMESTAMPTZ     NOT NULL    DEFAULT NOW(),
    updated_by_login_id         INTEGER         NULL,
    historial_cambios           JSONB           NULL        DEFAULT '[]'::jsonb,

    -- Soft Delete (41-42)
    soft_delete                 BOOLEAN         NULL        DEFAULT FALSE,
    soft_delete_date            TIMESTAMPTZ     NULL,

    -- Primary Key
    CONSTRAINT pk_planes_pago PRIMARY KEY (id_plan)
);

-- Comentario de la tabla
COMMENT ON TABLE planes_pago IS 'Planes de pago para financiamiento de productos/servicios a clientes';

-- Comentarios de columnas principales
COMMENT ON COLUMN planes_pago.id_plan IS 'Llave primaria autoincremental';
COMMENT ON COLUMN planes_pago.business_id IS 'Multi-tenancy: segrega datos por negocio';
COMMENT ON COLUMN planes_pago.sucursal IS 'Array de sucursales donde aplica el plan';
COMMENT ON COLUMN planes_pago.id_cliente IS 'FK a clientes: El titular/graduado del plan';
COMMENT ON COLUMN planes_pago.invitados IS 'Array JSONB de clientes asociados (familia) - maximo 10';
COMMENT ON COLUMN planes_pago.nombre_plan IS 'Nombre descriptivo del plan';
COMMENT ON COLUMN planes_pago.descripcion IS 'Descripcion libre del plan';
COMMENT ON COLUMN planes_pago.productos_ids IS 'Array de IDs de productos del inventario';
COMMENT ON COLUMN planes_pago.productos_snapshot IS 'Copia de productos al momento de crear el plan';
COMMENT ON COLUMN planes_pago.monto_total IS 'Total a pagar del plan';
COMMENT ON COLUMN planes_pago.monto_enganche IS 'Pago inicial (si aplica)';
COMMENT ON COLUMN planes_pago.monto_financiado IS 'monto_total menos enganche';
COMMENT ON COLUMN planes_pago.numero_parcialidades IS 'Cantidad de cuotas (1 a 36)';
COMMENT ON COLUMN planes_pago.monto_parcialidad IS 'Monto de cada cuota';
COMMENT ON COLUMN planes_pago.total_abonado IS 'Suma de todos los pagos realizados';
COMMENT ON COLUMN planes_pago.saldo_pendiente IS 'Lo que falta por pagar';
COMMENT ON COLUMN planes_pago.porcentaje_cubierto IS 'Porcentaje pagado (0-100)';
COMMENT ON COLUMN planes_pago.periodicidad IS 'Frecuencia de pagos: semanal, catorcenal, quincenal, mensual, bimestral, semestral, anual';
COMMENT ON COLUMN planes_pago.dia_pago IS 'Dia del mes para pago (1-31)';
COMMENT ON COLUMN planes_pago.calendario_pagos IS 'Array JSONB de cuotas con fechas y estados';
COMMENT ON COLUMN planes_pago.monto_mora_acumulada IS 'Total de recargos por mora';
COMMENT ON COLUMN planes_pago.historial_mora IS 'Registro JSONB de recargos aplicados';
COMMENT ON COLUMN planes_pago.politica_mora IS 'Como se cobra la mora: mora_siguiente, mora_obligatorio, mora_opcional, mora_final';
COMMENT ON COLUMN planes_pago.estado IS 'Estado del plan: 1=Activo, 2=Pausado, 3=Completado, 4=Cancelado';
COMMENT ON COLUMN planes_pago.elegible_entregables IS 'TRUE si ha pagado 30% o mas';
COMMENT ON COLUMN planes_pago.fecha_inicio IS 'Cuando inicia el plan';
COMMENT ON COLUMN planes_pago.fecha_primer_pago IS 'Fecha del primer pago programado';
COMMENT ON COLUMN planes_pago.fecha_ultimo_pago IS 'Fecha del pago mas reciente';
COMMENT ON COLUMN planes_pago.fecha_limite IS 'Fecha limite final del plan';
COMMENT ON COLUMN planes_pago.fecha_liquidacion IS 'Cuando se completo al 100%';
COMMENT ON COLUMN planes_pago.fecha_pausa_inicio IS 'Cuando se pauso el plan';
COMMENT ON COLUMN planes_pago.fecha_pausa_fin IS 'Cuando se reactivo el plan';
COMMENT ON COLUMN planes_pago.dias_pausados IS 'Total dias en pausa (acumulativo)';
COMMENT ON COLUMN planes_pago.alertas_activas IS 'Cache JSONB de alertas vigentes';
COMMENT ON COLUMN planes_pago.ultima_evaluacion_alertas IS 'Cuando se evaluaron alertas por ultima vez';
COMMENT ON COLUMN planes_pago.created_at IS 'Timestamp de creacion';
COMMENT ON COLUMN planes_pago.created_by_login_id IS 'Quien creo el plan (FK a login)';
COMMENT ON COLUMN planes_pago.updated_at IS 'Timestamp ultima modificacion';
COMMENT ON COLUMN planes_pago.updated_by_login_id IS 'Quien modifico por ultima vez (FK a login)';
COMMENT ON COLUMN planes_pago.historial_cambios IS 'Registro JSONB de modificaciones';
COMMENT ON COLUMN planes_pago.soft_delete IS 'Borrado logico';
COMMENT ON COLUMN planes_pago.soft_delete_date IS 'Fecha del borrado logico';

-- ============================================================================
-- SECCION 1.1: CONSTRAINTS de planes_pago (6 total)
-- ============================================================================

-- Constraint 1: FK a clientes
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_planes_cliente'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT fk_planes_cliente
        FOREIGN KEY (id_cliente)
        REFERENCES clientes(id_cliente)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION;
    END IF;
END $$;

-- Constraint 2: CHECK periodicidad
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_periodicidad'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT chk_periodicidad
        CHECK (periodicidad IN ('semanal', 'catorcenal', 'quincenal', 'mensual', 'bimestral', 'semestral', 'anual'));
    END IF;
END $$;

-- Constraint 3: CHECK estado
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_estado'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT chk_estado
        CHECK (estado IN (1, 2, 3, 4));
    END IF;
END $$;

-- Constraint 4: CHECK numero_parcialidades
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_parcialidades'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT chk_parcialidades
        CHECK (numero_parcialidades >= 1 AND numero_parcialidades <= 36);
    END IF;
END $$;

-- Constraint 5: CHECK dia_pago
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_dia_pago'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT chk_dia_pago
        CHECK (dia_pago IS NULL OR (dia_pago >= 1 AND dia_pago <= 31));
    END IF;
END $$;

-- Constraint 6: CHECK politica_mora
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_politica_mora'
    ) THEN
        ALTER TABLE planes_pago
        ADD CONSTRAINT chk_politica_mora
        CHECK (politica_mora IS NULL OR politica_mora IN ('mora_siguiente', 'mora_obligatorio', 'mora_opcional', 'mora_final'));
    END IF;
END $$;

-- ============================================================================
-- SECCION 1.2: INDICES de planes_pago (8 total)
-- ============================================================================

-- Indice 1: business_id (BTREE) - Multi-tenancy obligatorio
CREATE INDEX IF NOT EXISTS idx_planes_pago_business_id
ON planes_pago USING btree (business_id);

-- Indice 2: id_cliente (BTREE) - Buscar planes de un cliente
CREATE INDEX IF NOT EXISTS idx_planes_pago_cliente
ON planes_pago USING btree (id_cliente);

-- Indice 3: sucursal (GIN) - Buscar por sucursal (array)
CREATE INDEX IF NOT EXISTS idx_planes_pago_sucursal
ON planes_pago USING gin (sucursal);

-- Indice 4: estado (BTREE) - Filtrar por estado
CREATE INDEX IF NOT EXISTS idx_planes_pago_estado
ON planes_pago USING btree (estado);

-- Indice 5: fecha_limite (BTREE) - Reportes y alertas
CREATE INDEX IF NOT EXISTS idx_planes_pago_fecha_limite
ON planes_pago USING btree (fecha_limite);

-- Indice 6: invitados (GIN) - Buscar planes donde un cliente es invitado
CREATE INDEX IF NOT EXISTS idx_planes_pago_invitados
ON planes_pago USING gin (invitados);

-- Indice 7: productos_ids (GIN) - Buscar planes que incluyen un producto
CREATE INDEX IF NOT EXISTS idx_planes_pago_productos
ON planes_pago USING gin (productos_ids);

-- Indice 8: business_id + estado (BTREE compuesto) - Listados filtrados
CREATE INDEX IF NOT EXISTS idx_planes_pago_business_estado
ON planes_pago USING btree (business_id, estado);

-- ============================================================================
-- SECCION 1.3: TRIGGER de planes_pago (1 total)
-- ============================================================================

-- Eliminar trigger si existe para recrearlo
DROP TRIGGER IF EXISTS trg_planes_pago_updated_at ON planes_pago;

-- Crear trigger para actualizar updated_at automaticamente
CREATE TRIGGER trg_planes_pago_updated_at
    BEFORE UPDATE ON planes_pago
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at_timestamp();

COMMENT ON TRIGGER trg_planes_pago_updated_at ON planes_pago IS 'Actualiza updated_at automaticamente en cada UPDATE';

-- ============================================================================
-- SECCION 2: TABLA pagos_planes (15 columnas)
-- ============================================================================

CREATE TABLE IF NOT EXISTS pagos_planes (
    -- Identificacion (1-5)
    id_pago                     SERIAL          NOT NULL,
    id_plan_pago                INTEGER         NOT NULL,
    business_id                 VARCHAR(100)    NOT NULL,
    sucursal                    INT4[]          NULL,
    id_cliente_pagador          INTEGER         NOT NULL,

    -- Detalles del Pago (6-10)
    tipo_pago_plan              VARCHAR(20)     NOT NULL,
    numero_cuota                INTEGER         NULL,
    monto                       NUMERIC(12,2)   NOT NULL,
    metodo_pago                 INTEGER         NOT NULL,
    efectivo                    JSONB           NULL,

    -- Fecha y Auditoria (11-13)
    fecha_pago                  TIMESTAMPTZ     NOT NULL    DEFAULT NOW(),
    created_by_login_id         INTEGER         NOT NULL,
    notas                       TEXT            NULL,

    -- Soft Delete (14-15)
    soft_delete                 BOOLEAN         NULL        DEFAULT FALSE,
    soft_delete_date            TIMESTAMPTZ     NULL,

    -- Primary Key
    CONSTRAINT pk_pagos_planes PRIMARY KEY (id_pago)
);

-- Comentario de la tabla
COMMENT ON TABLE pagos_planes IS 'Registro de pagos realizados a los planes de pago';

-- Comentarios de columnas
COMMENT ON COLUMN pagos_planes.id_pago IS 'Llave primaria autoincremental';
COMMENT ON COLUMN pagos_planes.id_plan_pago IS 'FK al plan de pago asociado';
COMMENT ON COLUMN pagos_planes.business_id IS 'Multi-tenancy: segrega datos por negocio';
COMMENT ON COLUMN pagos_planes.sucursal IS 'Array de sucursales';
COMMENT ON COLUMN pagos_planes.id_cliente_pagador IS 'FK a clientes: Quien hizo el pago (puede ser cualquier cliente registrado)';
COMMENT ON COLUMN pagos_planes.tipo_pago_plan IS 'Tipo: enganche, abono, mora, ajuste';
COMMENT ON COLUMN pagos_planes.numero_cuota IS 'Numero de cuota (1, 2, 3...) - NULL para enganche/mora/ajuste';
COMMENT ON COLUMN pagos_planes.monto IS 'Cantidad pagada';
COMMENT ON COLUMN pagos_planes.metodo_pago IS '1=efectivo, 2=transferencia, 3=tarjeta';
COMMENT ON COLUMN pagos_planes.efectivo IS 'Desglose JSONB de billetes si metodo_pago=1';
COMMENT ON COLUMN pagos_planes.fecha_pago IS 'Cuando se realizo el pago';
COMMENT ON COLUMN pagos_planes.created_by_login_id IS 'Quien registro el pago (FK a login)';
COMMENT ON COLUMN pagos_planes.notas IS 'Notas adicionales del pago';
COMMENT ON COLUMN pagos_planes.soft_delete IS 'Borrado logico';
COMMENT ON COLUMN pagos_planes.soft_delete_date IS 'Fecha del borrado logico';

-- ============================================================================
-- SECCION 2.1: CONSTRAINTS de pagos_planes (4 total)
-- ============================================================================

-- Constraint 1: FK a planes_pago
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_pago_plan'
    ) THEN
        ALTER TABLE pagos_planes
        ADD CONSTRAINT fk_pago_plan
        FOREIGN KEY (id_plan_pago)
        REFERENCES planes_pago(id_plan)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION;
    END IF;
END $$;

-- Constraint 2: FK a clientes (pagador)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_pago_pagador'
    ) THEN
        ALTER TABLE pagos_planes
        ADD CONSTRAINT fk_pago_pagador
        FOREIGN KEY (id_cliente_pagador)
        REFERENCES clientes(id_cliente)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION;
    END IF;
END $$;

-- Constraint 3: CHECK tipo_pago_plan
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_tipo_pago_plan'
    ) THEN
        ALTER TABLE pagos_planes
        ADD CONSTRAINT chk_tipo_pago_plan
        CHECK (tipo_pago_plan IN ('enganche', 'abono', 'mora', 'ajuste'));
    END IF;
END $$;

-- Constraint 4: CHECK metodo_pago
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_metodo_pago'
    ) THEN
        ALTER TABLE pagos_planes
        ADD CONSTRAINT chk_metodo_pago
        CHECK (metodo_pago IN (1, 2, 3));
    END IF;
END $$;

-- ============================================================================
-- SECCION 2.2: INDICES de pagos_planes (5 total)
-- ============================================================================

-- Indice 1: id_plan_pago (BTREE) - Obtener todos los pagos de un plan
CREATE INDEX IF NOT EXISTS idx_pagos_planes_plan
ON pagos_planes USING btree (id_plan_pago);

-- Indice 2: id_cliente_pagador (BTREE) - Buscar pagos hechos por un cliente
CREATE INDEX IF NOT EXISTS idx_pagos_planes_pagador
ON pagos_planes USING btree (id_cliente_pagador);

-- Indice 3: business_id (BTREE) - Multi-tenancy
CREATE INDEX IF NOT EXISTS idx_pagos_planes_business
ON pagos_planes USING btree (business_id);

-- Indice 4: fecha_pago (BTREE) - Reportes por rango de fechas
CREATE INDEX IF NOT EXISTS idx_pagos_planes_fecha
ON pagos_planes USING btree (fecha_pago);

-- Indice 5: tipo_pago_plan (BTREE) - Filtrar por tipo de pago
CREATE INDEX IF NOT EXISTS idx_pagos_planes_tipo
ON pagos_planes USING btree (tipo_pago_plan);

-- ============================================================================
-- SECCION 3: TABLA configuraciones_planes_pago (7 columnas)
-- ============================================================================

CREATE TABLE IF NOT EXISTS configuraciones_planes_pago (
    -- Identificacion (1-3)
    id                          SERIAL          NOT NULL,
    business_id                 VARCHAR(100)    NOT NULL,
    clave                       VARCHAR(100)    NOT NULL,

    -- Valor y Descripcion (4-5)
    valor                       JSONB           NOT NULL,
    descripcion                 TEXT            NULL,

    -- Auditoria (6-7)
    created_at                  TIMESTAMPTZ     NULL        DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ     NULL        DEFAULT NOW(),

    -- Primary Key
    CONSTRAINT pk_config_planes_pago PRIMARY KEY (id)
);

-- Comentario de la tabla
COMMENT ON TABLE configuraciones_planes_pago IS 'Parametros de configuracion del sistema de planes de pago por negocio';

-- Comentarios de columnas
COMMENT ON COLUMN configuraciones_planes_pago.id IS 'Llave primaria autoincremental';
COMMENT ON COLUMN configuraciones_planes_pago.business_id IS 'Multi-tenancy: segrega datos por negocio';
COMMENT ON COLUMN configuraciones_planes_pago.clave IS 'Identificador de la configuracion (ej: niveles_mora, alertas_planes_pago)';
COMMENT ON COLUMN configuraciones_planes_pago.valor IS 'Valor de la configuracion en formato JSONB';
COMMENT ON COLUMN configuraciones_planes_pago.descripcion IS 'Descripcion de que hace esta configuracion';
COMMENT ON COLUMN configuraciones_planes_pago.created_at IS 'Timestamp de creacion';
COMMENT ON COLUMN configuraciones_planes_pago.updated_at IS 'Timestamp ultima modificacion';

-- ============================================================================
-- SECCION 3.1: CONSTRAINTS de configuraciones_planes_pago (1 total)
-- ============================================================================

-- Constraint 1: UNIQUE business_id + clave
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_config_planes'
    ) THEN
        ALTER TABLE configuraciones_planes_pago
        ADD CONSTRAINT uq_config_planes
        UNIQUE (business_id, clave);
    END IF;
END $$;

-- ============================================================================
-- SECCION 3.2: INDICES de configuraciones_planes_pago (1 total)
-- ============================================================================

-- Indice 1: business_id (BTREE) - Obtener todas las configuraciones de un negocio
CREATE INDEX IF NOT EXISTS idx_config_planes_business
ON configuraciones_planes_pago USING btree (business_id);

-- ============================================================================
-- SECCION 4: MODIFICACION TABLA notas_internas
-- ============================================================================

-- Agregar columna id_plan_pago si no existe
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'notas_internas' AND column_name = 'id_plan_pago'
    ) THEN
        ALTER TABLE notas_internas ADD COLUMN id_plan_pago BIGINT NULL;
        COMMENT ON COLUMN notas_internas.id_plan_pago IS 'FK a planes_pago: vincula nota a un plan especifico (opcional)';
    END IF;
END $$;

-- Agregar FK a planes_pago
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_notas_plan'
    ) THEN
        ALTER TABLE notas_internas
        ADD CONSTRAINT fk_notas_plan
        FOREIGN KEY (id_plan_pago)
        REFERENCES planes_pago(id_plan)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION;
    END IF;
END $$;

-- Agregar indice para id_plan_pago
CREATE INDEX IF NOT EXISTS idx_notas_plan_pago
ON notas_internas USING btree (id_plan_pago);

-- ============================================================================
-- SECCION 5: MODIFICACION TABLA configuraciones
-- ============================================================================

-- Agregar columna planes_pago_config si no existe
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'configuraciones' AND column_name = 'planes_pago_config'
    ) THEN
        ALTER TABLE configuraciones ADD COLUMN planes_pago_config JSONB NULL DEFAULT '{}'::jsonb;
        COMMENT ON COLUMN configuraciones.planes_pago_config IS 'Configuraciones de planes de pago por sucursal: politica de mora, reglas de cancelacion, limites';
    END IF;
END $$;

-- ============================================================================
-- SECCION 6: VERIFICACION FINAL
-- ============================================================================

-- Verificar que las tablas fueron creadas
DO $$
DECLARE
    v_count INTEGER;
BEGIN
    -- Verificar planes_pago
    SELECT COUNT(*) INTO v_count FROM information_schema.tables
    WHERE table_name = 'planes_pago' AND table_schema = 'public';
    IF v_count = 0 THEN
        RAISE EXCEPTION 'ERROR: Tabla planes_pago no fue creada';
    END IF;

    -- Verificar pagos_planes
    SELECT COUNT(*) INTO v_count FROM information_schema.tables
    WHERE table_name = 'pagos_planes' AND table_schema = 'public';
    IF v_count = 0 THEN
        RAISE EXCEPTION 'ERROR: Tabla pagos_planes no fue creada';
    END IF;

    -- Verificar configuraciones_planes_pago
    SELECT COUNT(*) INTO v_count FROM information_schema.tables
    WHERE table_name = 'configuraciones_planes_pago' AND table_schema = 'public';
    IF v_count = 0 THEN
        RAISE EXCEPTION 'ERROR: Tabla configuraciones_planes_pago no fue creada';
    END IF;

    -- Verificar columna en notas_internas
    SELECT COUNT(*) INTO v_count FROM information_schema.columns
    WHERE table_name = 'notas_internas' AND column_name = 'id_plan_pago';
    IF v_count = 0 THEN
        RAISE EXCEPTION 'ERROR: Columna id_plan_pago no fue agregada a notas_internas';
    END IF;

    -- Verificar columna en configuraciones
    SELECT COUNT(*) INTO v_count FROM information_schema.columns
    WHERE table_name = 'configuraciones' AND column_name = 'planes_pago_config';
    IF v_count = 0 THEN
        RAISE EXCEPTION 'ERROR: Columna planes_pago_config no fue agregada a configuraciones';
    END IF;

    RAISE NOTICE 'VERIFICACION EXITOSA: Todas las tablas y columnas fueron creadas correctamente';
END $$;

-- ============================================================================
-- FIN DE LA MIGRACION
-- ============================================================================
