CREATE TABLE loan_proposal_status_catalog (
    slug VARCHAR(50) PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    pipeline_group VARCHAR(50) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_final TINYINT(1) NOT NULL DEFAULT 0,
    is_positive TINYINT(1) NOT NULL DEFAULT 0,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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);

CREATE TABLE loan_proposals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    broker_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NOT NULL,
    api_integration_id INT UNSIGNED NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'digitado',
    product_type VARCHAR(60) NOT NULL,
    requested_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    approved_amount DECIMAL(12,2) NULL,
    disbursed_amount DECIMAL(12,2) NULL,
    installment_value DECIMAL(12,2) NULL,
    term_months SMALLINT UNSIGNED NULL,
    interest_rate DECIMAL(8,4) NULL,
    commission_value DECIMAL(12,2) NULL,
    commission_percent DECIMAL(5,2) NULL,
    sign_method ENUM('digital','presencial','biometria') NOT NULL DEFAULT 'digital',
    external_reference VARCHAR(120) NULL,
    submitted_at DATETIME NULL,
    approved_at DATETIME NULL,
    funded_at DATETIME NULL,
    paid_at DATETIME NULL,
    last_synced_at DATETIME NULL,
    next_sync_at DATETIME NULL,
    notes TEXT NULL,
    metadata LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_loan_proposal_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_loan_proposal_broker FOREIGN KEY (broker_id) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_proposal_employee FOREIGN KEY (employee_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_loan_proposal_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_proposal_integration FOREIGN KEY (api_integration_id) REFERENCES api_integrations (id) ON DELETE SET NULL,
    CONSTRAINT fk_loan_proposal_status FOREIGN KEY (status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    INDEX idx_loan_proposals_status (status),
    INDEX idx_loan_proposals_broker_status (broker_id, status),
    INDEX idx_loan_proposals_employee (employee_id),
    INDEX idx_loan_proposals_product (product_type),
    INDEX idx_loan_proposals_submitted (submitted_at),
    INDEX idx_loan_proposals_funded (funded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_status_history (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    old_status VARCHAR(50) NULL,
    new_status VARCHAR(50) NOT NULL,
    description TEXT NULL,
    payload LONGTEXT NULL,
    changed_by INT UNSIGNED NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    CONSTRAINT fk_history_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    CONSTRAINT fk_history_old_status FOREIGN KEY (old_status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    CONSTRAINT fk_history_new_status FOREIGN KEY (new_status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    CONSTRAINT fk_history_user FOREIGN KEY (changed_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_history_proposal (proposal_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    label VARCHAR(120) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size INT UNSIGNED NULL,
    status ENUM('pending','uploaded','approved','rejected') NOT NULL DEFAULT 'pending',
    uploaded_by INT UNSIGNED NULL,
    uploaded_at DATETIME NULL,
    reviewed_at DATETIME NULL,
    expires_at DATETIME NULL,
    review_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_documents_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_documents_proposal (proposal_id),
    INDEX idx_documents_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_bank_payloads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    direction ENUM('request','response','webhook') NOT NULL,
    endpoint VARCHAR(150) NOT NULL,
    payload LONGTEXT NOT NULL,
    status_code SMALLINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payloads_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    INDEX idx_payloads_proposal (proposal_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;