-- ============================================
-- CLER ADMIN PLATFORM - PostgreSQL Database
-- Orden: Tablas sin FK → Tablas con FK
-- ============================================

-- 1. ROLES (sin dependencias)
CREATE TABLE IF NOT EXISTS roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. ADMIN USERS (depende de roles)
CREATE TABLE IF NOT EXISTS admin_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    role_id INTEGER REFERENCES roles(id) ON DELETE SET NULL,
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. PERMISSIONS (sin dependencias)
CREATE TABLE IF NOT EXISTS permissions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    description TEXT,
    module VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. ROLE_PERMISSIONS (depende de roles y permissions)
CREATE TABLE IF NOT EXISTS role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    permission_id INTEGER REFERENCES permissions(id) ON DELETE CASCADE,
    UNIQUE(role_id, permission_id)
);

-- 5. LANDING_CONTENT (sin dependencias)
CREATE TABLE IF NOT EXISTS landing_content (
    id SERIAL PRIMARY KEY,
    section VARCHAR(50) NOT NULL,
    title VARCHAR(200),
    subtitle VARCHAR(300),
    content JSONB,
    display_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(section)
);

-- 6. APP_DOWNLOADS (sin dependencias)
CREATE TABLE IF NOT EXISTS app_downloads (
    id SERIAL PRIMARY KEY,
    version VARCHAR(20) NOT NULL,
    apk_filename VARCHAR(255) NOT NULL,
    apk_size BIGINT NOT NULL,
    release_notes TEXT,
    download_count INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 7. TELEMETRY (sin dependencias)
CREATE TABLE IF NOT EXISTS telemetry (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    user_id INTEGER,
    is_active BOOLEAN DEFAULT false,
    storage_used BIGINT DEFAULT 0,
    internet_usage BIGINT DEFAULT 0,
    conversation_count INTEGER DEFAULT 0,
    app_usage_minutes INTEGER DEFAULT 0,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 8. FEEDBACK (sin dependencias)
CREATE TABLE IF NOT EXISTS feedback (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(100),
    device_id VARCHAR(255),
    feedback_type VARCHAR(50),
    subject VARCHAR(200),
    message TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'new',
    admin_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 9. ANNOUNCEMENTS (depende de admin_users)
CREATE TABLE IF NOT EXISTS announcements (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) DEFAULT 'info',
    action VARCHAR(50) DEFAULT 'none',
    image_url VARCHAR(500),
    target_devices VARCHAR(50) DEFAULT 'all',
    priority INTEGER DEFAULT 0,
    expires_at TIMESTAMP,
    block_duration INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES admin_users(id) ON DELETE SET NULL,
    status VARCHAR(20) DEFAULT 'active'
);

-- 10. DEVICE_ANNOUNCEMENTS (depende de announcements)
CREATE TABLE IF NOT EXISTS device_announcements (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    announcement_id INTEGER REFERENCES announcements(id) ON DELETE CASCADE,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dismissed_at TIMESTAMP
);

-- ============================================
-- ÍNDICES PARA OPTIMIZACIÓN
-- ============================================

CREATE INDEX IF NOT EXISTS idx_admin_users_username ON admin_users(username);
CREATE INDEX IF NOT EXISTS idx_admin_users_email ON admin_users(email);
CREATE INDEX IF NOT EXISTS idx_telemetry_device_id ON telemetry(device_id);
CREATE INDEX IF NOT EXISTS idx_telemetry_last_activity ON telemetry(last_activity);
CREATE INDEX IF NOT EXISTS idx_announcements_status ON announcements(status);
CREATE INDEX IF NOT EXISTS idx_device_announcements_device ON device_announcements(device_id);
CREATE INDEX IF NOT EXISTS idx_feedback_status ON feedback(status);

-- ============================================
-- DATOS INICIALES
-- ============================================

-- Rol Super Admin
INSERT INTO roles (name, display_name, description) 
VALUES ('superadmin', 'Super Administrador', 'Acceso total al sistema')
ON CONFLICT (name) DO NOTHING;

-- Usuario Admin por defecto (password: admin123)
INSERT INTO admin_users (username, password, email, full_name, role_id, is_active)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@cler.com', 'Administrador', 1, true)
ON CONFLICT (username) DO NOTHING;

-- Permisos del sistema
INSERT INTO permissions (name, display_name, description, module) VALUES
('view_dashboard', 'Ver Dashboard', 'Ver estadísticas del dashboard', 'dashboard'),
('view_total_users', 'Ver Total Usuarios', 'Ver total de usuarios', 'dashboard'),
('view_active_devices', 'Ver Dispositivos Activos', 'Ver dispositivos activos', 'dashboard'),
('view_errors', 'Ver Errores', 'Ver errores del sistema', 'dashboard'),
('view_storage', 'Ver Almacenamiento', 'Ver uso de almacenamiento', 'dashboard'),
('view_internet_traffic', 'Ver Tráfico Internet', 'Ver tráfico de internet', 'dashboard'),
('view_conversations', 'Ver Conversaciones', 'Ver flujo de conversaciones', 'dashboard'),
('view_app_usage', 'Ver Uso de App', 'Ver tiempo de uso de la app', 'dashboard'),
('view_admin_users', 'Ver Usuarios Admin', 'Ver lista de usuarios administradores', 'admin'),
('create_admin_users', 'Crear Usuarios Admin', 'Crear nuevos usuarios administradores', 'admin'),
('edit_admin_users', 'Editar Usuarios Admin', 'Editar usuarios administradores', 'admin'),
('delete_admin_users', 'Eliminar Usuarios Admin', 'Eliminar usuarios administradores', 'admin'),
('view_roles', 'Ver Roles', 'Ver lista de roles', 'admin'),
('manage_roles', 'Gestionar Roles', 'Crear, editar y eliminar roles', 'admin'),
('view_landing', 'Ver Landing Page', 'Ver contenido de landing page', 'landing'),
('edit_landing', 'Editar Landing Page', 'Editar contenido de landing page', 'landing'),
('view_announcements', 'Ver Anuncios', 'Ver lista de anuncios', 'announcements'),
('create_announcements', 'Crear Anuncios', 'Crear nuevos anuncios', 'announcements'),
('delete_announcements', 'Eliminar Anuncios', 'Eliminar anuncios', 'announcements'),
('block_devices', 'Bloquear Dispositivos', 'Bloquear uso de app en dispositivos', 'announcements'),
('manage_downloads', 'Gestionar Descargas', 'Subir y gestionar versiones de la app', 'downloads'),
('view_feedback', 'Ver Feedback', 'Ver feedback de usuarios', 'feedback')
ON CONFLICT (name) DO NOTHING;

-- Asignar todos los permisos al Super Admin
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions
ON CONFLICT DO NOTHING;

-- ============================================
-- SCRIPT COMPLETADO
-- ============================================
