-- 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;