-- Admin schema additions for user management
-- Run this after the main schema.sql

USE `securitisys_greg`;

-- Add status and usage tracking columns to users table
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `status` ENUM('active', 'disabled', 'suspended') NOT NULL DEFAULT 'active' AFTER `image`,
ADD COLUMN IF NOT EXISTS `is_admin` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`,
ADD COLUMN IF NOT EXISTS `query_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `is_admin`,
ADD COLUMN IF NOT EXISTS `last_active` TIMESTAMP NULL AFTER `query_count`;

-- Create index for admin queries
CREATE INDEX IF NOT EXISTS `idx_users_status` ON `users` (`status`);
CREATE INDEX IF NOT EXISTS `idx_users_is_admin` ON `users` (`is_admin`);

-- View for admin dashboard
DROP VIEW IF EXISTS `v_user_stats`;
CREATE VIEW `v_user_stats` AS
SELECT 
  u.id,
  u.username,
  u.email,
  u.name,
  u.status,
  u.is_admin,
  u.query_count,
  u.last_active,
  u.created_at,
  COUNT(DISTINCT c.id) as conversation_count,
  COUNT(m.id) as message_count
FROM users u
LEFT JOIN conversations c ON u.id = c.user_id
LEFT JOIN messages m ON c.id = m.conversation_id
GROUP BY u.id;