CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','director','manager','analyst','compliance','finance','broker','client') NOT NULL DEFAULT 'analyst',locale VARCHAR(5) NOT NULL DEFAULT 'pt',status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS clients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,portal_user_id BIGINT UNSIGNED NULL,client_type VARCHAR(40) NOT NULL,legal_name VARCHAR(190) NOT NULL,trade_name VARCHAR(190) NULL,country VARCHAR(100) NULL,tax_id VARCHAR(100) NULL,email VARCHAR(190) NULL,phone VARCHAR(80) NULL,preferred_locale VARCHAR(5) DEFAULT 'en',kyc_status VARCHAR(30) DEFAULT 'pending',risk_rating VARCHAR(20) NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(email),FOREIGN KEY(portal_user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category VARCHAR(80) NOT NULL,name VARCHAR(190) NOT NULL,grade VARCHAR(120) NULL,specification TEXT NULL,origin_country VARCHAR(100) NULL,unit VARCHAR(30) NULL,status VARCHAR(30) NOT NULL DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS suppliers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,supplier_type ENUM('farmer','mill','producer','trade','slaughterhouse','financial_institution','logistics') NOT NULL,legal_name VARCHAR(190) NOT NULL,country VARCHAR(100),tax_id VARCHAR(100),contact_name VARCHAR(150),email VARCHAR(190),phone VARCHAR(80),certifications TEXT,capacity_notes TEXT,status VARCHAR(30) DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS supplier_products (supplier_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,availability_status VARCHAR(30) DEFAULT 'available',monthly_capacity DECIMAL(18,3) NULL,minimum_order DECIMAL(18,3) NULL,price_reference DECIMAL(18,2) NULL,currency CHAR(3) DEFAULT 'USD',production_start DATE NULL,production_end DATE NULL,PRIMARY KEY(supplier_id,product_id),FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS operations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(60) NOT NULL UNIQUE,operation_type VARCHAR(80) NOT NULL,client_id BIGINT UNSIGNED NULL,product_id BIGINT UNSIGNED NULL,supplier_id BIGINT UNSIGNED NULL,service_type VARCHAR(120) NULL,amount DECIMAL(20,2) DEFAULT 0,currency CHAR(3) DEFAULT 'USD',quantity DECIMAL(20,3) NULL,unit VARCHAR(30) NULL,incoterm VARCHAR(20) NULL,destination VARCHAR(190) NULL,stage VARCHAR(40) NOT NULL DEFAULT 'lead',status VARCHAR(40) NOT NULL DEFAULT 'open',owner_user_id BIGINT UNSIGNED NULL,source VARCHAR(40) DEFAULT 'manual',notes TEXT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL,FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,INDEX(stage),INDEX(status)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_templates (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,document_type VARCHAR(80) NOT NULL,operation_type VARCHAR(80) NULL,locale VARCHAR(5) NOT NULL DEFAULT 'en',content_html LONGTEXT NOT NULL,requires_signature TINYINT(1) DEFAULT 1,workflow_order INT DEFAULT 0,status VARCHAR(30) DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS operation_documents (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_id BIGINT UNSIGNED NOT NULL,template_id BIGINT UNSIGNED NULL,document_type VARCHAR(80) NOT NULL,title VARCHAR(190) NOT NULL,direction ENUM('incoming','outgoing','internal') NOT NULL,content_html LONGTEXT NULL,file_path VARCHAR(255) NULL,status VARCHAR(40) DEFAULT 'draft',requires_signature TINYINT(1) DEFAULT 0,signature_status VARCHAR(40) DEFAULT 'pending',public_token CHAR(64) NOT NULL UNIQUE,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(template_id) REFERENCES document_templates(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS signatures (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_document_id BIGINT UNSIGNED NOT NULL,signer_name VARCHAR(190) NOT NULL,signer_email VARCHAR(190) NOT NULL,signer_phone VARCHAR(80),document_photo_path VARCHAR(255),face_photo_path VARCHAR(255),signature_image_path VARCHAR(255),initials_image_path VARCHAR(255),ip_address VARCHAR(64),geo_lat DECIMAL(10,7),geo_lng DECIMAL(10,7),device_fingerprint VARCHAR(255),user_agent VARCHAR(500),consent_text TEXT,signed_at DATETIME NULL,status VARCHAR(30) DEFAULT 'pending',verification_hash CHAR(64) NULL,FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS bank_instruments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_id BIGINT UNSIGNED NULL,client_id BIGINT UNSIGNED NULL,instrument_type VARCHAR(40) NOT NULL,reference_number VARCHAR(120),issuing_bank VARCHAR(190),issuing_bank_swift VARCHAR(30),advising_bank VARCHAR(190),face_value DECIMAL(20,2),currency CHAR(3) DEFAULT 'USD',issue_date DATE,expiry_date DATE,governing_rules VARCHAR(40),status VARCHAR(40) DEFAULT 'registered',purpose TEXT,document_path VARCHAR(255),created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS instrument_loans (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,bank_instrument_id BIGINT UNSIGNED NOT NULL,lender_name VARCHAR(190),principal DECIMAL(20,2),currency CHAR(3),interest_rate DECIMAL(9,6),start_date DATE,maturity_date DATE,status VARCHAR(40),purpose VARCHAR(190),fees_total DECIMAL(20,2) DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS loan_installments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,instrument_loan_id BIGINT UNSIGNED NOT NULL,due_date DATE NOT NULL,principal_due DECIMAL(20,2) DEFAULT 0,interest_due DECIMAL(20,2) DEFAULT 0,fees_due DECIMAL(20,2) DEFAULT 0,paid_amount DECIMAL(20,2) DEFAULT 0,paid_at DATETIME NULL,status VARCHAR(30) DEFAULT 'pending',FOREIGN KEY(instrument_loan_id) REFERENCES instrument_loans(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cash_movements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_id BIGINT UNSIGNED NULL,bank_instrument_id BIGINT UNSIGNED NULL,instrument_loan_id BIGINT UNSIGNED NULL,movement_type ENUM('inflow','outflow','allocation','trade','reinvestment') NOT NULL,category VARCHAR(100),description VARCHAR(255),amount DECIMAL(20,2) NOT NULL,currency CHAR(3) NOT NULL,movement_date DATE NOT NULL,beneficiary VARCHAR(190),status VARCHAR(30) DEFAULT 'planned',created_at DATETIME NOT NULL,FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,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) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS brokers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,portal_user_id BIGINT UNSIGNED NULL,name VARCHAR(190) NOT NULL,company VARCHAR(190),email VARCHAR(190),phone VARCHAR(80),country VARCHAR(100),default_commission_pct DECIMAL(9,4) DEFAULT 0,status VARCHAR(30) DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(portal_user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS broker_commissions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_id BIGINT UNSIGNED NOT NULL,broker_id BIGINT UNSIGNED NOT NULL,commission_type VARCHAR(40) DEFAULT 'percentage',base_commission DECIMAL(20,2) DEFAULT 0,overprice_commission DECIMAL(20,2) DEFAULT 0,currency CHAR(3) DEFAULT 'USD',due_date DATE NULL,paid_at DATETIME NULL,status VARCHAR(30) DEFAULT 'pending',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 CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS deliveries (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,operation_id BIGINT UNSIGNED NOT NULL,transport_mode VARCHAR(40),carrier VARCHAR(190),vessel_or_vehicle VARCHAR(190),origin VARCHAR(190),destination VARCHAR(190),etd DATETIME,eta DATETIME,tracking_reference VARCHAR(120),status VARCHAR(40) DEFAULT 'planned',documents_json JSON NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(100),entity_id BIGINT UNSIGNED NULL,ip_address VARCHAR(64),user_agent VARCHAR(255),metadata_json JSON NULL,created_at DATETIME NOT NULL,INDEX(entity_type,entity_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
INSERT INTO products(category,name,grade,origin_country,unit,status,created_at,updated_at) VALUES ('sugar','White Refined Sugar','ICUMSA 45','Brazil','MT','active',NOW(),NOW()),('sugar','Brown Sugar','ICUMSA 600','Brazil','MT','active',NOW(),NOW()),('soy','Soybeans','GMO / Non-GMO','Brazil','MT','active',NOW(),NOW()),('corn','Yellow Corn','Grade 2','Brazil','MT','active',NOW(),NOW()),('gold','Gold Bullion','99.99%','International','KG','active',NOW(),NOW()),('live_cattle','Live Cattle','Export Standard','Brazil','HEAD','active',NOW(),NOW()),('frozen_chicken','Frozen Chicken','Halal / Standard','Brazil','MT','active',NOW(),NOW()),('frozen_beef','Frozen Beef','Halal / Standard','Brazil','MT','active',NOW(),NOW()),('frozen_sheep','Frozen Sheep Meat','Halal','International','MT','active',NOW(),NOW()),('frozen_goat','Frozen Goat Meat','Halal','International','MT','active',NOW(),NOW());

