-- Active: 1742323104246@@192.168.0.177@3306@nucleo_base
-- ----------------------------
-- Drop existing tables if they exist
-- ----------------------------
DROP TABLE IF EXISTS `ai_prompts`;
DROP TABLE IF EXISTS `ai_responses`;
DROP TABLE IF EXISTS `ai_keys`;
DROP TABLE IF EXISTS `ai_module_relations`;
DROP TABLE IF EXISTS `ai_logs`;
DROP TABLE IF EXISTS `ai_categories`; -- Nueva tabla para normalización

-- ----------------------------
-- Create ai_categories table (Normalización de ENUMs)
-- ----------------------------
CREATE TABLE `ai_categories` (
    `ai_cat_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_cat_name` VARCHAR(20) NOT NULL UNIQUE,
    `ai_cat_ent_id` INT NOT NULL,
    `ai_cat_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    `ai_cat_create_user_id` INT COMMENT 'Usuario que creó el registro'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insertar valores iniciales
INSERT INTO `ai_categories` (`ai_cat_name`, `ai_cat_ent_id`) VALUES 
('support', 1), 
('sales', 1), 
('inventory', 1), 
('other', 1);

-- ----------------------------
-- Create ai_prompts table
-- ----------------------------
CREATE TABLE `ai_prompts` (
    `ai_prt_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_prt_text` TEXT NOT NULL,
    `ai_prt_cat_id` INT NOT NULL,
    `ai_prt_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ai_prt_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ai_prt_ent_id` INT NOT NULL,
    `ai_prt_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    `ai_prt_create_user_id` INT COMMENT 'Usuario que creó el registro',
    INDEX idx_entidad (ai_prt_ent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Create ai_responses table
-- ----------------------------
CREATE TABLE `ai_responses` (
    `ai_rsp_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_rsp_prompt_id` INT NOT NULL,
    `ai_rsp_text` TEXT NOT NULL,
    `ai_rsp_model_used` VARCHAR(100) DEFAULT 'GPT-4',
    `ai_rsp_confidence_score` DECIMAL(5,2) CHECK (ai_rsp_confidence_score BETWEEN 0 AND 1),
    `ai_rsp_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ai_rsp_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ai_rsp_ent_id` INT NOT NULL,
    `ai_rsp_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    `ai_rsp_create_user_id` INT COMMENT 'Usuario que creó el registro',
    INDEX idx_prompt (ai_rsp_prompt_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Create ai_keys table
-- ----------------------------
CREATE TABLE `ai_keys` (
    `ai_key_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_key_name` VARCHAR(255) UNIQUE NOT NULL,
    `ai_key_value` VARBINARY(255) NOT NULL,
    `ai_key_provider` ENUM('OpenAI', 'Azure', 'Google', 'Other') DEFAULT 'OpenAI',
    `ai_key_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ai_key_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ai_key_ent_id` INT NOT NULL,
    `ai_key_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    `ai_key_create_user_id` INT COMMENT 'Usuario que creó el registro',
    INDEX idx_provider (ai_key_provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Create ai_module_relations table
-- ----------------------------
CREATE TABLE `ai_module_relations` (
    `ai_mod_rel_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_mod_rel_name` VARCHAR(100) NOT NULL,
    `ai_mod_rel_feature` VARCHAR(100) NOT NULL,
    `ai_mod_rel_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ai_mod_rel_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `ai_mod_rel_mod_id` INT NOT NULL,
    `ai_mod_rel_ent_id` INT NOT NULL,
    `ai_mod_rel_json` JSON CHECK (JSON_VALID(ai_mod_rel_json)),
    `ai_mod_rel_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    `ai_mod_rel_create_user_id` INT COMMENT 'Usuario que creó el registro'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Create ai_logs table
-- ----------------------------
CREATE TABLE `ai_logs` (
    `ai_log_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ai_log_user_id` INT,
    `ai_log_prompt_id` INT NOT NULL,
    `ai_log_response_id` INT NOT NULL,
    `ai_log_execution_time` FLOAT,
    `ai_log_status` ENUM('success', 'error', 'pending') DEFAULT 'success',
    `ai_log_error_details` TEXT,
    `ai_log_request_metadata` JSON,
    `ai_log_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ai_log_ent_id` INT NOT NULL,
    `ai_log_state` INT DEFAULT 0 COMMENT '0=active, 1=archived, 2=deleted',
    INDEX idx_fechas (ai_log_created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Crear tabla de histórico para ai_responses
-- ----------------------------
DROP TABLE IF EXISTS `ai_responses_history`;

CREATE TABLE `ai_responses_history` (
  `ai_rsp_id` int NOT NULL AUTO_INCREMENT,
  `ai_rsp_prompt_id` int NOT NULL,
  `ai_rsp_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ai_rsp_model_used` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'GPT-4',
  `ai_rsp_confidence_score` decimal(5,2) DEFAULT NULL,
  `ai_rsp_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ai_rsp_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `ai_rsp_ent_id` int NOT NULL,
  `ai_rsp_state` int DEFAULT '0',
  `operation_type` enum('INSERT','UPDATE','DELETE') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `operation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ai_rsp_id`),
  KEY `idx_prompt_id` (`ai_rsp_prompt_id`),
  KEY `idx_ent_id` (`ai_rsp_ent_id`),
  KEY `idx_operation_time` (`operation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;