SET @has_is_system_col := (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'loan_proposal_status_catalog'
      AND column_name = 'is_system'
);

SET @add_is_system_sql := IF(
    @has_is_system_col > 0,
    'DO 0',
    'ALTER TABLE loan_proposal_status_catalog ADD COLUMN is_system TINYINT(1) NOT NULL DEFAULT 0 AFTER is_positive'
);

PREPARE alter_status_catalog_stmt FROM @add_is_system_sql;
EXECUTE alter_status_catalog_stmt;
DEALLOCATE PREPARE alter_status_catalog_stmt;

UPDATE loan_proposal_status_catalog
SET slug = 'digitado'
WHERE slug = 'draft'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'digitado' LIMIT 1
        ) AS existing_digitado
    );
UPDATE loan_proposals SET status = 'digitado' WHERE status = 'draft';
UPDATE loan_proposal_status_history SET old_status = 'digitado' WHERE old_status = 'draft';
UPDATE loan_proposal_status_history SET new_status = 'digitado' WHERE new_status = 'draft';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'draft';

UPDATE loan_proposal_status_catalog
SET slug = 'ag_assinatura'
WHERE slug = 'internal_review'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'ag_assinatura' LIMIT 1
        ) AS existing_ag_assinatura
    );
UPDATE loan_proposals SET status = 'ag_assinatura' WHERE status = 'internal_review';
UPDATE loan_proposal_status_history SET old_status = 'ag_assinatura' WHERE old_status = 'internal_review';
UPDATE loan_proposal_status_history SET new_status = 'ag_assinatura' WHERE new_status = 'internal_review';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'internal_review';

UPDATE loan_proposal_status_catalog
SET slug = 'pendente_documento'
WHERE slug = 'pending_docs'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'pendente_documento' LIMIT 1
        ) AS existing_pendente_doc
    );
UPDATE loan_proposals SET status = 'pendente_documento' WHERE status = 'pending_docs';
UPDATE loan_proposal_status_history SET old_status = 'pendente_documento' WHERE old_status = 'pending_docs';
UPDATE loan_proposal_status_history SET new_status = 'pendente_documento' WHERE new_status = 'pending_docs';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'pending_docs';

UPDATE loan_proposal_status_catalog
SET slug = 'enviada'
WHERE slug = 'submitted'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'enviada' LIMIT 1
        ) AS existing_enviada
    );
UPDATE loan_proposals SET status = 'enviada' WHERE status = 'submitted';
UPDATE loan_proposal_status_history SET old_status = 'enviada' WHERE old_status = 'submitted';
UPDATE loan_proposal_status_history SET new_status = 'enviada' WHERE new_status = 'submitted';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'submitted';

UPDATE loan_proposal_status_catalog
SET slug = 'analise_banco'
WHERE slug = 'bank_review'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'analise_banco' LIMIT 1
        ) AS existing_analise
    );
UPDATE loan_proposals SET status = 'analise_banco' WHERE status = 'bank_review';
UPDATE loan_proposal_status_history SET old_status = 'analise_banco' WHERE old_status = 'bank_review';
UPDATE loan_proposal_status_history SET new_status = 'analise_banco' WHERE new_status = 'bank_review';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'bank_review';

UPDATE loan_proposal_status_catalog
SET slug = 'ag_saldo_devedor'
WHERE slug = 'approved'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'ag_saldo_devedor' LIMIT 1
        ) AS existing_ag_saldo
    );
UPDATE loan_proposals SET status = 'ag_saldo_devedor' WHERE status = 'approved';
UPDATE loan_proposal_status_history SET old_status = 'ag_saldo_devedor' WHERE old_status = 'approved';
UPDATE loan_proposal_status_history SET new_status = 'ag_saldo_devedor' WHERE new_status = 'approved';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'approved';

UPDATE loan_proposal_status_catalog
SET slug = 'enviado_quitacao'
WHERE slug = 'funded'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'enviado_quitacao' LIMIT 1
        ) AS existing_enviado
    );
UPDATE loan_proposals SET status = 'enviado_quitacao' WHERE status = 'funded';
UPDATE loan_proposal_status_history SET old_status = 'enviado_quitacao' WHERE old_status = 'funded';
UPDATE loan_proposal_status_history SET new_status = 'enviado_quitacao' WHERE new_status = 'funded';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'funded';

UPDATE loan_proposal_status_catalog
SET slug = 'saldo_quitado'
WHERE slug = 'paid'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'saldo_quitado' LIMIT 1
        ) AS existing_saldo
    );
UPDATE loan_proposals SET status = 'saldo_quitado' WHERE status = 'paid';
UPDATE loan_proposal_status_history SET old_status = 'saldo_quitado' WHERE old_status = 'paid';
UPDATE loan_proposal_status_history SET new_status = 'saldo_quitado' WHERE new_status = 'paid';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'paid';

UPDATE loan_proposal_status_catalog
SET slug = 'reprovada'
WHERE slug = 'rejected'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'reprovada' LIMIT 1
        ) AS existing_reprovada
    );
UPDATE loan_proposals SET status = 'reprovada' WHERE status = 'rejected';
UPDATE loan_proposal_status_history SET old_status = 'reprovada' WHERE old_status = 'rejected';
UPDATE loan_proposal_status_history SET new_status = 'reprovada' WHERE new_status = 'rejected';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'rejected';

UPDATE loan_proposal_status_catalog
SET slug = 'cancelada'
WHERE slug = 'cancelled'
  AND NOT EXISTS (
        SELECT 1 FROM (
            SELECT slug FROM loan_proposal_status_catalog WHERE slug = 'cancelada' LIMIT 1
        ) AS existing_cancelada
    );
UPDATE loan_proposals SET status = 'cancelada' WHERE status = 'cancelled';
UPDATE loan_proposal_status_history SET old_status = 'cancelada' WHERE old_status = 'cancelled';
UPDATE loan_proposal_status_history SET new_status = 'cancelada' WHERE new_status = 'cancelled';
DELETE FROM loan_proposal_status_catalog WHERE slug = 'cancelled';

INSERT INTO loan_proposal_status_catalog (slug, label, pipeline_group, sort_order, is_final, is_positive, is_system)
VALUES
    ('digitado', 'Digitado', 'preparation', 10, 0, 0, 1),
    ('ag_assinatura', 'Ag. assinatura', 'preparation', 20, 0, 0, 1),
    ('pendente_documento', 'Pend. documento', 'preparation', 30, 0, 0, 1),
    ('enviada', 'Enviada', 'bank_submission', 40, 0, 0, 1),
    ('analise_banco', 'Analise banco', 'bank_process', 50, 0, 0, 1),
    ('ag_saldo_devedor', 'Ag. saldo devedor', 'results', 60, 1, 1, 1),
    ('enviado_quitacao', 'Enviado quitacao', 'results', 70, 1, 1, 1),
    ('saldo_quitado', 'Saldo quitado', 'after_sales', 80, 1, 1, 1),
    ('reprovada', 'Reprovada', 'results', 90, 1, 0, 1),
    ('cancelada', 'Cancelada', 'results', 100, 1, 0, 1)
ON DUPLICATE KEY UPDATE
    label = VALUES(label),
    pipeline_group = VALUES(pipeline_group),
    sort_order = VALUES(sort_order),
    is_final = VALUES(is_final),
    is_positive = VALUES(is_positive),
    is_system = VALUES(is_system);

ALTER TABLE loan_proposals
    MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'digitado';