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;