-- V005 - commercial automation, payment schedules, commission splits and risk monitoring
ALTER TABLE operations ADD COLUMN IF NOT EXISTS priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal' AFTER stage;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS expected_close_date DATE NULL AFTER priority;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS probability_percent TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER expected_close_date;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS owner_user_id BIGINT UNSIGNED NULL AFTER probability_percent;

CREATE TABLE IF NOT EXISTS counterparties (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 type ENUM('supplier','farmer','mill','trade','producer','slaughterhouse','carrier','bank','financial_institution','inspection_company','other') NOT NULL,
 legal_name VARCHAR(190) NOT NULL,
 trade_name VARCHAR(190) NULL,
 country_code CHAR(2) NULL,
 tax_id VARCHAR(80) NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(80) NULL,
 website VARCHAR(190) NULL,
 contact_name VARCHAR(190) NULL,
 products_json JSON NULL,
 capacity_notes TEXT NULL,
 compliance_status ENUM('pending','approved','restricted','rejected') NOT NULL DEFAULT 'pending',
 risk_rating ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(type,active), INDEX(legal_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_counterparties (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 role VARCHAR(80) NOT NULL,
 commercial_terms TEXT NULL,
 status ENUM('prospect','invited','quoted','selected','contracted','rejected') NOT NULL DEFAULT 'prospect',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_operation_counterparty(operation_id,counterparty_id,role),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_schedules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 contract_id BIGINT UNSIGNED NULL,
 loan_id BIGINT UNSIGNED NULL,
 sequence_no INT UNSIGNED NOT NULL DEFAULT 1,
 description VARCHAR(190) NOT NULL,
 payer VARCHAR(190) NULL,
 payee VARCHAR(190) NULL,
 due_date DATE NOT NULL,
 amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 payment_type ENUM('deposit','balance','principal','interest','fee','commission','supplier','logistics','tax','other') NOT NULL DEFAULT 'other',
 status ENUM('planned','invoiced','partially_paid','paid','overdue','waived','cancelled') NOT NULL DEFAULT 'planned',
 paid_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 paid_at DATETIME NULL,
 bank_reference VARCHAR(120) NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(contract_id) REFERENCES operation_contracts(id) ON DELETE SET NULL,
 INDEX(operation_id,due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS commission_splits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 broker_id BIGINT UNSIGNED NULL,
 beneficiary_name VARCHAR(190) NOT NULL,
 beneficiary_email VARCHAR(190) NULL,
 commission_basis ENUM('fixed','percentage','per_unit','overprice') NOT NULL DEFAULT 'percentage',
 rate DECIMAL(12,6) NULL,
 base_amount DECIMAL(20,2) NULL,
 calculated_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 payment_status ENUM('pending','approved','scheduled','paid','held','cancelled') NOT NULL DEFAULT 'pending',
 due_date DATE NULL,
 paid_at DATETIME NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(broker_id) REFERENCES brokers(id) ON DELETE SET NULL,
 INDEX(operation_id,payment_status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_risk_alerts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 alert_type VARCHAR(100) NOT NULL,
 severity ENUM('info','warning','high','critical') NOT NULL DEFAULT 'warning',
 title VARCHAR(190) NOT NULL,
 description TEXT NULL,
 source VARCHAR(80) NULL,
 status ENUM('open','acknowledged','resolved','dismissed') NOT NULL DEFAULT 'open',
 assigned_user_id BIGINT UNSIGNED NULL,
 due_at DATETIME NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,severity,due_at), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_payment_instructions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 account_name VARCHAR(190) NOT NULL,
 bank_name VARCHAR(190) NOT NULL,
 bank_address VARCHAR(255) NULL,
 account_number_masked VARCHAR(100) NULL,
 iban VARCHAR(100) NULL,
 swift_bic VARCHAR(30) NULL,
 routing_code VARCHAR(80) NULL,
 intermediary_bank TEXT NULL,
 currency CHAR(3) NULL,
 verification_status ENUM('pending','verified','rejected','expired') NOT NULL DEFAULT 'pending',
 verified_by BIGINT UNSIGNED NULL,
 verified_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,verification_status)
) ENGINE=InnoDB;