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