-- Base de datos para Cler Admin Platform (PostgreSQL)
CREATE DATABASE cler_admin;

\c cler_admin;

-- Tabla de usuarios de la app
CREATE TABLE app_users (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE NOT NULL,
    user_name VARCHAR(255),
    install_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_active TIMESTAMP 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
);

CREATE INDEX idx_device_id ON app_users(device_id);
CREATE INDEX idx_last_active ON app_users(last_active);

-- Tabla de reportes de errores
CREATE TABLE error_reports (
    id SERIAL 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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'new' CHECK (status IN ('new', 'reviewing', 'fixed', 'ignored')),
    priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    notes TEXT,
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE SET NULL
);

CREATE INDEX idx_status ON error_reports(status);
CREATE INDEX idx_timestamp ON error_reports(timestamp);

-- Tabla de anuncios/notificaciones
CREATE TABLE announcements (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(20) DEFAULT 'info' CHECK (type IN ('info', 'warning', 'update', 'feature')),
    target_audience VARCHAR(20) DEFAULT 'all' CHECK (target_audience IN ('all', 'active', 'inactive', 'specific')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    scheduled_for TIMESTAMP,
    sent_at TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'scheduled', 'sent')),
    total_sent INT DEFAULT 0,
    total_delivered INT DEFAULT 0
);

CREATE INDEX idx_ann_status ON announcements(status);

-- Tabla de interacciones/solicitudes
CREATE TABLE user_interactions (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255),
    interaction_type VARCHAR(100),
    user_message TEXT,
    ai_response TEXT,
    emotion VARCHAR(50),
    response_time_ms INT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_id VARCHAR(255),
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE CASCADE
);

CREATE INDEX idx_ui_device_id ON user_interactions(device_id);
CREATE INDEX idx_ui_timestamp ON user_interactions(timestamp);
CREATE INDEX idx_interaction_type ON user_interactions(interaction_type);

-- Tabla de aprendizaje de la IA
CREATE TABLE ai_learning_data (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255),
    learning_type VARCHAR(20) NOT NULL CHECK (learning_type IN ('memory', 'preference', 'behavior', 'context')),
    key_data VARCHAR(255),
    value_data TEXT,
    confidence_score DECIMAL(3,2) DEFAULT 0.50,
    usage_count INT DEFAULT 1,
    last_used TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES app_users(device_id) ON DELETE CASCADE
);

CREATE INDEX idx_learning_type ON ai_learning_data(learning_type);
CREATE INDEX idx_ald_device_id ON ai_learning_data(device_id);

-- Tabla de métricas diarias
CREATE TABLE daily_metrics (
    id SERIAL 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
);

CREATE INDEX idx_metric_date ON daily_metrics(metric_date);

-- 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
(CURRENT_DATE, 3, 2, 0, 45, 2, 850),
(CURRENT_DATE - INTERVAL '1 day', 3, 3, 1, 67, 1, 920);
