CREATE TABLE commission_rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    receiver_role ENUM('admin','broker','employee') NOT NULL,
    receiver_user_id INT UNSIGNED NULL,
    match_broker_id INT UNSIGNED NULL,
    match_employee_id INT UNSIGNED NULL,
    bank_code VARCHAR(20) NULL,
    rate_table_slug VARCHAR(80) NULL,
    product_type VARCHAR(80) NULL,
    sub_product_id INT UNSIGNED NULL,
    operation_type VARCHAR(60) NULL,
    min_requested_amount DECIMAL(14,2) NULL,
    max_requested_amount DECIMAL(14,2) NULL,
    commission_type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
    commission_value DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    priority_override TINYINT NOT NULL DEFAULT 0,
    specificity_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    valid_from DATE NULL,
    valid_to DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_rules_receiver_user FOREIGN KEY (receiver_user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rules_match_broker FOREIGN KEY (match_broker_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rules_match_employee FOREIGN KEY (match_employee_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rules_sub_product FOREIGN KEY (sub_product_id) REFERENCES loan_sub_products (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rules_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rules_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_commission_rules_target (receiver_role, receiver_user_id),
    INDEX idx_commission_rules_filters (bank_code, rate_table_slug, product_type),
    INDEX idx_commission_rules_specificity (is_active, specificity_score, priority_override),
    INDEX idx_commission_rules_validity (valid_from, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    loan_proposal_id INT UNSIGNED NOT NULL,
    lead_id INT UNSIGNED NOT NULL,
    broker_id INT UNSIGNED NULL,
    employee_id INT UNSIGNED NULL,
    primary_rule_id BIGINT UNSIGNED NULL,
    status ENUM('pending_release','reporting_window','wallet','paid','cancelled') NOT NULL DEFAULT 'pending_release',
    disbursed_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    commission_base_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    commission_total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    commission_currency CHAR(3) NOT NULL DEFAULT 'BRL',
    commission_percent_applied DECIMAL(10,4) NULL,
    applied_rules JSON NULL,
    proposal_status_snapshot VARCHAR(40) NOT NULL,
    proposal_paid_at DATETIME NULL,
    eligible_at DATETIME NOT NULL,
    report_available_on DATE NULL,
    report_closes_on DATE NULL,
    wallet_available_on DATE NULL,
    paid_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    cancel_reason VARCHAR(255) NULL,
    notes TEXT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_entries_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_entries_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_entries_broker FOREIGN KEY (broker_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_entries_employee FOREIGN KEY (employee_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_entries_primary_rule FOREIGN KEY (primary_rule_id) REFERENCES commission_rules (id) ON DELETE SET NULL,
    INDEX idx_commission_entries_status (status, report_available_on),
    INDEX idx_commission_entries_wallet (wallet_available_on),
    INDEX idx_commission_entries_users (broker_id, employee_id),
    INDEX idx_commission_entries_proposal (loan_proposal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_entry_shares (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entry_id BIGINT UNSIGNED NOT NULL,
    receiver_role ENUM('admin','broker','employee') NOT NULL,
    receiver_user_id INT UNSIGNED NULL,
    direction ENUM('credit','debit','adjustment') NOT NULL DEFAULT 'credit',
    gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    repasse_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    commission_percent DECIMAL(10,4) NULL,
    commission_rule_id BIGINT UNSIGNED NULL,
    status ENUM('pending_release','reporting_window','wallet','paid','cancelled') NOT NULL DEFAULT 'pending_release',
    due_at DATE NULL,
    reported_at DATETIME NULL,
    wallet_posted_at DATETIME NULL,
    paid_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    visible_to_owner TINYINT(1) NOT NULL DEFAULT 1,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_shares_entry FOREIGN KEY (entry_id) REFERENCES commission_entries (id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_shares_receiver FOREIGN KEY (receiver_user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_shares_rule FOREIGN KEY (commission_rule_id) REFERENCES commission_rules (id) ON DELETE SET NULL,
    INDEX idx_commission_shares_receiver (receiver_role, receiver_user_id),
    INDEX idx_commission_shares_status (status),
    INDEX idx_commission_shares_entry (entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_wallets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    role ENUM('admin','broker','employee') NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'BRL',
    available_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    pending_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    hold_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    last_reconciled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_wallet_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    UNIQUE KEY uq_commission_wallet_user (user_id, role, currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_wallet_transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wallet_id BIGINT UNSIGNED NOT NULL,
    entry_share_id BIGINT UNSIGNED NULL,
    type ENUM('credit','debit','adjustment','transfer') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    balance_after DECIMAL(14,2) NOT NULL,
    description VARCHAR(255) NULL,
    reference_date DATE NULL,
    occurred_at DATETIME NOT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_wallet_transactions_wallet FOREIGN KEY (wallet_id) REFERENCES commission_wallets (id) ON DELETE CASCADE,
    CONSTRAINT fk_wallet_transactions_share FOREIGN KEY (entry_share_id) REFERENCES commission_entry_shares (id) ON DELETE SET NULL,
    INDEX idx_wallet_transactions_wallet (wallet_id, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_adjustments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entry_id BIGINT UNSIGNED NULL,
    entry_share_id BIGINT UNSIGNED NULL,
    adjustment_type ENUM('increase','decrease','glosa','reprocess') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    reason VARCHAR(255) NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_adjust_entry FOREIGN KEY (entry_id) REFERENCES commission_entries (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_adjust_share FOREIGN KEY (entry_share_id) REFERENCES commission_entry_shares (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_adjust_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_commission_adjust_entry (entry_id),
    INDEX idx_commission_adjust_share (entry_share_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_rule_audits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rule_id BIGINT UNSIGNED NULL,
    action VARCHAR(40) NOT NULL,
    payload JSON NULL,
    snapshot LONGTEXT NULL,
    performed_by INT UNSIGNED NULL,
    performed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_commission_rule_audits_rule FOREIGN KEY (rule_id) REFERENCES commission_rules (id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_rule_audits_user FOREIGN KEY (performed_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_commission_rule_audits_rule (rule_id),
    INDEX idx_commission_rule_audits_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;