-- V020 - Global Trade Exchange
CREATE TABLE IF NOT EXISTS marketplace_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 entity_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 member_type ENUM('buyer','supplier','bank','insurer','logistics','broker','multi') NOT NULL,
 display_name VARCHAR(190) NOT NULL,
 compliance_status ENUM('pending','approved','conditional','suspended','rejected') NOT NULL DEFAULT 'pending',
 trust_score SMALLINT UNSIGNED NULL,
 operational_limit DECIMAL(20,2) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 allowed_countries_json JSON NULL,
 allowed_commodities_json JSON NULL,
 allowed_instruments_json JSON NULL,
 status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(member_type,status), INDEX(compliance_status,trust_score),
 FOREIGN KEY(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(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS marketplace_offers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 offer_code VARCHAR(50) NOT NULL UNIQUE,
 member_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 offer_type ENUM('sell','buy') NOT NULL,
 visibility ENUM('marketplace','private','invitation_only') NOT NULL DEFAULT 'marketplace',
 title VARCHAR(190) NOT NULL,
 product_id BIGINT UNSIGNED NULL,
 commodity_name VARCHAR(190) NOT NULL,
 origin_country CHAR(2) NULL,
 destination_country CHAR(2) NULL,
 origin_port VARCHAR(190) NULL,
 destination_port VARCHAR(190) NULL,
 quantity DECIMAL(20,4) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 min_quantity DECIMAL(20,4) NULL,
 price DECIMAL(20,6) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 payment_terms VARCHAR(255) NULL,
 instrument_type VARCHAR(80) NULL,
 valid_from DATE NULL,
 valid_until DATE NULL,
 compliance_min_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 status ENUM('draft','published','paused','matched','negotiation','closed','cancelled','expired') NOT NULL DEFAULT 'draft',
 terms_json JSON NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(offer_type,status,valid_until), INDEX(commodity_name,origin_country,destination_country),
 FOREIGN KEY(member_id) REFERENCES marketplace_members(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS marketplace_offer_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 offer_id BIGINT UNSIGNED NOT NULL,
 invited_member_id BIGINT UNSIGNED NOT NULL,
 invited_by BIGINT UNSIGNED NULL,
 status ENUM('pending','viewed','accepted','declined','expired') NOT NULL DEFAULT 'pending',
 token_hash CHAR(64) NULL,
 expires_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_offer_member(offer_id,invited_member_id),
 FOREIGN KEY(offer_id) REFERENCES marketplace_offers(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_member_id) REFERENCES marketplace_members(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS marketplace_matches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 source_offer_id BIGINT UNSIGNED NOT NULL,
 target_offer_id BIGINT UNSIGNED NOT NULL,
 match_score SMALLINT UNSIGNED NOT NULL,
 commodity_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 geography_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 quantity_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 price_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 compliance_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 payment_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 logistics_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 explanation_json JSON NULL,
 status ENUM('suggested','invited','accepted','rejected','converted','expired') NOT NULL DEFAULT 'suggested',
 calculated_at DATETIME NOT NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 UNIQUE KEY uq_offer_pair(source_offer_id,target_offer_id),
 INDEX(match_score,status),
 FOREIGN KEY(source_offer_id) REFERENCES marketplace_offers(id) ON DELETE CASCADE,
 FOREIGN KEY(target_offer_id) REFERENCES marketplace_offers(id) ON DELETE CASCADE,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trading_rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_code VARCHAR(50) NOT NULL UNIQUE,
 title VARCHAR(190) NOT NULL,
 match_id BIGINT UNSIGNED NULL,
 operation_id BIGINT UNSIGNED NULL,
 status ENUM('open','due_diligence','negotiation','contracting','execution','closed','cancelled') NOT NULL DEFAULT 'open',
 confidentiality ENUM('standard','confidential','strict') NOT NULL DEFAULT 'confidential',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(status,updated_at),
 FOREIGN KEY(match_id) REFERENCES marketplace_matches(id) ON DELETE SET NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trading_room_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 marketplace_member_id BIGINT UNSIGNED NULL,
 role ENUM('owner','buyer','supplier','bank','insurer','logistics','broker','legal','compliance','observer') NOT NULL,
 can_upload TINYINT(1) NOT NULL DEFAULT 1,
 can_message TINYINT(1) NOT NULL DEFAULT 1,
 can_invite TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('invited','active','removed') NOT NULL DEFAULT 'active',
 joined_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 INDEX(room_id,status),
 FOREIGN KEY(room_id) REFERENCES trading_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(marketplace_member_id) REFERENCES marketplace_members(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trading_room_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NULL,
 sender_member_id BIGINT UNSIGNED NULL,
 message_text TEXT NOT NULL,
 message_type ENUM('message','system','decision','request') NOT NULL DEFAULT 'message',
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL,
 INDEX(room_id,created_at),
 FOREIGN KEY(room_id) REFERENCES trading_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(sender_member_id) REFERENCES marketplace_members(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS deal_room_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_id BIGINT UNSIGNED NOT NULL,
 document_id BIGINT UNSIGNED NULL,
 category ENUM('loi','icpo','fco','spa','kyc','due_diligence','banking','logistics','insurance','certificate','other') NOT NULL DEFAULT 'other',
 title VARCHAR(190) NOT NULL,
 file_path VARCHAR(500) NULL,
 file_hash CHAR(64) NULL,
 version_number INT UNSIGNED NOT NULL DEFAULT 1,
 access_level ENUM('all','internal','selected') NOT NULL DEFAULT 'all',
 status ENUM('draft','shared','approved','rejected','superseded') NOT NULL DEFAULT 'shared',
 uploaded_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(room_id,category,status),
 FOREIGN KEY(room_id) REFERENCES trading_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS exchange_offers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 exchange_type ENUM('finance','freight','insurance') NOT NULL,
 provider_member_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 service_type VARCHAR(100) NOT NULL,
 origin_country CHAR(2) NULL,
 destination_country CHAR(2) NULL,
 capacity_value DECIMAL(20,4) NULL,
 capacity_unit VARCHAR(30) NULL,
 amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 fee_value DECIMAL(20,6) NULL,
 fee_type ENUM('fixed','percent','spread','quote') NOT NULL DEFAULT 'quote',
 available_from DATE NULL,
 available_until DATE NULL,
 terms_json JSON NULL,
 status ENUM('draft','published','paused','allocated','closed','cancelled','expired') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(exchange_type,status,available_until),
 FOREIGN KEY(provider_member_id) REFERENCES marketplace_members(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS exchange_quotes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 exchange_offer_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 requested_by_member_id BIGINT UNSIGNED NULL,
 quoted_amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 quoted_fee DECIMAL(20,6) NULL,
 terms_text TEXT NULL,
 valid_until DATETIME NULL,
 status ENUM('requested','quoted','countered','accepted','rejected','expired','cancelled') NOT NULL DEFAULT 'requested',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(exchange_offer_id,status),
 FOREIGN KEY(exchange_offer_id) REFERENCES exchange_offers(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(requested_by_member_id) REFERENCES marketplace_members(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS smart_pricing_runs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 offer_id BIGINT UNSIGNED NULL,
 commodity_price DECIMAL(20,6) NOT NULL DEFAULT 0,
 freight_cost DECIMAL(20,6) NOT NULL DEFAULT 0,
 insurance_cost DECIMAL(20,6) NOT NULL DEFAULT 0,
 banking_cost DECIMAL(20,6) NOT NULL DEFAULT 0,
 taxes_cost DECIMAL(20,6) NOT NULL DEFAULT 0,
 commissions_cost DECIMAL(20,6) NOT NULL DEFAULT 0,
 risk_premium DECIMAL(20,6) NOT NULL DEFAULT 0,
 target_margin_percent DECIMAL(8,4) NOT NULL DEFAULT 0,
 break_even_price DECIMAL(20,6) NOT NULL,
 suggested_price DECIMAL(20,6) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 assumptions_json JSON NULL,
 calculated_by BIGINT UNSIGNED NULL,
 calculated_at DATETIME NOT NULL,
 INDEX(operation_id,calculated_at), INDEX(offer_id,calculated_at),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(offer_id) REFERENCES marketplace_offers(id) ON DELETE SET NULL,
 FOREIGN KEY(calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS global_alerts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 alert_type ENUM('compliance','sanctions','pep','document','banking','shipment','payment','market','geopolitical','match','system') NOT NULL,
 severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
 title VARCHAR(190) NOT NULL,
 message TEXT NOT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 operation_id BIGINT UNSIGNED NULL,
 source VARCHAR(190) NULL,
 status ENUM('open','acknowledged','resolved','dismissed') NOT NULL DEFAULT 'open',
 assigned_to BIGINT UNSIGNED NULL,
 acknowledged_at DATETIME NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(status,severity,created_at), INDEX(entity_type,entity_id),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crm_global_leads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 lead_code VARCHAR(50) NOT NULL UNIQUE,
 legal_name VARCHAR(190) NOT NULL,
 contact_name VARCHAR(190) NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(80) NULL,
 country_code CHAR(2) NULL,
 source VARCHAR(120) NULL,
 broker_id BIGINT UNSIGNED NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 estimated_value DECIMAL(20,2) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 lead_score SMALLINT UNSIGNED NULL,
 opportunity_score SMALLINT UNSIGNED NULL,
 probability_percent TINYINT UNSIGNED NOT NULL DEFAULT 10,
 stage ENUM('new','qualified','kyc','proposal','negotiation','won','lost','dormant') NOT NULL DEFAULT 'new',
 next_action VARCHAR(255) NULL,
 next_action_at DATETIME NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX(stage,owner_user_id,next_action_at),
 FOREIGN KEY(broker_id) REFERENCES brokers(id) ON DELETE SET NULL,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;