-- Normalizacion de forma de pago y medios de pago para facturacion

CREATE TABLE IF NOT EXISTS catalogo_formas_pago (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(20) NOT NULL,
    descripcion VARCHAR(120) NOT NULL,
    comentario VARCHAR(255) NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_catalogo_formas_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO catalogo_formas_pago (id, codigo, descripcion, comentario, estado, created_at) VALUES
(1, 'CONTADO', 'Contado', 'Pago al momento de la operacion', 1, NOW()),
(2, 'CREDITO', 'Credito', 'Pago diferido o en cuotas', 1, NOW());

CREATE TABLE IF NOT EXISTS catalogo_medios_pago (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(30) NOT NULL,
    descripcion VARCHAR(120) NOT NULL,
    comentario VARCHAR(255) NULL,
    es_credito TINYINT(1) NOT NULL DEFAULT 0,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_catalogo_medios_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO catalogo_medios_pago (id, codigo, descripcion, comentario, estado, created_at) VALUES
(1, 'EFECTIVO', 'Efectivo', 'Pago en efectivo', 1, NOW()),
(2, 'CREDITO', 'Credito', 'Pago diferido o en cuotas', 1, NOW()),
(3, 'YAPE', 'Yape', 'Pago por Yape', 1, NOW()),
(4, 'PLIN', 'Plin', 'Pago por Plin', 1, NOW()),
(5, 'TRANSFERENCIA', 'Transferencia', 'Transferencia bancaria', 1, NOW()),
(6, 'CHEQUE', 'Cheque', 'Pago con cheque', 1, NOW()),
(7, 'TARJETA', 'Tarjeta', 'Pago con tarjeta de debito o credito', 1, NOW()),
(8, 'DEPOSITO', 'Deposito', 'Deposito bancario', 1, NOW()),
(9, 'OTRO', 'Otro', 'Otro medio de pago', 1, NOW());

UPDATE catalogo_medios_pago
SET es_credito = CASE WHEN UPPER(codigo) = 'CREDITO' THEN 1 ELSE 0 END;

SET @sql_add_forma_pago_id := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN forma_pago_id BIGINT UNSIGNED NULL AFTER moneda',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'forma_pago_id'
);
PREPARE stmt_add_forma_pago_id FROM @sql_add_forma_pago_id;
EXECUTE stmt_add_forma_pago_id;
DEALLOCATE PREPARE stmt_add_forma_pago_id;

UPDATE facturacion_comprobantes
SET forma_pago_id = CASE UPPER(COALESCE(forma_pago, 'CONTADO'))
    WHEN 'CREDITO' THEN 2
    ELSE 1
END
WHERE forma_pago_id IS NULL;

SET @sql_add_forma_fk := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD CONSTRAINT fk_fact_comprobante_forma_pago FOREIGN KEY (forma_pago_id) REFERENCES catalogo_formas_pago(id)',
        'SELECT 1'
    )
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND constraint_name = 'fk_fact_comprobante_forma_pago'
);
PREPARE stmt_add_forma_fk FROM @sql_add_forma_fk;
EXECUTE stmt_add_forma_fk;
DEALLOCATE PREPARE stmt_add_forma_fk;

SET @sql_forma_not_null := 'ALTER TABLE facturacion_comprobantes MODIFY COLUMN forma_pago_id BIGINT UNSIGNED NOT NULL COMMENT ''Referencia a catalogo_formas_pago''';
PREPARE stmt_forma_not_null FROM @sql_forma_not_null;
EXECUTE stmt_forma_not_null;
DEALLOCATE PREPARE stmt_forma_not_null;

SET @sql_drop_forma_legacy := (
    SELECT IF(
        COUNT(*) > 0,
        'ALTER TABLE facturacion_comprobantes DROP COLUMN forma_pago',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'forma_pago'
);
PREPARE stmt_drop_forma_legacy FROM @sql_drop_forma_legacy;
EXECUTE stmt_drop_forma_legacy;
DEALLOCATE PREPARE stmt_drop_forma_legacy;

CREATE TABLE IF NOT EXISTS facturacion_comprobante_pagos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comprobante_id BIGINT UNSIGNED NOT NULL,
    medio_pago_id BIGINT UNSIGNED NOT NULL,
    monto DECIMAL(14,2) NOT NULL DEFAULT 0,
    referencia VARCHAR(150) NULL,
    fecha_pago DATE NULL,
    observacion VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_fact_pago_comprobante (comprobante_id),
    KEY idx_fact_pago_medio (medio_pago_id),
    CONSTRAINT fk_fact_pago_comprobante FOREIGN KEY (comprobante_id) REFERENCES facturacion_comprobantes(id),
    CONSTRAINT fk_fact_pago_medio FOREIGN KEY (medio_pago_id) REFERENCES catalogo_medios_pago(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;