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;