ALTER TABLE operations ADD COLUMN IF NOT EXISTS risk_level ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium' AFTER status;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS compliance_status VARCHAR(30) NOT NULL DEFAULT 'pending' AFTER risk_level;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS expected_close_date DATE NULL AFTER destination;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS sanctions_status VARCHAR(30) NOT NULL DEFAULT 'not_checked' AFTER risk_rating;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS pep_status VARCHAR(30) NOT NULL DEFAULT 'not_checked' AFTER sanctions_status;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS beneficial_owners_json JSON NULL AFTER pep_status;
CREATE TABLE IF NOT EXISTS compliance_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, operation_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NULL, assigned_user_id BIGINT UNSIGNED NULL, case_type VARCHAR(60) NOT NULL DEFAULT 'transaction_due_diligence', risk_level ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium', status ENUM('pending','in_review','approved','rejected','on_hold') NOT NULL DEFAULT 'pending', decision_notes TEXT NULL, opened_at DATETIME NOT NULL, decided_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_compliance_operation(operation_id), FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX(status,risk_level)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS compliance_checks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, compliance_case_id BIGINT UNSIGNED NOT NULL, check_type VARCHAR(80) NOT NULL, label VARCHAR(190) NOT NULL, status ENUM('pending','clear','alert','not_applicable') NOT NULL DEFAULT 'pending', source_reference VARCHAR(255) NULL, notes TEXT NULL, checked_by BIGINT UNSIGNED NULL, checked_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY(compliance_case_id) REFERENCES compliance_cases(id) ON DELETE CASCADE, FOREIGN KEY(checked_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(compliance_case_id,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS client_kyc_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, document_type VARCHAR(80) NOT NULL, document_number VARCHAR(120) NULL, issuing_country VARCHAR(100) NULL, issue_date DATE NULL, expiry_date DATE NULL, file_path VARCHAR(255) NULL, verification_status ENUM('pending','verified','rejected','expired') NOT NULL DEFAULT 'pending', verified_by BIGINT UNSIGNED NULL, verified_at DATETIME NULL, notes TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(client_id,verification_status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_extractions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, operation_document_id BIGINT UNSIGNED NULL, operation_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NULL, provider VARCHAR(60) NOT NULL DEFAULT 'manual', source_text LONGTEXT NULL, extracted_json JSON NULL, confidence DECIMAL(5,2) NULL, status ENUM('received','processing','review_required','approved','rejected','applied') NOT NULL DEFAULT 'review_required', reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, applied_at DATETIME NULL, error_message TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL, FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(status,created_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS operation_ledger (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, operation_id BIGINT UNSIGNED NOT NULL, bank_instrument_id BIGINT UNSIGNED NULL, instrument_loan_id BIGINT UNSIGNED NULL, entry_type ENUM('inflow','outflow','commitment','release','adjustment') NOT NULL, category VARCHAR(100) NOT NULL, description VARCHAR(255) NULL, amount DECIMAL(20,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', reference_date DATE NOT NULL, status ENUM('planned','confirmed','cancelled') NOT NULL DEFAULT 'planned', counterparty VARCHAR(190) NULL, reference_number VARCHAR(120) NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE, FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE SET NULL, FOREIGN KEY(instrument_loan_id) REFERENCES instrument_loans(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(operation_id,reference_date), INDEX(status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS fund_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, operation_id BIGINT UNSIGNED NOT NULL, source_ledger_id BIGINT UNSIGNED NULL, allocation_type ENUM('commodity_purchase','reinvestment','bank_trade','fees','commission','logistics','tax','other') NOT NULL, beneficiary VARCHAR(190) NULL, description VARCHAR(255) NULL, amount DECIMAL(20,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', planned_date DATE NULL, executed_at DATETIME NULL, status ENUM('planned','approved','executed','cancelled') NOT NULL DEFAULT 'planned', approved_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE, FOREIGN KEY(source_ledger_id) REFERENCES operation_ledger(id) ON DELETE SET NULL, FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(operation_id,status)
) ENGINE=InnoDB;