-- Tabla de telemetría de dispositivos
CREATE TABLE IF NOT EXISTS device_telemetry (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE NOT NULL,
    user_id INTEGER,
    is_active BOOLEAN DEFAULT true,
    storage_used_mb BIGINT DEFAULT 0,
    internet_usage_bytes BIGINT DEFAULT 0,
    conversation_count INTEGER DEFAULT 0,
    last_activity TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de consultas de usuarios (para top queries)
CREATE TABLE IF NOT EXISTS user_queries (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    device_id VARCHAR(255),
    query_text TEXT NOT NULL,
    query_type VARCHAR(50), -- 'web_search', 'conversation', 'command'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Índices para mejorar rendimiento
CREATE INDEX IF NOT EXISTS idx_device_telemetry_device_id ON device_telemetry(device_id);
CREATE INDEX IF NOT EXISTS idx_device_telemetry_is_active ON device_telemetry(is_active);
CREATE INDEX IF NOT EXISTS idx_device_telemetry_last_activity ON device_telemetry(last_activity);
CREATE INDEX IF NOT EXISTS idx_user_queries_created_at ON user_queries(created_at);
CREATE INDEX IF NOT EXISTS idx_user_queries_query_text ON user_queries(query_text);

-- Trigger para actualizar updated_at automáticamente
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_device_telemetry_updated_at BEFORE UPDATE ON device_telemetry
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
