-- Reset Database Script
-- This script clears ALL data from the database EXCEPT admin users
-- Run this with caution - data cannot be recovered!

-- Start transaction for safety
BEGIN;

-- Step 1: Store admin user IDs before clearing
CREATE TEMP TABLE temp_admin_users AS
SELECT * FROM users WHERE role = 'admin';

CREATE TEMP TABLE temp_admin_settings AS
SELECT us.* FROM user_settings us
JOIN users u ON us.user_id = u.id
WHERE u.role = 'admin';

CREATE TEMP TABLE temp_admin_subscriptions AS
SELECT sub.* FROM user_subscriptions sub
JOIN users u ON sub.user_id = u.id
WHERE u.role = 'admin';

-- Step 2: Delete all data from tables (in correct order to respect foreign keys)

-- Delete from tables with no dependents first, then work up
TRUNCATE TABLE admin_logs CASCADE;
TRUNCATE TABLE ticket_messages CASCADE;
TRUNCATE TABLE support_tickets CASCADE;
TRUNCATE TABLE push_tokens CASCADE;
TRUNCATE TABLE notifications CASCADE;
TRUNCATE TABLE content_reports CASCADE;
TRUNCATE TABLE generated_reports CASCADE;

-- User activity tables
TRUNCATE TABLE listening_history CASCADE;
TRUNCATE TABLE downloaded_tracks CASCADE;
TRUNCATE TABLE liked_tracks CASCADE;
TRUNCATE TABLE liked_albums CASCADE;
TRUNCATE TABLE user_sessions CASCADE;

-- Playlist related
TRUNCATE TABLE playlist_followers CASCADE;
TRUNCATE TABLE playlist_tracks CASCADE;
TRUNCATE TABLE playlists CASCADE;

-- Artist related
TRUNCATE TABLE artist_followers CASCADE;
TRUNCATE TABLE artist_campaigns CASCADE;
TRUNCATE TABLE artist_earnings CASCADE;
TRUNCATE TABLE artist_payouts CASCADE;
TRUNCATE TABLE scheduled_releases CASCADE;

-- Track related
TRUNCATE TABLE stream_counts CASCADE;
TRUNCATE TABLE track_artists CASCADE;
TRUNCATE TABLE track_genres CASCADE;
TRUNCATE TABLE tracks CASCADE;

-- Album related
TRUNCATE TABLE album_genres CASCADE;
TRUNCATE TABLE albums CASCADE;

-- Artists
TRUNCATE TABLE artists CASCADE;

-- Payment related
TRUNCATE TABLE payment_transactions CASCADE;
TRUNCATE TABLE user_subscriptions CASCADE;
TRUNCATE TABLE user_settings CASCADE;

-- Users (this will clear all users)
TRUNCATE TABLE users CASCADE;

-- Reference data (optional - comment out if you want to keep these)
-- TRUNCATE TABLE subscription_plans CASCADE;
-- TRUNCATE TABLE genres CASCADE;
-- TRUNCATE TABLE categories CASCADE;
-- TRUNCATE TABLE royalty_rates CASCADE;
-- TRUNCATE TABLE promotions CASCADE;
-- TRUNCATE TABLE api_keys CASCADE;
-- TRUNCATE TABLE system_settings CASCADE;

-- Step 3: Restore admin users from temp tables
INSERT INTO users SELECT * FROM temp_admin_users;
INSERT INTO user_settings SELECT * FROM temp_admin_settings;
INSERT INTO user_subscriptions SELECT * FROM temp_admin_subscriptions;

-- Step 4: Clean up temp tables
DROP TABLE temp_admin_users;
DROP TABLE temp_admin_settings;
DROP TABLE temp_admin_subscriptions;

-- Commit transaction
COMMIT;

-- Verify admin users are preserved
SELECT id, name, email, role FROM users WHERE role = 'admin';