CREATE TABLE integration_bem_tokens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    jwt_token TEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at_estimated DATETIME NULL,
    last_header_token TEXT NULL,
    environment VARCHAR(20) NOT NULL DEFAULT 'prod',
    UNIQUE KEY idx_bem_tokens_env (environment)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bem_proposals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    crm_proposal_id INT UNSIGNED NULL,
    bem_proposta_numero BIGINT NULL,
    bem_protocolo VARCHAR(120) NULL,
    tipo_operacao ENUM('NOVO','REFIN','PORTAB','PORTAB_REFIN') NOT NULL,
    cpf_cliente VARCHAR(20) NOT NULL,
    cpf_agente VARCHAR(20) NOT NULL,
    conveniada VARCHAR(50) NOT NULL,
    orgao VARCHAR(80) NOT NULL,
    plano VARCHAR(50) NOT NULL,
    prazo VARCHAR(20) NOT NULL,
    status_local ENUM(
        'RASCUNHO','SIMULADA','ENVIADA','EM_ANDAMENTO','PENDENTE_DOC','PENDENTE_ASSINATURA',
        'PENDENTE_ACEITE','APROVADA','REPROVADA','CANCELADA'
    ) NOT NULL DEFAULT 'RASCUNHO',
    situacao_codigo VARCHAR(50) NULL,
    situacao_descricao VARCHAR(150) NULL,
    situacao_detalhada TEXT NULL,
    permissoes_json JSON NULL,
    demandas_json JSON NULL,
    docs_pendentes_json JSON NULL,
    resumo_json JSON NULL,
    payload_submit_json JSON NULL,
    payload_response_json JSON NULL,
    last_sync_at DATETIME NULL,
    needs_attention TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_bem_proposal_crm FOREIGN KEY (crm_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    INDEX idx_bem_proposal_crm (crm_proposal_id),
    INDEX idx_bem_proposal_numero (bem_proposta_numero),
    INDEX idx_bem_proposal_protocolo (bem_protocolo),
    INDEX idx_bem_proposal_cpf (cpf_cliente),
    INDEX idx_bem_proposal_status (status_local),
    INDEX idx_bem_proposal_sync (last_sync_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bem_sync_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    run_type ENUM('MANUAL','CRON','IMPORT_INCLUSAO','IMPORT_MOVIMENTACAO','IMPORT_ULTIMAS') NOT NULL,
    ref_date DATE NULL,
    pagina_atual INT UNSIGNED NOT NULL DEFAULT 0,
    finished TINYINT(1) NOT NULL DEFAULT 0,
    log TEXT NULL,
    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;