-- =====================================================================
-- VisualGen AI Manual Admin Queries (For direct execution in phpMyAdmin / MySQL)
-- =====================================================================

-- 1. Categories Management
-- Add a new category
INSERT INTO `categories` (`id`, `name`, `icon`, `sort_order`, `is_active`) 
VALUES ('cat_90s_retro', '90s Grunge', 'ic_vibe_90s', 3, 1);


-- 2. Templates Management
-- Add a template (with placeholders)
INSERT INTO `templates` (`id`, `category_id`, `title`, `description`, `preview_image`, `sample_outputs`, `prompt_text`, `negative_prompt`, `is_premium`, `is_trending`, `credit_cost`, `sort_order`, `use_count`, `is_active`)
VALUES ('t_retro_car', 'cat_80s_vibe', '80s Sunset Drive', 'Put your portrait in a vintage convertible riding towards a neon pink sunset.', 'uploads/templates/sunset_car_prev.jpg', '[]', 'A vintage 1980s convertible sports car driving towards a huge neon outrun sun, subject face visible in retro rearview mirror, pastel lighting, heavy grain', 'modern cars, modern filters, sharp, smooth gradient', 0, 1, 1, 7, 0, 1);

-- Edit a template prompt text
UPDATE `templates` 
SET `prompt_text` = 'An authentic 1980s high-grain Polaroid picture of subject with vintage color fading' 
WHERE `id` = 't_polaroid_80';

-- Toggle premium / trending flags on a template
UPDATE `templates` 
SET `is_premium` = 1, `is_trending` = 1 
WHERE `id` = 't_vhs_glitch';


-- 3. Provider Management
-- Turn a provider ON/OFF
UPDATE `providers` 
SET `is_active` = 0 
WHERE `id` = 'p_openai_02';

-- Change provider priority and daily limit
UPDATE `providers` 
SET `priority` = 1, `daily_limit` = 1200 
WHERE `id` = 'p_gemini_01';

-- Change provider model name
UPDATE `providers` 
SET `model_name` = 'gemini-1.5-pro' 
WHERE `id` = 'p_gemini_01';


-- 4. User Credit Management
-- Add 50 user credits securely (uses an atomic transaction block and inserts transaction audit trail)
-- Replace 'USER_UUID_HERE' with real user id
START TRANSACTION;
UPDATE `users` 
SET `credits` = `credits` + 50 
WHERE `id` = 'USER_UUID_HERE';

INSERT INTO `credit_transactions` (`id`, `user_id`, `amount`, `reason`, `ref_id`, `balance_after`)
SELECT 
  UUID(), 
  `id`, 
  50, 
  'admin_grant', 
  NULL, 
  `credits`
FROM `users` 
WHERE `id` = 'USER_UUID_HERE';
COMMIT;

-- Deduct 10 credits from a user
START TRANSACTION;
UPDATE `users` 
SET `credits` = GREATEST(0, `credits` - 10) 
WHERE `id` = 'USER_UUID_HERE';

INSERT INTO `credit_transactions` (`id`, `user_id`, `amount`, `reason`, `ref_id`, `balance_after`)
SELECT 
  UUID(), 
  `id`, 
  -10, 
  'admin_deduct', 
  NULL, 
  `credits`
FROM `users` 
WHERE `id` = 'USER_UUID_HERE';
COMMIT;


-- 5. User Moderation / Banning
-- Ban a user
UPDATE `users` 
SET `is_banned` = 1, `banned_reason` = 'Abusive generation attempts violating NSFW policies.' 
WHERE `id` = 'USER_UUID_HERE';

-- Unban a user
UPDATE `users` 
SET `is_banned` = 0, `banned_reason` = NULL 
WHERE `id` = 'USER_UUID_HERE';


-- 6. Content Reports & Moderation
-- List open reports with generation details
SELECT r.`id` as report_id, r.`reason`, r.`note`, g.`id` as gen_id, g.`output_path`, u.`email` as user_email
FROM `reports` r
JOIN `generations` g ON r.`generation_id` = g.`id`
JOIN `users` u ON r.`user_id` = u.`id`
WHERE r.`status` = 'open';

-- Action a report: Mark report as reviewed, hide the offending generation
START TRANSACTION;
UPDATE `reports` 
SET `status` = 'actioned' 
WHERE `id` = 'REPORT_UUID_HERE';

UPDATE `generations` 
SET `is_hidden` = 1 
WHERE `id` = 'GENERATION_UUID_HERE';
COMMIT;


-- 7. Settings Management
-- Put app into scheduled maintenance mode
UPDATE `settings` 
SET `svalue` = '1' 
WHERE `skey` = 'maintenance_mode';

-- Turn off maintenance mode
UPDATE `settings` 
SET `svalue` = '0' 
WHERE `skey` = 'maintenance_mode';

-- Change minimum app version required code (forces upgrade)
UPDATE `settings` 
SET `svalue` = '2' 
WHERE `skey` = 'min_app_version_code';


-- 8. Statistics & Diagnostics
-- View today's success/failure/blocked counts for generations
SELECT 
    `status`, 
    COUNT(*) as count, 
    AVG(`duration_ms`) as avg_duration_ms
FROM `generations`
WHERE `created_at` >= CURDATE()
GROUP BY `status`;

-- Get top templates sorted by use count
SELECT `id`, `title`, `use_count`, `credit_cost`, `is_active`
FROM `templates`
ORDER BY `use_count` DESC
LIMIT 10;

-- Provider stats: count and approximate server cost estimation (assuming baseline model cost)
SELECT 
    p.`name` as provider, 
    p.`model_name`, 
    COUNT(g.`id`) as total_generations,
    SUM(g.`status` = 'success') as successful_generations,
    SUM(g.`status` = 'failed') as failed_generations
FROM `providers` p
LEFT JOIN `generations` g ON p.`id` = g.`provider_id`
GROUP BY p.`id`;