-- V002 additions
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS subject VARCHAR(190) NULL AFTER name;
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS response_to_document_type VARCHAR(80) NULL AFTER operation_type;
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS auto_create_on_stage VARCHAR(40) NULL AFTER workflow_order;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS parent_document_id BIGINT UNSIGNED NULL AFTER template_id;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS sent_at DATETIME NULL AFTER signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS completed_at DATETIME NULL AFTER sent_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS signer_access_expires_at DATETIME NULL AFTER public_token;
ALTER TABLE operation_documents ADD INDEX idx_public_token (public_token);
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS signer_document_number VARCHAR(120) NULL AFTER signer_phone;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS signer_country VARCHAR(100) NULL AFTER signer_document_number;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS otp_verified_at DATETIME NULL AFTER consent_text;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS evidence_json JSON NULL AFTER verification_hash;
CREATE TABLE IF NOT EXISTS operation_stage_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 from_stage VARCHAR(40) NULL,
 to_stage VARCHAR(40) NOT NULL,
 changed_by BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,created_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_workflows (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 operation_type VARCHAR(80) NOT NULL,
 locale VARCHAR(5) NOT NULL DEFAULT 'en',
 status VARCHAR(30) NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_workflow_steps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workflow_id BIGINT UNSIGNED NOT NULL,
 template_id BIGINT UNSIGNED NOT NULL,
 step_order INT NOT NULL DEFAULT 0,
 stage VARCHAR(40) NOT NULL DEFAULT 'documentation',
 trigger_type VARCHAR(40) NOT NULL DEFAULT 'manual',
 requires_previous_signed TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(workflow_id) REFERENCES document_workflows(id) ON DELETE CASCADE,
 FOREIGN KEY(template_id) REFERENCES document_templates(id) ON DELETE CASCADE,
 UNIQUE KEY uq_workflow_step(workflow_id,template_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS notification_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 channel ENUM('email','whatsapp','internal') NOT NULL DEFAULT 'email',
 recipient VARCHAR(190) NOT NULL,
 subject VARCHAR(190) NULL,
 body LONGTEXT NOT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 attempts INT NOT NULL DEFAULT 0,
 scheduled_at DATETIME NOT NULL,
 sent_at DATETIME NULL,
 last_error TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX(status,scheduled_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS operation_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 description TEXT NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 due_date DATE NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
 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(operation_id,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS instrument_expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_instrument_id BIGINT UNSIGNED NOT NULL,
 instrument_loan_id BIGINT UNSIGNED NULL,
 expense_type VARCHAR(100) NOT NULL,
 description VARCHAR(255) NULL,
 amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 due_date DATE NULL,
 paid_at DATETIME NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL,
 FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE CASCADE,
 FOREIGN KEY(instrument_loan_id) REFERENCES instrument_loans(id) ON DELETE SET NULL
) ENGINE=InnoDB;



-- V003 additions
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;
-- V004 - contracts, approvals, instrument lifecycle and logistics milestones
ALTER TABLE operations ADD COLUMN IF NOT EXISTS contract_status VARCHAR(30) NOT NULL DEFAULT 'not_started' AFTER compliance_status;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS logistics_status VARCHAR(30) NOT NULL DEFAULT 'not_started' AFTER contract_status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS custody_status VARCHAR(40) NOT NULL DEFAULT 'pending' AFTER status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS current_holder VARCHAR(190) NULL AFTER custody_status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS available_value DECIMAL(20,2) NULL AFTER face_value;

CREATE TABLE IF NOT EXISTS operation_contracts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 operation_document_id BIGINT UNSIGNED NULL,
 contract_type VARCHAR(80) NOT NULL,
 contract_number VARCHAR(100) NULL,
 title VARCHAR(190) NOT NULL,
 effective_date DATE NULL,
 expiry_date DATE NULL,
 governing_law VARCHAR(120) NULL,
 jurisdiction VARCHAR(120) NULL,
 total_value DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','internal_review','client_review','signature_pending','active','suspended','completed','terminated') NOT NULL DEFAULT 'draft',
 signed_at DATETIME NULL,
 notes TEXT 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(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contract_obligations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 contract_id BIGINT UNSIGNED NOT NULL,
 responsible_party ENUM('company','client','supplier','bank','broker','carrier','other') NOT NULL,
 obligation_type VARCHAR(100) NOT NULL,
 description VARCHAR(255) NOT NULL,
 due_date DATE NULL,
 amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 status ENUM('pending','in_progress','fulfilled','overdue','waived','cancelled') NOT NULL DEFAULT 'pending',
 evidence_path VARCHAR(255) NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(contract_id) REFERENCES operation_contracts(id) ON DELETE CASCADE,
 INDEX(contract_id,status,due_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS approval_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 approval_type VARCHAR(100) NOT NULL,
 requested_by BIGINT UNSIGNED NULL,
 assigned_role VARCHAR(40) NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
 request_notes TEXT NULL,
 decision_notes TEXT NULL,
 requested_at DATETIME NOT NULL,
 decided_at DATETIME NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,assigned_role), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_instrument_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_instrument_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 event_type VARCHAR(100) NOT NULL,
 event_date DATETIME NOT NULL,
 from_party VARCHAR(190) NULL,
 to_party VARCHAR(190) NULL,
 bank_name VARCHAR(190) NULL,
 swift_reference VARCHAR(120) NULL,
 amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'recorded',
 notes TEXT NULL,
 attachment_path VARCHAR(255) NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(bank_instrument_id,event_date), INDEX(swift_reference)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_number VARCHAR(100) NOT NULL,
 transport_mode ENUM('sea','air','road','rail','multimodal') NOT NULL DEFAULT 'sea',
 carrier VARCHAR(190) NULL,
 vessel_or_vehicle VARCHAR(190) NULL,
 origin VARCHAR(190) NULL,
 destination VARCHAR(190) NULL,
 load_port VARCHAR(190) NULL,
 discharge_port VARCHAR(190) NULL,
 quantity DECIMAL(20,3) NULL,
 unit VARCHAR(30) NULL,
 etd DATETIME NULL,
 eta DATETIME NULL,
 actual_departure DATETIME NULL,
 actual_arrival DATETIME NULL,
 tracking_reference VARCHAR(120) NULL,
 status ENUM('planned','booking','loading','in_transit','customs','delivered','cancelled') NOT NULL DEFAULT 'planned',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_shipment_number(shipment_number),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 INDEX(operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_milestones (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 shipment_id BIGINT UNSIGNED NOT NULL,
 milestone_type VARCHAR(100) NOT NULL,
 location VARCHAR(190) NULL,
 planned_at DATETIME NULL,
 completed_at DATETIME NULL,
 status ENUM('pending','completed','delayed','cancelled') NOT NULL DEFAULT 'pending',
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE CASCADE,
 INDEX(shipment_id,status,planned_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NULL,
 recipient_role VARCHAR(40) NULL,
 message TEXT NOT NULL,
 visibility ENUM('internal','client','broker','all') NOT NULL DEFAULT 'internal',
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,created_at)
) ENGINE=InnoDB;
-- 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;

-- Structures introduced in V006
-- V006 - sourcing, quotations, purchase orders, inventory lots and quality inspections
CREATE TABLE IF NOT EXISTS counterparty_products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 origin_country_code CHAR(2) NULL,
 specification TEXT NULL,
 monthly_capacity DECIMAL(20,3) NULL,
 minimum_order_quantity DECIMAL(20,3) NULL,
 unit VARCHAR(30) NULL,
 indicative_price DECIMAL(20,4) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 availability_status ENUM('available','to_produce','allocated','unavailable') NOT NULL DEFAULT 'available',
 lead_time_days INT UNSIGNED NULL,
 valid_until DATE NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_counterparty_product(counterparty_id,product_id,origin_country_code),
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
 INDEX(product_id,availability_status), INDEX(counterparty_id,active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sourcing_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 request_code VARCHAR(60) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 target_price DECIMAL(20,4) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 required_delivery_date DATE NULL,
 response_deadline DATETIME NULL,
 specifications TEXT NULL,
 status ENUM('draft','open','under_review','awarded','closed','cancelled') NOT NULL DEFAULT 'draft',
 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(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(response_deadline,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sourcing_request_suppliers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sourcing_request_id BIGINT UNSIGNED NOT NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 invitation_status ENUM('pending','sent','viewed','responded','declined') NOT NULL DEFAULT 'pending',
 invited_at DATETIME NULL,
 responded_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_rfq_supplier(sourcing_request_id,counterparty_id),
 FOREIGN KEY(sourcing_request_id) REFERENCES sourcing_requests(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS supplier_quotations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sourcing_request_id BIGINT UNSIGNED NOT NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 quotation_number VARCHAR(80) NULL,
 quoted_quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 origin_country_code CHAR(2) NULL,
 loading_location VARCHAR(190) NULL,
 availability_date DATE NULL,
 payment_terms TEXT NULL,
 validity_date DATE NULL,
 quality_specification TEXT NULL,
 internal_score DECIMAL(5,2) NULL,
 status ENUM('received','under_review','shortlisted','selected','rejected','expired','withdrawn') NOT NULL DEFAULT 'received',
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(sourcing_request_id) REFERENCES sourcing_requests(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(sourcing_request_id,status), INDEX(counterparty_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 quotation_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 po_number VARCHAR(80) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 expected_delivery_date DATE NULL,
 payment_terms TEXT NULL,
 status ENUM('draft','pending_approval','approved','sent','confirmed','partially_fulfilled','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME 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(quotation_id) REFERENCES supplier_quotations(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(counterparty_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_lots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 purchase_order_id BIGINT UNSIGNED NULL,
 lot_code VARCHAR(100) NOT NULL UNIQUE,
 origin_country_code CHAR(2) NULL,
 production_date DATE NULL,
 expiry_date DATE NULL,
 warehouse_location VARCHAR(190) NULL,
 quantity_total DECIMAL(20,3) NOT NULL,
 quantity_available DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 quality_grade VARCHAR(100) NULL,
 certificate_reference VARCHAR(190) NULL,
 status ENUM('expected','available','reserved','partially_allocated','allocated','in_transit','delivered','blocked','expired') NOT NULL DEFAULT 'expected',
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
 INDEX(product_id,status), INDEX(counterparty_id,status), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS quality_inspections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 inventory_lot_id BIGINT UNSIGNED NULL,
 shipment_id BIGINT UNSIGNED NULL,
 inspection_company_id BIGINT UNSIGNED NULL,
 inspection_type ENUM('pre_shipment','loading','quantity','quality','health','phytosanitary','customs','other') NOT NULL DEFAULT 'quality',
 scheduled_date DATE NULL,
 completed_date DATE NULL,
 location VARCHAR(190) NULL,
 result ENUM('pending','passed','passed_with_reservations','failed','cancelled') NOT NULL DEFAULT 'pending',
 certificate_number VARCHAR(120) NULL,
 findings TEXT NULL,
 corrective_actions TEXT NULL,
 document_id 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(inventory_lot_id) REFERENCES inventory_lots(id) ON DELETE SET NULL,
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(inspection_company_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 INDEX(operation_id,result), INDEX(scheduled_date,result)
) ENGINE=InnoDB;

-- V007 - sales execution, allocations, invoices and trade documentation
CREATE TABLE IF NOT EXISTS sales_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 order_number VARCHAR(80) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 expected_delivery_date DATE NULL,
 payment_terms TEXT NULL,
 status ENUM('draft','pending_approval','approved','contracted','partially_allocated','allocated','in_fulfillment','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 approved_by BIGINT UNSIGNED NULL,
 approved_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(client_id) REFERENCES clients(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(client_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sales_order_id BIGINT UNSIGNED NOT NULL,
 inventory_lot_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 status ENUM('reserved','confirmed','released','shipped','delivered','cancelled') NOT NULL DEFAULT 'reserved',
 reserved_until DATETIME NULL,
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(inventory_lot_id) REFERENCES inventory_lots(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(sales_order_id,status), INDEX(inventory_lot_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS commercial_invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 sales_order_id BIGINT UNSIGNED NULL,
 invoice_number VARCHAR(80) NOT NULL UNIQUE,
 invoice_type ENUM('proforma','commercial','credit_note','debit_note') NOT NULL DEFAULT 'proforma',
 issue_date DATE NOT NULL,
 due_date DATE NULL,
 subtotal DECIMAL(20,2) NOT NULL,
 taxes DECIMAL(20,2) NOT NULL DEFAULT 0,
 fees DECIMAL(20,2) NOT NULL DEFAULT 0,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','issued','sent','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
 payment_reference VARCHAR(190) NULL,
 notes TEXT 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(sales_order_id) REFERENCES sales_orders(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trade_document_checklists (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 document_code VARCHAR(80) NOT NULL,
 document_name VARCHAR(190) NOT NULL,
 responsible_party ENUM('company','client','supplier','bank','broker','carrier','inspector','other') NOT NULL DEFAULT 'company',
 required_stage VARCHAR(80) NULL,
 due_date DATE NULL,
 status ENUM('not_started','requested','received','under_review','approved','rejected','waived') NOT NULL DEFAULT 'not_started',
 operation_document_id BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_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(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_operation_document_code(operation_id,document_code),
 INDEX(operation_id,status), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 shipment_id BIGINT UNSIGNED NOT NULL,
 inventory_allocation_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_shipment_allocation(shipment_id,inventory_allocation_id),
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE CASCADE,
 FOREIGN KEY(inventory_allocation_id) REFERENCES inventory_allocations(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
-- V008 - customs, cargo insurance, shipment costs and claims
CREATE TABLE IF NOT EXISTS customs_declarations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 declaration_number VARCHAR(120) NOT NULL,
 declaration_type ENUM('export','import','transit','temporary','other') NOT NULL DEFAULT 'export',
 customs_authority VARCHAR(190) NULL,
 country_code CHAR(2) NULL,
 customs_broker_id BIGINT UNSIGNED NULL,
 filing_date DATE NULL,
 clearance_date DATE NULL,
 declared_value DECIMAL(20,2) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 duties_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 taxes_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 status ENUM('draft','filed','under_review','inspection','cleared','held','rejected','cancelled') NOT NULL DEFAULT 'draft',
 document_id BIGINT UNSIGNED NULL,
 notes TEXT 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(customs_broker_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_customs_declaration_number(declaration_number),
 INDEX(operation_id,status), INDEX(shipment_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cargo_insurance_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 insurer_id BIGINT UNSIGNED NULL,
 policy_number VARCHAR(120) NOT NULL,
 coverage_type ENUM('all_risks','named_perils','war_risk','political_risk','credit_risk','other') NOT NULL DEFAULT 'all_risks',
 insured_party VARCHAR(190) NOT NULL,
 beneficiary VARCHAR(190) NULL,
 insured_value DECIMAL(20,2) NOT NULL,
 premium_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 deductible_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 effective_date DATE NOT NULL,
 expiry_date DATE NULL,
 status ENUM('quoted','pending','active','expired','cancelled','claimed') NOT NULL DEFAULT 'pending',
 document_id BIGINT UNSIGNED NULL,
 terms_summary TEXT 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(insurer_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_policy_number(policy_number),
 INDEX(operation_id,status), INDEX(shipment_id,status), INDEX(expiry_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_costs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 cost_type ENUM('freight','port','storage','demurrage','detention','inspection','customs','insurance','handling','tax','other') NOT NULL,
 description VARCHAR(255) NOT NULL,
 planned_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 actual_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 due_date DATE NULL,
 paid_date DATE NULL,
 status ENUM('estimated','approved','invoiced','partially_paid','paid','disputed','cancelled') NOT NULL DEFAULT 'estimated',
 invoice_reference VARCHAR(120) NULL,
 notes TEXT 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(shipment_id,cost_type), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trade_claims (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 insurance_policy_id BIGINT UNSIGNED NULL,
 claim_number VARCHAR(120) NOT NULL,
 claim_type ENUM('shortage','quality','damage','delay','non_delivery','documentary','payment','insurance','other') NOT NULL,
 claimant_party VARCHAR(190) NOT NULL,
 respondent_party VARCHAR(190) NULL,
 incident_date DATE NULL,
 notification_date DATE NOT NULL,
 claimed_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 approved_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 recovered_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','notified','under_review','evidence_requested','negotiation','approved','partially_settled','settled','rejected','closed') NOT NULL DEFAULT 'draft',
 description TEXT NOT NULL,
 resolution TEXT NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 resolved_at DATETIME 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(insurance_policy_id) REFERENCES cargo_insurance_policies(id) ON DELETE SET NULL,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_claim_number(claim_number),
 INDEX(operation_id,status), INDEX(shipment_id,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS business_units (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(20) NOT NULL UNIQUE,
 name VARCHAR(150) NOT NULL,
 country VARCHAR(100) NOT NULL,
 city VARCHAR(100) NULL,
 unit_type ENUM('headquarters','branch','representative_office') NOT NULL DEFAULT 'branch',
 timezone VARCHAR(80) NOT NULL DEFAULT 'UTC',
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO business_units(code,name,country,city,unit_type,timezone,currency,status,created_at) VALUES
('CH-ZRH','Switzerland Headquarters','Switzerland','Zurich','headquarters','Europe/Zurich','CHF','active',NOW()),
('BR','Brazil Unit','Brazil',NULL,'branch','America/Sao_Paulo','BRL','active',NOW()),
('AR','Argentina Unit','Argentina',NULL,'branch','America/Argentina/Buenos_Aires','ARS','active',NOW()),
('CO','Colombia Unit','Colombia',NULL,'branch','America/Bogota','COP','active',NOW()),
('QA','Qatar Unit','Qatar','Doha','branch','Asia/Qatar','QAR','active',NOW()),
('AE-DXB','Dubai Unit','United Arab Emirates','Dubai','branch','Asia/Dubai','AED','active',NOW()),
('SY','Syria Unit','Syria',NULL,'branch','Asia/Damascus','USD','active',NOW()),
('SG','Singapore Unit','Singapore','Singapore','branch','Asia/Singapore','SGD','active',NOW());

CREATE TABLE IF NOT EXISTS user_units (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 access_role ENUM('director','manager','sales','administrative','finance','compliance','viewer') NOT NULL DEFAULT 'viewer',
 is_primary TINYINT(1) NOT NULL DEFAULT 0,
 can_view_all TINYINT(1) NOT NULL DEFAULT 0,
 can_edit TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_user_unit(user_id,unit_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 INDEX(unit_id,access_role)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_preferences (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 theme ENUM('light','dark','system') NOT NULL DEFAULT 'light',
 accent_color VARCHAR(20) NOT NULL DEFAULT '#1261a0',
 sidebar_collapsed TINYINT(1) NOT NULL DEFAULT 0,
 dashboard_layout_json JSON NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_two_factor (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 secret VARCHAR(64) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 recovery_codes_json JSON NULL,
 confirmed_at DATETIME NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE operations ADD COLUMN IF NOT EXISTS unit_id BIGINT UNSIGNED NULL AFTER code;
ALTER TABLE operations ADD INDEX IF NOT EXISTS idx_operations_unit(unit_id);

CREATE TABLE IF NOT EXISTS unit_access_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 action VARCHAR(80) NOT NULL,
 ip_address VARCHAR(64) NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 INDEX(user_id,created_at)
) ENGINE=InnoDB;
INSERT IGNORE INTO user_units(user_id,unit_id,access_role,is_primary,can_view_all,can_edit,created_at)
SELECT u.id,bu.id,CASE WHEN u.role='admin' THEN 'director' WHEN u.role='director' THEN 'director' ELSE 'viewer' END,1,1,1,NOW()
FROM users u JOIN business_units bu ON bu.code='CH-ZRH'
WHERE u.role IN ('admin','director');
CREATE TABLE IF NOT EXISTS departments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 parent_id BIGINT UNSIGNED NULL,
 code VARCHAR(40) NOT NULL,
 name VARCHAR(150) NOT NULL,
 description TEXT NULL,
 manager_user_id BIGINT UNSIGNED NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_department_unit_code(unit_id,code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(parent_id) REFERENCES departments(id) ON DELETE SET NULL,
 FOREIGN KEY(manager_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS teams (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 department_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 team_type ENUM('commercial','administrative','finance','compliance','operations','logistics','executive','other') NOT NULL DEFAULT 'other',
 leader_user_id BIGINT UNSIGNED NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL,
 FOREIGN KEY(leader_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,department_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS team_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 team_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 member_role ENUM('leader','member','assistant','observer') NOT NULL DEFAULT 'member',
 joined_at DATETIME NOT NULL,
 UNIQUE KEY uq_team_user(team_id,user_id),
 FOREIGN KEY(team_id) REFERENCES teams(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permission_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 description TEXT NULL,
 is_system TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_permission_profile_name(name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permission_profile_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 profile_id BIGINT UNSIGNED NOT NULL,
 module_key VARCHAR(80) NOT NULL,
 can_view TINYINT(1) NOT NULL DEFAULT 0,
 can_create TINYINT(1) NOT NULL DEFAULT 0,
 can_edit TINYINT(1) NOT NULL DEFAULT 0,
 can_delete TINYINT(1) NOT NULL DEFAULT 0,
 can_approve TINYINT(1) NOT NULL DEFAULT 0,
 can_export TINYINT(1) NOT NULL DEFAULT 0,
 UNIQUE KEY uq_profile_module(profile_id,module_key),
 FOREIGN KEY(profile_id) REFERENCES permission_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_unit_governance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 permission_profile_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NULL,
 data_scope ENUM('own','team','department','unit','all_units') NOT NULL DEFAULT 'own',
 approval_limit DECIMAL(20,2) NULL,
 approval_currency CHAR(3) NOT NULL DEFAULT 'USD',
 may_delegate TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_user_unit_governance(user_id,unit_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(permission_profile_id) REFERENCES permission_profiles(id) ON DELETE SET NULL,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dashboard_widgets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 widget_key VARCHAR(80) NOT NULL,
 name VARCHAR(120) NOT NULL,
 description VARCHAR(255) NULL,
 minimum_role VARCHAR(40) NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 UNIQUE KEY uq_widget_key(widget_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_dashboard_widgets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 widget_id BIGINT UNSIGNED NOT NULL,
 position_order INT NOT NULL DEFAULT 0,
 column_span TINYINT UNSIGNED NOT NULL DEFAULT 1,
 is_visible TINYINT(1) NOT NULL DEFAULT 1,
 settings_json JSON NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_user_widget(user_id,widget_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(widget_id) REFERENCES dashboard_widgets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO permission_profiles(id,name,description,is_system,status,created_at) VALUES
(1,'Diretoria','Acesso executivo amplo, aprovações e relatórios.',1,'active',NOW()),
(2,'Gerência','Gestão da unidade, equipe e operações.',1,'active',NOW()),
(3,'Força de vendas','Clientes, pipeline, propostas e pedidos de venda.',1,'active',NOW()),
(4,'Administrativo','Cadastros, documentos e suporte operacional.',1,'active',NOW()),
(5,'Financeiro','Instrumentos, pagamentos, faturas e fluxo financeiro.',1,'active',NOW()),
(6,'Compliance','KYC, due diligence, documentos e alertas.',1,'active',NOW()),
(7,'Consulta','Acesso somente para visualização.',1,'active',NOW());

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('executive_summary','Resumo executivo','Indicadores consolidados da unidade ativa.',NULL,'active'),
('sales_pipeline','Pipeline comercial','Quantidade e valor por etapa comercial.',NULL,'active'),
('pending_approvals','Aprovações pendentes','Solicitações aguardando decisão.',NULL,'active'),
('financial_position','Posição financeira','Entradas, saídas e saldo registrado.',NULL,'active'),
('inventory_position','Posição de estoque','Lotes, disponibilidade e reservas.',NULL,'active'),
('risk_compliance','Riscos e compliance','Alertas abertos e casos de compliance.',NULL,'active'),
('logistics_status','Situação logística','Embarques por status.',NULL,'active'),
('my_tasks','Minha equipe e tarefas','Resumo de equipes e responsabilidades.',NULL,'active');
CREATE TABLE IF NOT EXISTS work_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 description TEXT NULL,
 task_type ENUM('general','call','email','document','approval','compliance','finance','logistics','follow_up') NOT NULL DEFAULT 'general',
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('backlog','pending','in_progress','waiting','completed','cancelled') NOT NULL DEFAULT 'pending',
 assigned_user_id BIGINT UNSIGNED NULL,
 assigned_team_id BIGINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NULL,
 start_at DATETIME NULL,
 due_at DATETIME NULL,
 completed_at DATETIME NULL,
 estimated_minutes INT UNSIGNED NULL,
 progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
 visibility ENUM('private','team','unit') NOT NULL DEFAULT 'unit',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_team_id) REFERENCES teams(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,due_at), INDEX(assigned_user_id,status), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS task_comments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 task_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 comment_text TEXT NOT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(task_id) REFERENCES work_tasks(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(task_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS business_activities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 activity_type ENUM('note','call','email','meeting','visit','message','status_change','system') NOT NULL DEFAULT 'note',
 subject VARCHAR(190) NOT NULL,
 details TEXT NULL,
 activity_at DATETIME NOT NULL,
 duration_minutes INT UNSIGNED NULL,
 outcome VARCHAR(190) NULL,
 next_action VARCHAR(190) NULL,
 next_action_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,activity_at), INDEX(operation_id,activity_at), INDEX(client_id,activity_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sla_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 entity_type ENUM('operation','document','approval','compliance','payment','shipment','task') NOT NULL,
 event_key VARCHAR(80) NOT NULL,
 priority ENUM('low','normal','high','critical','all') NOT NULL DEFAULT 'all',
 response_minutes INT UNSIGNED NOT NULL,
 resolution_minutes INT UNSIGNED NOT NULL,
 business_hours_only TINYINT(1) NOT NULL DEFAULT 1,
 escalation_role VARCHAR(50) NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 INDEX(unit_id,entity_type,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS responsibility_delegations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 delegator_user_id BIGINT UNSIGNED NOT NULL,
 delegate_user_id BIGINT UNSIGNED NOT NULL,
 delegation_type ENUM('tasks','approvals','operations','documents','all') NOT NULL DEFAULT 'tasks',
 starts_at DATETIME NOT NULL,
 ends_at DATETIME NOT NULL,
 reason VARCHAR(255) NULL,
 status ENUM('scheduled','active','revoked','expired') NOT NULL DEFAULT 'scheduled',
 created_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(delegator_user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(delegate_user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(unit_id,status,starts_at,ends_at), INDEX(delegate_user_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS task_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 task_id BIGINT UNSIGNED NOT NULL,
 changed_by BIGINT UNSIGNED NULL,
 old_status VARCHAR(40) NULL,
 new_status VARCHAR(40) NOT NULL,
 notes VARCHAR(255) NULL,
 changed_at DATETIME NOT NULL,
 FOREIGN KEY(task_id) REFERENCES work_tasks(id) ON DELETE CASCADE,
 FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(task_id,changed_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('task_productivity','Produtividade e prazos','Tarefas pessoais, atrasos e atividades próximas.',NULL,'active');
CREATE TABLE IF NOT EXISTS communication_threads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 subject VARCHAR(190) NOT NULL,
 channel ENUM('internal','email','whatsapp','phone','other') NOT NULL DEFAULT 'internal',
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('open','waiting','resolved','archived') NOT NULL DEFAULT 'open',
 created_by BIGINT UNSIGNED NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,priority), INDEX(operation_id), INDEX(client_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS communication_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 thread_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NULL,
 direction ENUM('internal','incoming','outgoing') NOT NULL DEFAULT 'internal',
 body TEXT NOT NULL,
 recipient VARCHAR(190) NULL,
 sent_at DATETIME NOT NULL,
 read_at DATETIME NULL,
 FOREIGN KEY(thread_id) REFERENCES communication_threads(id) ON DELETE CASCADE,
 FOREIGN KEY(sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(thread_id,sent_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS business_meetings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 meeting_type ENUM('internal','client','supplier','bank','broker','compliance','other') NOT NULL DEFAULT 'internal',
 starts_at DATETIME NOT NULL,
 ends_at DATETIME NULL,
 location VARCHAR(255) NULL,
 meeting_url VARCHAR(500) NULL,
 agenda TEXT NULL,
 minutes_text LONGTEXT NULL,
 decisions_text LONGTEXT NULL,
 status ENUM('scheduled','held','cancelled') NOT NULL DEFAULT 'scheduled',
 organizer_user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(organizer_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,starts_at,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meeting_participants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 meeting_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 external_name VARCHAR(150) NULL,
 external_email VARCHAR(190) NULL,
 attendance_status ENUM('invited','accepted','declined','attended','absent') NOT NULL DEFAULT 'invited',
 FOREIGN KEY(meeting_id) REFERENCES business_meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(meeting_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS service_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 request_code VARCHAR(40) NOT NULL,
 category ENUM('commercial','document','finance','banking','logistics','compliance','technical','other') NOT NULL DEFAULT 'other',
 subject VARCHAR(190) NOT NULL,
 description TEXT NOT NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('new','triage','in_progress','waiting_client','resolved','closed','cancelled') NOT NULL DEFAULT 'new',
 requester_name VARCHAR(150) NULL,
 requester_email VARCHAR(190) NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 due_at DATETIME NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_service_request_code(request_code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,priority), INDEX(client_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS knowledge_articles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 category VARCHAR(100) NOT NULL,
 title VARCHAR(190) NOT NULL,
 slug VARCHAR(190) NOT NULL,
 summary TEXT NULL,
 content LONGTEXT NOT NULL,
 language_code VARCHAR(10) NOT NULL DEFAULT 'pt',
 visibility ENUM('internal','unit','all_units','client') NOT NULL DEFAULT 'internal',
 status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
 author_user_id BIGINT UNSIGNED NULL,
 published_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_knowledge_slug_lang(slug,language_code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(author_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,category,language_code)
) ENGINE=InnoDB;

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('communications_overview','Comunicações e atendimento','Conversas abertas, solicitações e próximas reuniões.',NULL,'active');


-- V013 - workflow, governance, authorized signers, secure verification and notifications
ALTER TABLE users ADD COLUMN IF NOT EXISTS may_sign_documents TINYINT(1) NOT NULL DEFAULT 0 AFTER status;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_title VARCHAR(150) NULL AFTER may_sign_documents;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_signature_path VARCHAR(255) NULL AFTER signer_title;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_initials_path VARCHAR(255) NULL AFTER signer_signature_path;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_authorized_at DATETIME NULL AFTER signer_initials_path;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_authorized_by BIGINT UNSIGNED NULL AFTER signer_authorized_at;

ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signature_status ENUM('not_required','pending','requested','signed','rejected') NOT NULL DEFAULT 'pending' AFTER signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signer_user_id BIGINT UNSIGNED NULL AFTER internal_signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signature_requested_at DATETIME NULL AFTER internal_signer_user_id;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internally_signed_at DATETIME NULL AFTER internal_signature_requested_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS content_hash CHAR(64) NULL AFTER internally_signed_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS verification_key CHAR(64) NULL AFTER content_hash;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS verification_code VARCHAR(40) NULL AFTER verification_key;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS issued_at DATETIME NULL AFTER verification_code;
ALTER TABLE operation_documents ADD UNIQUE KEY uq_document_verification_key(verification_key);
ALTER TABLE operation_documents ADD UNIQUE KEY uq_document_verification_code(verification_code);

CREATE TABLE IF NOT EXISTS internal_document_signatures (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_document_id BIGINT UNSIGNED NOT NULL,
 signer_user_id BIGINT UNSIGNED NOT NULL,
 signature_hash CHAR(64) NOT NULL,
 content_hash CHAR(64) NOT NULL,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(500) NULL,
 signed_at DATETIME NOT NULL,
 notes VARCHAR(500) NULL,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE CASCADE,
 FOREIGN KEY(signer_user_id) REFERENCES users(id) ON DELETE RESTRICT,
 UNIQUE KEY uq_internal_document_signature(operation_document_id),
 INDEX(signer_user_id,signed_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 notification_type VARCHAR(60) NOT NULL DEFAULT 'general',
 title VARCHAR(190) NOT NULL,
 message TEXT NOT NULL,
 action_url VARCHAR(500) NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,read_at,created_at), INDEX(entity_type,entity_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS document_validation_otps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_document_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(190) NOT NULL,
 otp_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 verified_at DATETIME NULL,
 request_ip VARCHAR(64) NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE CASCADE,
 INDEX(operation_document_id,email,expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS workflow_definitions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL, operation_type VARCHAR(80) NOT NULL,
 description TEXT NULL, status ENUM('draft','active','inactive') NOT NULL DEFAULT 'draft',
 version INT UNSIGNED NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_type,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS workflow_steps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, workflow_id BIGINT UNSIGNED NOT NULL,
 step_key VARCHAR(80) NOT NULL, name VARCHAR(190) NOT NULL, step_type ENUM('task','approval','document','signature','notification','condition','wait','system') NOT NULL DEFAULT 'task',
 sequence_no INT NOT NULL DEFAULT 0, responsible_role VARCHAR(80) NULL, settings_json JSON NULL, is_required TINYINT(1) NOT NULL DEFAULT 1,
 FOREIGN KEY(workflow_id) REFERENCES workflow_definitions(id) ON DELETE CASCADE,
 UNIQUE KEY uq_workflow_step_key(workflow_id,step_key), INDEX(workflow_id,sequence_no)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS automation_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, name VARCHAR(190) NOT NULL,
 event_key VARCHAR(100) NOT NULL, conditions_json JSON NULL, actions_json JSON NOT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(event_key,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS legal_clauses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category VARCHAR(100) NOT NULL, clause_code VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL, language_code VARCHAR(10) NOT NULL DEFAULT 'en', content LONGTEXT NOT NULL,
 version INT UNSIGNED NOT NULL DEFAULT 1, status ENUM('draft','approved','archived') NOT NULL DEFAULT 'draft', created_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NULL, UNIQUE KEY uq_clause_code_lang_version(clause_code,language_code,version)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS board_resolutions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, resolution_no VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL, resolution_text LONGTEXT NOT NULL, meeting_date DATE NOT NULL,
 status ENUM('draft','approved','rejected','archived') NOT NULL DEFAULT 'draft', created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY uq_resolution_no(resolution_no), FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS powers_of_attorney (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, grantor VARCHAR(190) NOT NULL, attorney_name VARCHAR(190) NOT NULL,
 powers_text LONGTEXT NOT NULL, valid_from DATE NOT NULL, valid_until DATE NULL, spending_limit DECIMAL(18,2) NULL, currency CHAR(3) NULL,
 status ENUM('draft','active','expired','revoked') NOT NULL DEFAULT 'draft', document_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL, FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

UPDATE users SET may_sign_documents=1, signer_authorized_at=NOW() WHERE role IN ('admin','director');
-- V014 - External stakeholder portals, secure data rooms and collaboration
CREATE TABLE IF NOT EXISTS external_organizations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 organization_type ENUM('supplier','bank','broker','carrier','insurer','auditor','inspection','other') NOT NULL,
 legal_name VARCHAR(190) NOT NULL,
 trade_name VARCHAR(190) NULL,
 country_code CHAR(2) NULL,
 tax_id VARCHAR(80) NULL,
 status ENUM('pending','active','suspended','blocked') NOT NULL DEFAULT 'pending',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,organization_type,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(190) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(60) NULL,
 job_title VARCHAR(150) NULL,
 password_hash VARCHAR(255) NULL,
 locale VARCHAR(10) NOT NULL DEFAULT 'en',
 role ENUM('organization_admin','manager','operator','viewer','auditor') NOT NULL DEFAULT 'viewer',
 status ENUM('invited','active','suspended','blocked') NOT NULL DEFAULT 'invited',
 must_change_password TINYINT(1) NOT NULL DEFAULT 1,
 last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE CASCADE,
 UNIQUE KEY uq_external_user_email(email),
 INDEX(organization_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_invitations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(190) NOT NULL,
 role VARCHAR(40) NOT NULL DEFAULT 'viewer',
 token_hash CHAR(64) NOT NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 invited_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_external_invitation_token(token_hash),
 INDEX(email,expires_at,accepted_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_operation_access (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 external_user_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NOT NULL,
 access_level ENUM('view','collaborate','upload','approve') NOT NULL DEFAULT 'view',
 granted_by BIGINT UNSIGNED NULL,
 granted_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(granted_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_external_operation_access(external_user_id,operation_id),
 INDEX(operation_id,revoked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 operation_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 description TEXT NULL,
 classification ENUM('internal','confidential','restricted','external') NOT NULL DEFAULT 'confidential',
 status ENUM('draft','active','locked','archived') NOT NULL DEFAULT 'draft',
 expires_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_room_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 data_room_id BIGINT UNSIGNED NOT NULL,
 external_user_id BIGINT UNSIGNED NOT NULL,
 permission ENUM('view','download','upload','manage') NOT NULL DEFAULT 'view',
 watermark_enabled TINYINT(1) NOT NULL DEFAULT 1,
 download_allowed TINYINT(1) NOT NULL DEFAULT 0,
 expires_at DATETIME NULL,
 added_by BIGINT UNSIGNED NULL,
 added_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE CASCADE,
 FOREIGN KEY(added_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_data_room_member(data_room_id,external_user_id),
 INDEX(external_user_id,revoked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_room_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 data_room_id BIGINT UNSIGNED NOT NULL,
 operation_document_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 item_type ENUM('document','notice','link') NOT NULL DEFAULT 'document',
 external_url VARCHAR(500) NULL,
 notes TEXT NULL,
 version_label VARCHAR(30) NULL,
 checksum_sha256 CHAR(64) NULL,
 visible_from DATETIME NULL,
 visible_until DATETIME NULL,
 uploaded_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(data_room_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 data_room_id BIGINT UNSIGNED NULL,
 internal_user_id BIGINT UNSIGNED NULL,
 external_user_id BIGINT UNSIGNED NULL,
 direction ENUM('internal_to_external','external_to_internal','system') NOT NULL,
 subject VARCHAR(190) NULL,
 body TEXT NOT NULL,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(internal_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE SET NULL,
 INDEX(external_user_id,read_at,created_at), INDEX(operation_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_access_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 external_user_id BIGINT UNSIGNED NULL,
 organization_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(500) NULL,
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE SET NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE SET NULL,
 INDEX(external_user_id,created_at), INDEX(entity_type,entity_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS password_reset_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 selector CHAR(24) NOT NULL UNIQUE,
 token_hash CHAR(64) NOT NULL,
 otp_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5,
 consumed_at DATETIME NULL,
 requested_ip VARCHAR(64) NULL,
 requested_user_agent VARCHAR(255) NULL,
 created_at DATETIME NOT NULL,
 INDEX(user_id,expires_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS password_reset_rate_limits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 rate_key CHAR(64) NOT NULL,
 window_started_at DATETIME NOT NULL,
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 1,
 UNIQUE KEY uq_password_reset_rate(rate_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 session_hash CHAR(64) NOT NULL UNIQUE,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(255) NULL,
 created_at DATETIME NOT NULL,
 last_seen_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 INDEX(user_id,revoked_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS translation_overrides (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 page_scope VARCHAR(190) NOT NULL DEFAULT '*',
 source_locale VARCHAR(10) NOT NULL,
 target_locale VARCHAR(10) NOT NULL,
 source_text VARCHAR(500) NOT NULL,
 source_hash CHAR(64) NOT NULL,
 translated_text VARCHAR(500) NOT NULL,
 status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
 created_by BIGINT UNSIGNED NULL,
 updated_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_translation_override(page_scope,target_locale,source_hash),
 INDEX(target_locale,status),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS translation_editor_permissions (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 can_translate TINYINT(1) NOT NULL DEFAULT 0,
 can_publish TINYINT(1) NOT NULL DEFAULT 0,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS integration_connections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 connection_type ENUM('rest','soap','graphql','mysql','postgresql','sqlserver','oracle','sap','totvs','file') NOT NULL,
 base_url VARCHAR(500) NULL,
 auth_type ENUM('none','api_key','bearer','basic','oauth2','database') NOT NULL DEFAULT 'none',
 credentials_encrypted MEDIUMTEXT NULL,
 settings_json JSON NULL,
 status ENUM('draft','active','inactive','error') NOT NULL DEFAULT 'draft',
 last_test_at DATETIME NULL,
 last_test_status VARCHAR(30) NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(unit_id,status),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_keys (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 key_prefix VARCHAR(20) NOT NULL,
 key_hash CHAR(64) NOT NULL UNIQUE,
 allowed_ips TEXT NULL,
 scopes_json JSON NULL,
 rate_limit_per_minute INT UNSIGNED NOT NULL DEFAULT 60,
 expires_at DATETIME NULL,
 last_used_at DATETIME NULL,
 status ENUM('active','revoked','expired') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_endpoints (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 slug VARCHAR(120) NOT NULL UNIQUE,
 source_table VARCHAR(120) NOT NULL,
 allowed_fields_json JSON NOT NULL,
 allowed_methods VARCHAR(100) NOT NULL DEFAULT 'GET',
 filter_fields_json JSON NULL,
 unit_scoped TINYINT(1) NOT NULL DEFAULT 1,
 status ENUM('draft','active','inactive') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_request_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 api_key_id BIGINT UNSIGNED NULL,
 endpoint_id BIGINT UNSIGNED NULL,
 method VARCHAR(10) NOT NULL,
 request_path VARCHAR(500) NOT NULL,
 status_code SMALLINT UNSIGNED NOT NULL,
 duration_ms INT UNSIGNED NULL,
 ip_address VARCHAR(64) NULL,
 request_id CHAR(36) NOT NULL,
 created_at DATETIME NOT NULL,
 INDEX(api_key_id,created_at), INDEX(endpoint_id,created_at),
 FOREIGN KEY(api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL,
 FOREIGN KEY(endpoint_id) REFERENCES api_endpoints(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhook_subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 event_name VARCHAR(120) NOT NULL,
 target_url VARCHAR(500) NOT NULL,
 secret_hash CHAR(64) NOT NULL,
 headers_json JSON NULL,
 status ENUM('active','paused','disabled') NOT NULL DEFAULT 'active',
 max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(event_name,status),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhook_deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 subscription_id BIGINT UNSIGNED NOT NULL,
 event_name VARCHAR(120) NOT NULL,
 entity_type VARCHAR(100) NULL,
 entity_id BIGINT UNSIGNED NULL,
 payload_json JSON NOT NULL,
 status ENUM('queued','processing','delivered','failed','dead_letter') NOT NULL DEFAULT 'queued',
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 response_code SMALLINT UNSIGNED NULL,
 response_body TEXT NULL,
 next_attempt_at DATETIME NULL,
 delivered_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 INDEX(status,next_attempt_at),
 FOREIGN KEY(subscription_id) REFERENCES webhook_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dynamic_forms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 slug VARCHAR(120) NOT NULL UNIQUE,
 purpose ENUM('lead','commodity_purchase','commodity_sale','monetization','financing','kyc','service','other') NOT NULL DEFAULT 'lead',
 workflow_id BIGINT UNSIGNED NULL,
 locale VARCHAR(10) NOT NULL DEFAULT 'pt',
 success_message TEXT NULL,
 status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(workflow_id) REFERENCES workflow_definitions(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dynamic_form_fields (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 form_id BIGINT UNSIGNED NOT NULL,
 field_key VARCHAR(120) NOT NULL,
 label VARCHAR(190) NOT NULL,
 field_type ENUM('text','email','phone','number','currency','date','textarea','select','checkbox','file','signature','hidden') NOT NULL,
 options_json JSON NULL,
 validation_json JSON NULL,
 mapping_target VARCHAR(190) NULL,
 is_required TINYINT(1) NOT NULL DEFAULT 0,
 sort_order INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_form_field(form_id,field_key),
 FOREIGN KEY(form_id) REFERENCES dynamic_forms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dynamic_form_submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 form_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 submission_code VARCHAR(40) NOT NULL UNIQUE,
 data_json JSON NOT NULL,
 source_ip VARCHAR(64) NULL,
 user_agent VARCHAR(500) NULL,
 status ENUM('received','reviewing','applied','rejected') NOT NULL DEFAULT 'received',
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 INDEX(form_id,status),
 FOREIGN KEY(form_id) REFERENCES dynamic_forms(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS document_ai_jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 document_id BIGINT UNSIGNED NULL,
 job_type ENUM('ocr','document_reader','contract_compare','translation','classification') NOT NULL,
 source_name VARCHAR(255) NULL,
 source_path VARCHAR(500) NULL,
 source_hash CHAR(64) NULL,
 provider VARCHAR(100) NOT NULL DEFAULT 'manual',
 extracted_text MEDIUMTEXT NULL,
 result_json JSON NULL,
 confidence DECIMAL(5,2) NULL,
 status ENUM('queued','processing','needs_review','approved','rejected','failed') NOT NULL DEFAULT 'queued',
 error_message TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 reviewed_at DATETIME NULL,
 INDEX(status,job_type),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contract_comparisons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 baseline_text MEDIUMTEXT NOT NULL,
 received_text MEDIUMTEXT NOT NULL,
 comparison_json JSON NULL,
 risk_level ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
 status ENUM('draft','reviewed','approved','rejected') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 reviewed_at DATETIME NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_risk_scores (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 score DECIMAL(5,2) NOT NULL,
 risk_level ENUM('low','medium','high','critical') NOT NULL,
 factors_json JSON NOT NULL,
 explanation TEXT NULL,
 calculated_by ENUM('rules','manual','ai') NOT NULL DEFAULT 'rules',
 calculated_at DATETIME NOT NULL,
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 INDEX(operation_id,calculated_at),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_catalog_assets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 connection_id BIGINT UNSIGNED NULL,
 unit_id BIGINT UNSIGNED NULL,
 asset_name VARCHAR(190) NOT NULL,
 asset_type ENUM('table','view','api','file','report','dataset') NOT NULL,
 source_name VARCHAR(190) NULL,
 description TEXT NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 steward_user_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NULL,
 classification ENUM('public','internal','restricted','confidential','ultra_confidential') NOT NULL DEFAULT 'internal',
 contains_personal_data TINYINT(1) NOT NULL DEFAULT 0,
 retention_days INT UNSIGNED NULL,
 schema_json JSON NULL,
 status ENUM('active','inactive','deprecated') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(classification,status),
 FOREIGN KEY(connection_id) REFERENCES integration_connections(id) ON DELETE SET NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(steward_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ai_providers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 provider_type ENUM('openai_compatible','azure_openai','gemini','anthropic','local','manual') NOT NULL DEFAULT 'openai_compatible',
 base_url VARCHAR(500) NULL,
 model_name VARCHAR(190) NULL,
 api_key_encrypted MEDIUMTEXT NULL,
 settings_json JSON NULL,
 is_default TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('active','inactive','error') NOT NULL DEFAULT 'inactive',
 last_test_at DATETIME NULL,
 last_test_status VARCHAR(30) NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_governance_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 allowed_modules_json JSON NULL,
 blocked_fields_json JSON NULL,
 max_classification ENUM('public','internal','restricted','confidential','ultra_confidential') NOT NULL DEFAULT 'internal',
 allow_external_ai TINYINT(1) NOT NULL DEFAULT 0,
 allow_document_content TINYINT(1) NOT NULL DEFAULT 0,
 allow_financial_values TINYINT(1) NOT NULL DEFAULT 0,
 require_human_approval_for_actions TINYINT(1) NOT NULL DEFAULT 1,
 retention_days INT UNSIGNED NOT NULL DEFAULT 90,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_prompt_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 category ENUM('executive','commercial','financial','compliance','logistics','documents','general') NOT NULL DEFAULT 'general',
 locale VARCHAR(10) NOT NULL DEFAULT 'pt',
 prompt_text MEDIUMTEXT NOT NULL,
 required_module VARCHAR(100) NULL,
 status ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_conversations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 provider_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 locale VARCHAR(10) NOT NULL DEFAULT 'pt',
 status ENUM('active','archived','deleted') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(user_id,unit_id,status),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(provider_id) REFERENCES ai_providers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 conversation_id BIGINT UNSIGNED NOT NULL,
 role ENUM('system','user','assistant','tool') NOT NULL,
 content MEDIUMTEXT NOT NULL,
 context_hash CHAR(64) NULL,
 tokens_input INT UNSIGNED NULL,
 tokens_output INT UNSIGNED NULL,
 redactions_count INT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 INDEX(conversation_id,created_at),
 FOREIGN KEY(conversation_id) REFERENCES ai_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_context_access_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 conversation_id BIGINT UNSIGNED NULL,
 message_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 module_key VARCHAR(100) NOT NULL,
 entity_type VARCHAR(100) NULL,
 entity_id BIGINT UNSIGNED NULL,
 fields_json JSON NULL,
 classification VARCHAR(40) NULL,
 decision ENUM('allowed','redacted','blocked') NOT NULL,
 reason VARCHAR(500) NULL,
 created_at DATETIME NOT NULL,
 INDEX(user_id,created_at),
 FOREIGN KEY(conversation_id) REFERENCES ai_conversations(id) ON DELETE SET NULL,
 FOREIGN KEY(message_id) REFERENCES ai_messages(id) ON DELETE SET NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_action_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 conversation_id BIGINT UNSIGNED NULL,
 requested_by BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 action_type ENUM('create_task','create_report','draft_document','send_notification','update_operation','other') NOT NULL,
 target_type VARCHAR(100) NULL,
 target_id BIGINT UNSIGNED NULL,
 payload_json JSON NOT NULL,
 risk_level ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
 status ENUM('pending','approved','rejected','executed','failed','cancelled') NOT NULL DEFAULT 'pending',
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 executed_at DATETIME NULL,
 error_message TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX(status,unit_id),
 FOREIGN KEY(conversation_id) REFERENCES ai_conversations(id) ON DELETE SET NULL,
 FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS compliance_providers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 provider_code VARCHAR(80) NOT NULL UNIQUE,
 provider_category ENUM('sanctions','pep','adverse_media','corporate_registry','identity','banking','vessel','country_risk','financial','multi_source') NOT NULL,
 provider_type ENUM('official_public','commercial_api','manual_import','internal') NOT NULL DEFAULT 'commercial_api',
 base_url VARCHAR(500) NULL,
 auth_type ENUM('none','api_key','bearer','basic','oauth2','custom') NOT NULL DEFAULT 'api_key',
 credentials_encrypted MEDIUMTEXT NULL,
 settings_json JSON NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 is_primary TINYINT(1) NOT NULL DEFAULT 0,
 last_sync_at DATETIME NULL,
 last_status ENUM('never','ok','warning','error') NOT NULL DEFAULT 'never',
 last_error TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_screenings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 provider_id BIGINT UNSIGNED NULL,
 screening_type ENUM('sanctions','pep','adverse_media','corporate_registry','identity','ubo','banking','vessel','country_risk','financial','full_due_diligence') NOT NULL,
 subject_type ENUM('company','person','bank','vessel','country') NOT NULL DEFAULT 'company',
 subject_name VARCHAR(255) NOT NULL,
 subject_identifier VARCHAR(190) NULL,
 request_json JSON NULL,
 response_json JSON NULL,
 status ENUM('queued','running','completed','review_required','failed','cancelled') NOT NULL DEFAULT 'queued',
 match_status ENUM('clear','possible_match','confirmed_match','not_applicable','unknown') NOT NULL DEFAULT 'unknown',
 risk_points INT NOT NULL DEFAULT 0,
 confidence DECIMAL(5,2) NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 review_notes TEXT NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(client_id,screening_type,status),
 INDEX(match_status,created_at),
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(provider_id) REFERENCES compliance_providers(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_screening_hits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 screening_id BIGINT UNSIGNED NOT NULL,
 external_reference VARCHAR(190) NULL,
 list_name VARCHAR(190) NULL,
 matched_name VARCHAR(255) NOT NULL,
 match_score DECIMAL(5,2) NULL,
 hit_category ENUM('sanctions','pep','relative_associate','adverse_media','enforcement','debarment','corporate','identity','bank','vessel','other') NOT NULL,
 severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
 status ENUM('open','false_positive','confirmed','accepted_risk','remediated') NOT NULL DEFAULT 'open',
 source_url VARCHAR(700) NULL,
 details_json JSON NULL,
 resolution_notes TEXT NULL,
 resolved_by BIGINT UNSIGNED NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(screening_id,status,severity),
 FOREIGN KEY(screening_id) REFERENCES compliance_screenings(id) ON DELETE CASCADE,
 FOREIGN KEY(resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS client_trust_scores (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 score_version VARCHAR(30) NOT NULL DEFAULT 'GTS-1.0',
 global_score SMALLINT UNSIGNED NOT NULL,
 compliance_score SMALLINT UNSIGNED NOT NULL,
 financial_score SMALLINT UNSIGNED NOT NULL,
 trade_score SMALLINT UNSIGNED NOT NULL,
 banking_score SMALLINT UNSIGNED NOT NULL,
 country_score SMALLINT UNSIGNED NOT NULL,
 operational_score SMALLINT UNSIGNED NOT NULL,
 relationship_score SMALLINT UNSIGNED NOT NULL,
 rating VARCHAR(10) NOT NULL,
 risk_level ENUM('very_low','low','moderate','high','critical','blocked') NOT NULL,
 decision ENUM('approve','approve_with_conditions','enhanced_due_diligence','manual_review','reject','blocked') NOT NULL,
 reasons_json JSON NULL,
 calculated_by BIGINT UNSIGNED NULL,
 calculated_at DATETIME NOT NULL,
 valid_until DATETIME NULL,
 is_current TINYINT(1) NOT NULL DEFAULT 1,
 INDEX(client_id,is_current,calculated_at),
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS client_score_components (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trust_score_id BIGINT UNSIGNED NOT NULL,
 component_key VARCHAR(80) NOT NULL,
 label VARCHAR(190) NOT NULL,
 raw_score DECIMAL(10,2) NOT NULL,
 weight_percent DECIMAL(5,2) NOT NULL,
 weighted_score DECIMAL(10,2) NOT NULL,
 source VARCHAR(190) NULL,
 explanation TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX(trust_score_id),
 FOREIGN KEY(trust_score_id) REFERENCES client_trust_scores(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_monitoring_subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 frequency ENUM('daily','weekly','monthly','quarterly') NOT NULL DEFAULT 'daily',
 monitor_sanctions TINYINT(1) NOT NULL DEFAULT 1,
 monitor_pep TINYINT(1) NOT NULL DEFAULT 1,
 monitor_adverse_media TINYINT(1) NOT NULL DEFAULT 1,
 monitor_corporate_changes TINYINT(1) NOT NULL DEFAULT 1,
 monitor_financial_changes TINYINT(1) NOT NULL DEFAULT 0,
 next_run_at DATETIME NULL,
 last_run_at DATETIME NULL,
 status ENUM('active','paused','cancelled') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_monitor_client(client_id),
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS compliance_monitoring_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 subscription_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 event_type ENUM('new_sanction','pep_change','adverse_media','corporate_change','director_change','ubo_change','financial_change','document_expiry','score_change','provider_error','other') NOT NULL,
 severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 old_value_json JSON NULL,
 new_value_json JSON NULL,
 status ENUM('new','acknowledged','investigating','resolved','dismissed') NOT NULL DEFAULT 'new',
 assigned_to BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 resolved_at DATETIME NULL,
 INDEX(client_id,status,severity),
 FOREIGN KEY(subscription_id) REFERENCES compliance_monitoring_subscriptions(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS due_diligence_reports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 report_number VARCHAR(80) NOT NULL UNIQUE,
 report_type ENUM('standard','enhanced','periodic_review','event_driven') NOT NULL DEFAULT 'standard',
 executive_summary MEDIUMTEXT NULL,
 findings_json JSON NULL,
 recommendations_json JSON NULL,
 final_decision ENUM('approve','approve_with_conditions','enhanced_monitoring','reject','blocked','pending') NOT NULL DEFAULT 'pending',
 score_id BIGINT UNSIGNED NULL,
 status ENUM('draft','in_review','approved','signed','archived') NOT NULL DEFAULT 'draft',
 prepared_by BIGINT UNSIGNED NULL,
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 content_hash CHAR(64) NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(score_id) REFERENCES client_trust_scores(id) ON DELETE SET NULL,
 FOREIGN KEY(prepared_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO compliance_providers(name,provider_code,provider_category,provider_type,enabled,is_primary,created_at,updated_at) VALUES
('OFAC Sanctions','ofac','sanctions','official_public',0,0,NOW(),NOW()),
('United Nations Sanctions','un_sanctions','sanctions','official_public',0,0,NOW(),NOW()),
('European Union Sanctions','eu_sanctions','sanctions','official_public',0,0,NOW(),NOW()),
('UK Sanctions List','uk_sanctions','sanctions','official_public',0,0,NOW(),NOW()),
('Swiss SECO Sanctions','seco','sanctions','official_public',0,0,NOW(),NOW()),
('World Bank Debarred Firms','world_bank','sanctions','official_public',0,0,NOW(),NOW()),
('GLEIF LEI','gleif','banking','official_public',0,0,NOW(),NOW()),
('OpenSanctions','opensanctions','multi_source','commercial_api',0,0,NOW(),NOW()),
('LSEG World-Check','lseg_world_check','multi_source','commercial_api',0,0,NOW(),NOW()),
('Dow Jones Risk & Compliance','dow_jones','multi_source','commercial_api',0,0,NOW(),NOW()),
('LexisNexis Risk Solutions','lexisnexis','multi_source','commercial_api',0,0,NOW(),NOW()),
('Moody''s Grid / Orbis','moodys','corporate_registry','commercial_api',0,0,NOW(),NOW()),
('ComplyAdvantage','complyadvantage','multi_source','commercial_api',0,0,NOW(),NOW()),
('Sumsub','sumsub','identity','commercial_api',0,0,NOW(),NOW()),
('Veriff','veriff','identity','commercial_api',0,0,NOW(),NOW()),
('MarineTraffic / Vessel Screening','marine_traffic','vessel','commercial_api',0,0,NOW(),NOW());


-- Global Commodity & Finance Platform V019
-- Global Trade Intelligence, entity network, opportunity and reliability scoring.

CREATE TABLE IF NOT EXISTS global_entities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_type ENUM('company','person','bank','vessel','port','country','commodity','organization') NOT NULL DEFAULT 'company',
 legal_name VARCHAR(255) NOT NULL,
 trade_name VARCHAR(255) NULL,
 country_code CHAR(2) NULL,
 registration_number VARCHAR(190) NULL,
 lei VARCHAR(40) NULL,
 duns VARCHAR(30) NULL,
 tax_identifier VARCHAR(80) NULL,
 parent_entity_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 metadata_json JSON NULL,
 status ENUM('active','inactive','watchlist','blocked') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(entity_type,legal_name), INDEX(country_code,status), INDEX(lei),
 FOREIGN KEY(parent_entity_id) REFERENCES global_entities(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS entity_relationships (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 from_entity_id BIGINT UNSIGNED NOT NULL,
 to_entity_id BIGINT UNSIGNED NOT NULL,
 relationship_type ENUM('owns','controlled_by','director_of','ubo_of','associated_with','banks_with','supplies','buys_from','operates','owns_vessel','uses_port','party_to_operation','other') NOT NULL,
 ownership_percent DECIMAL(7,4) NULL,
 valid_from DATE NULL, valid_until DATE NULL,
 confidence DECIMAL(5,2) NULL,
 source VARCHAR(255) NULL,
 evidence_json JSON NULL,
 risk_flag TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('active','historical','disputed') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(from_entity_id,relationship_type), INDEX(to_entity_id,relationship_type), INDEX(risk_flag,status),
 FOREIGN KEY(from_entity_id) REFERENCES global_entities(id) ON DELETE CASCADE,
 FOREIGN KEY(to_entity_id) REFERENCES global_entities(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS opportunity_scores (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 score_version VARCHAR(30) NOT NULL DEFAULT 'OPS-1.0',
 opportunity_score SMALLINT UNSIGNED NOT NULL,
 close_probability DECIMAL(5,2) NOT NULL,
 priority ENUM('low','normal','high','urgent','strategic') NOT NULL DEFAULT 'normal',
 margin_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 client_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 negotiation_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 volume_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 logistics_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 bank_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 supplier_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 reasons_json JSON NULL,
 calculated_by BIGINT UNSIGNED NULL,
 calculated_at DATETIME NOT NULL,
 valid_until DATETIME NULL,
 is_current TINYINT(1) NOT NULL DEFAULT 1,
 INDEX(operation_id,is_current,calculated_at),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reliability_scores (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 subject_type ENUM('supplier','buyer','bank') NOT NULL,
 subject_id BIGINT UNSIGNED NOT NULL,
 score_version VARCHAR(30) NOT NULL DEFAULT 'REL-1.0',
 reliability_score SMALLINT UNSIGNED NOT NULL,
 rating VARCHAR(10) NOT NULL,
 on_time_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 quality_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 payment_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 dispute_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 responsiveness_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 compliance_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 evidence_json JSON NULL,
 calculated_by BIGINT UNSIGNED NULL,
 calculated_at DATETIME NOT NULL,
 is_current TINYINT(1) NOT NULL DEFAULT 1,
 INDEX(subject_type,subject_id,is_current),
 FOREIGN KEY(calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_intelligence_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_id BIGINT UNSIGNED NULL,
 bank_name VARCHAR(255) NOT NULL,
 swift_bic VARCHAR(20) NULL,
 lei VARCHAR(40) NULL,
 country_code CHAR(2) NULL,
 external_rating VARCHAR(30) NULL,
 internal_reliability_score SMALLINT UNSIGNED NULL,
 instruments_issued INT UNSIGNED NOT NULL DEFAULT 0,
 instruments_accepted INT UNSIGNED NOT NULL DEFAULT 0,
 average_issue_days DECIMAL(8,2) NULL,
 compliance_status ENUM('clear','review','restricted','blocked','unknown') NOT NULL DEFAULT 'unknown',
 notes TEXT NULL,
 updated_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_bank_swift(swift_bic),
 FOREIGN KEY(entity_id) REFERENCES global_entities(id) ON DELETE SET NULL,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS commodity_intelligence_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NULL,
 commodity_name VARCHAR(190) NOT NULL,
 hs_code VARCHAR(30) NULL,
 benchmark_name VARCHAR(120) NULL,
 market_currency CHAR(3) NULL,
 supply_outlook ENUM('strong_decline','decline','stable','growth','strong_growth','unknown') NOT NULL DEFAULT 'unknown',
 demand_outlook ENUM('strong_decline','decline','stable','growth','strong_growth','unknown') NOT NULL DEFAULT 'unknown',
 seasonality_json JSON NULL,
 key_exporters_json JSON NULL,
 key_importers_json JSON NULL,
 market_risk_score SMALLINT UNSIGNED NULL,
 latest_price DECIMAL(20,6) NULL,
 price_unit VARCHAR(40) NULL,
 price_as_of DATETIME NULL,
 source VARCHAR(255) NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS country_intelligence_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 country_code CHAR(2) NOT NULL UNIQUE,
 country_name VARCHAR(190) NOT NULL,
 political_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 fx_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 banking_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 corruption_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 regulatory_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 trade_restriction_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 sanctions_risk SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 economic_stability SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 overall_country_score SMALLINT UNSIGNED NOT NULL DEFAULT 500,
 risk_level ENUM('very_low','low','moderate','high','critical') NOT NULL DEFAULT 'moderate',
 source VARCHAR(255) NULL,
 source_date DATE NULL,
 data_json JSON NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS smart_recommendations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 recommendation_type ENUM('request_documents','enhanced_due_diligence','change_payment_terms','additional_guarantee','reduce_credit_limit','prioritize_supplier','prioritize_client','logistics_action','legal_review','other') NOT NULL,
 priority ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
 title VARCHAR(255) NOT NULL,
 rationale TEXT NOT NULL,
 evidence_json JSON NULL,
 status ENUM('new','accepted','rejected','implemented','expired') NOT NULL DEFAULT 'new',
 generated_by ENUM('rule','ai','user') NOT NULL DEFAULT 'rule',
 assigned_to BIGINT UNSIGNED NULL,
 resolved_by BIGINT UNSIGNED NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(operation_id,status,priority), INDEX(client_id,status),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS entity_timeline_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_type ENUM('client','supplier','bank','operation','global_entity') NOT NULL,
 entity_id BIGINT UNSIGNED NOT NULL,
 event_type VARCHAR(80) NOT NULL,
 title VARCHAR(255) NOT NULL,
 description TEXT NULL,
 source_table VARCHAR(80) NULL,
 source_id BIGINT UNSIGNED NULL,
 metadata_json JSON NULL,
 occurred_at DATETIME NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 INDEX(entity_type,entity_id,occurred_at),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_decisions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 department_key ENUM('commercial','compliance','finance','legal','logistics','executive') NOT NULL,
 decision ENUM('pending','approve','approve_with_conditions','reject','abstain') NOT NULL DEFAULT 'pending',
 conditions TEXT NULL,
 opinion TEXT NULL,
 decided_by BIGINT UNSIGNED NULL,
 decided_at DATETIME NULL,
 status ENUM('open','final') NOT NULL DEFAULT 'open',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_operation_department(operation_id,department_key),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(decided_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trade_intelligence_sources (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 source_type ENUM('market_price','trade_flow','economic','news','shipping','manual','api') NOT NULL,
 base_url VARCHAR(500) NULL,
 credentials_encrypted MEDIUMTEXT NULL,
 settings_json JSON NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 last_sync_at DATETIME NULL,
 last_status ENUM('never','ok','warning','error') NOT NULL DEFAULT 'never',
 last_error TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;