-- Base de datos para Cler Admin Platform
CREATE DATABASE IF NOT EXISTS cler_admin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cler_admin;

-- Tabla de usuarios de la app
CREATE TABLE IF NOT EXISTS app_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE NOT NULL,
    user_name VARCHAR(255),
    install_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
    app_version VARCHAR(50),
    android_version VARCHAR(50),
    device_model VARCHAR(255),
    total_conversations INT DEFAULT 0,
    total_messages INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    fcm_token TEXT,
    INDEX idx_device_id (device_id),
    INDEX idx_last_active (last_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de reportes de errores
CREATE TABLE IF NOT EXISTS error_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255),
    error_type VARCHAR(100),
    error_message TEXT,
    stack_trace TEXT,
    app_version VARCHAR(50),
    android_version VARCHAR(50),
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    status ENUM('new', 'reviewing', 'fixed', 'ignored') DEFAULT 'new',
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    notes TEXT,
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de anuncios/notificaciones
CREATE TABLE IF NOT EXISTS announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'warning', 'update', 'feature') DEFAULT 'info',
    target_audience ENUM('all', 'active', 'inactive', 'specific') DEFAULT 'all',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    scheduled_for DATETIME,
    sent_at DATETIME,
    status ENUM('draft', 'scheduled', 'sent') DEFAULT 'draft',
    total_sent INT DEFAULT 0,
    total_delivered INT DEFAULT 0,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de interacciones/solicitudes
CREATE TABLE IF NOT EXISTS user_interactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255),
    interaction_type VARCHAR(100),
    user_message TEXT,
    ai_response TEXT,
    emotion VARCHAR(50),
    response_time_ms INT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    session_id VARCHAR(255),
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE CASCADE,
    INDEX idx_device_id (device_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_interaction_type (interaction_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de aprendizaje de la IA
CREATE TABLE IF NOT EXISTS ai_learning_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255),
    learning_type ENUM('memory', 'preference', 'behavior', 'context') NOT NULL,
    key_data VARCHAR(255),
    value_data TEXT,
    confidence_score DECIMAL(3,2) DEFAULT 0.50,
    usage_count INT DEFAULT 1,
    last_used DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE CASCADE,
    INDEX idx_learning_type (learning_type),
    INDEX idx_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de métricas diarias
CREATE TABLE IF NOT EXISTS daily_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metric_date DATE UNIQUE NOT NULL,
    total_users INT DEFAULT 0,
    active_users INT DEFAULT 0,
    new_users INT DEFAULT 0,
    total_interactions INT DEFAULT 0,
    total_errors INT DEFAULT 0,
    avg_response_time_ms INT DEFAULT 0,
    INDEX idx_metric_date (metric_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar datos de ejemplo
INSERT INTO app_users (device_id, user_name, app_version, android_version, device_model, total_conversations, total_messages) VALUES
('device_001', 'Juan Pérez', '1.0.0', '13', 'Samsung Galaxy S21', 15, 234),
('device_002', 'María García', '1.0.0', '12', 'Xiaomi Redmi Note 10', 8, 156),
('device_003', 'Carlos López', '1.0.0', '14', 'Google Pixel 6', 22, 389);

INSERT INTO error_reports (device_id, error_type, error_message, app_version, status, priority) VALUES
('device_001', 'NullPointerException', 'Error al cargar contactos', '1.0.0', 'new', 'high'),
('device_002', 'NetworkException', 'Timeout en búsqueda web', '1.0.0', 'reviewing', 'medium');

INSERT INTO daily_metrics (metric_date, total_users, active_users, new_users, total_interactions, total_errors, avg_response_time_ms) VALUES
(CURDATE(), 3, 2, 0, 45, 2, 850),
(DATE_SUB(CURDATE(), INTERVAL 1 DAY), 3, 3, 1, 67, 1, 920);
