-- Migration Script for Admin Features
-- Run this script if you already have an existing InstaVlog database
-- This will add admin features to your existing database
 
-- Step 1: Add new columns to users table
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'super_admin')),
ADD COLUMN IF NOT EXISTS is_banned BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS banned_until TIMESTAMP,
ADD COLUMN IF NOT EXISTS ban_reason TEXT;

-- Step 2: Add new columns to posts table
ALTER TABLE posts 
ADD COLUMN IF NOT EXISTS is_blocked BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS block_reason TEXT,
ADD COLUMN IF NOT EXISTS blocked_by UUID REFERENCES users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS blocked_at TIMESTAMP;

-- Step 3: Create reports table
CREATE TABLE IF NOT EXISTS reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reported_post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    reported_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    reported_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    report_type VARCHAR(50) NOT NULL CHECK (report_type IN ('spam', 'harassment', 'hate_speech', 'violence', 'nudity', 'false_information', 'other')),
    description TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'reviewing', 'resolved', 'dismissed')),
    reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMP,
    resolution_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 4: Create admin_actions table (audit log)
CREATE TABLE IF NOT EXISTS admin_actions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    admin_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    action_type VARCHAR(50) NOT NULL CHECK (action_type IN ('ban_user', 'unban_user', 'block_post', 'unblock_post', 'delete_post', 'delete_comment', 'promote_user', 'demote_user', 'resolve_report', 'dismiss_report')),
    target_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    target_post_id UUID REFERENCES posts(id) ON DELETE SET NULL,
    target_comment_id UUID REFERENCES comments(id) ON DELETE SET NULL,
    report_id UUID REFERENCES reports(id) ON DELETE SET NULL,
    reason TEXT,
    details JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 5: Create new indexes
CREATE INDEX IF NOT EXISTS idx_posts_is_blocked ON posts(is_blocked);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_is_banned ON users(is_banned);
CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status);
CREATE INDEX IF NOT EXISTS idx_reports_reporter_id ON reports(reporter_id);
CREATE INDEX IF NOT EXISTS idx_reports_reported_post_id ON reports(reported_post_id);
CREATE INDEX IF NOT EXISTS idx_reports_reported_user_id ON reports(reported_user_id);
CREATE INDEX IF NOT EXISTS idx_admin_actions_admin_id ON admin_actions(admin_id);
CREATE INDEX IF NOT EXISTS idx_admin_actions_created_at ON admin_actions(created_at DESC);

-- Step 6: Add trigger for reports table
DROP TRIGGER IF EXISTS update_reports_updated_at ON reports;
CREATE TRIGGER update_reports_updated_at 
BEFORE UPDATE ON reports 
FOR EACH ROW 
EXECUTE FUNCTION update_updated_at_column();

-- Step 7: Create first admin user (optional - update credentials as needed)
-- IMPORTANT: Replace these values with your desired admin credentials
-- Password is 'admin123' - CHANGE THIS immediately after first login!
-- To create a hashed password, use: bcrypt.hash('your_password', 10)

-- INSERT INTO users (username, email, password_hash, full_name, role)
-- VALUES (
--     'admin',
--     'admin@instavlog.com',
--     '$2a$10$rOvHfz5g5SZUPj4U6MxZHOXQkL3wD3qk9xj6gxKvCUc2YJWYBq7Vy', -- 'admin123'
--     'Administrator',
--     'super_admin'
-- )
-- ON CONFLICT (username) DO NOTHING;

-- Migration completed successfully!
-- You can now use all admin features in InstaVlog