-- Complete Database Schema for AeThex Bot
-- Run this in your Supabase SQL Editor to ensure all tables exist

-- ============ CORE TABLES ============

-- User Profiles - Global user data
CREATE TABLE IF NOT EXISTS user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username VARCHAR(255),
  xp INTEGER DEFAULT 0,
  prestige_level INTEGER DEFAULT 0,
  total_xp_earned INTEGER DEFAULT 0,
  daily_streak INTEGER DEFAULT 0,
  last_daily TIMESTAMP WITH TIME ZONE,
  title VARCHAR(255),
  bio TEXT,
  avatar_url VARCHAR(512),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Discord Links - Discord ↔ AeThex account links
CREATE TABLE IF NOT EXISTS discord_links (
  id SERIAL PRIMARY KEY,
  discord_id VARCHAR(32) UNIQUE NOT NULL,
  user_id UUID REFERENCES user_profiles(id),
  username VARCHAR(255),
  linked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Discord Verifications - Pending verification codes
CREATE TABLE IF NOT EXISTS discord_verifications (
  id SERIAL PRIMARY KEY,
  discord_id VARCHAR(32) UNIQUE NOT NULL,
  verification_code VARCHAR(32) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ SERVER CONFIG ============

-- Server Config - Per-server bot configuration
CREATE TABLE IF NOT EXISTS server_config (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  prefix VARCHAR(10) DEFAULT '!',
  verified_role_id VARCHAR(32),
  welcome_channel_id VARCHAR(32),
  welcome_message TEXT,
  goodbye_channel_id VARCHAR(32),
  goodbye_message TEXT,
  log_channel_id VARCHAR(32),
  mod_log_channel_id VARCHAR(32),
  starboard_channel_id VARCHAR(32),
  starboard_threshold INTEGER DEFAULT 3,
  level_up_channel_id VARCHAR(32),
  level_up_message TEXT,
  xp_mode VARCHAR(32) DEFAULT 'standalone',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- XP Config - Per-server XP settings
CREATE TABLE IF NOT EXISTS xp_config (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  xp_per_message INTEGER DEFAULT 15,
  xp_cooldown INTEGER DEFAULT 60,
  voice_xp_per_minute INTEGER DEFAULT 5,
  reaction_xp INTEGER DEFAULT 5,
  difficulty VARCHAR(32) DEFAULT 'normal',
  bonus_channels JSONB DEFAULT '[]',
  role_multipliers JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ XP & LEVELING ============

-- Guild User XP - Per-server XP (standalone mode)
CREATE TABLE IF NOT EXISTS guild_user_xp (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  xp INTEGER DEFAULT 0,
  level INTEGER DEFAULT 0,
  prestige INTEGER DEFAULT 0,
  messages INTEGER DEFAULT 0,
  voice_minutes INTEGER DEFAULT 0,
  last_xp_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(guild_id, user_id)
);

-- User Stats - Per-server activity statistics
CREATE TABLE IF NOT EXISTS user_stats (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32) NOT NULL,
  messages_sent INTEGER DEFAULT 0,
  reactions_given INTEGER DEFAULT 0,
  reactions_received INTEGER DEFAULT 0,
  voice_minutes INTEGER DEFAULT 0,
  commands_used INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, guild_id)
);

-- Periodic XP - Weekly/monthly XP tracking
CREATE TABLE IF NOT EXISTS periodic_xp (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32) NOT NULL,
  period_type VARCHAR(16) NOT NULL, -- 'weekly' or 'monthly'
  period_start DATE NOT NULL,
  xp_earned INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, guild_id, period_type, period_start)
);

-- Level Roles - Auto-assign roles at levels
CREATE TABLE IF NOT EXISTS level_roles (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  level INTEGER NOT NULL,
  role_id VARCHAR(32) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(guild_id, level)
);

-- ============ ACHIEVEMENTS & QUESTS ============

-- Achievements - Achievement definitions
CREATE TABLE IF NOT EXISTS achievements (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  icon VARCHAR(64),
  trigger_type VARCHAR(32) NOT NULL, -- 'level', 'xp', 'messages', 'prestige', etc.
  trigger_value INTEGER NOT NULL,
  xp_reward INTEGER DEFAULT 0,
  role_reward_id VARCHAR(32),
  hidden BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User Achievements - Earned achievements
CREATE TABLE IF NOT EXISTS user_achievements (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32) NOT NULL,
  achievement_id INTEGER REFERENCES achievements(id),
  earned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, guild_id, achievement_id)
);

-- Quests - Quest definitions
CREATE TABLE IF NOT EXISTS quests (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  quest_type VARCHAR(32) DEFAULT 'daily', -- 'daily', 'weekly'
  trigger_type VARCHAR(32) NOT NULL, -- 'messages', 'reactions', 'voice', etc.
  target_value INTEGER NOT NULL,
  xp_reward INTEGER DEFAULT 100,
  active BOOLEAN DEFAULT true,
  starts_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User Quests - Quest progress
CREATE TABLE IF NOT EXISTS user_quests (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32) NOT NULL,
  quest_id INTEGER REFERENCES quests(id),
  progress INTEGER DEFAULT 0,
  completed BOOLEAN DEFAULT false,
  completed_at TIMESTAMP WITH TIME ZONE,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, guild_id, quest_id)
);

-- ============ SHOP SYSTEM ============

-- Shop Items - Item catalog
CREATE TABLE IF NOT EXISTS shop_items (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price INTEGER NOT NULL,
  item_type VARCHAR(32) NOT NULL, -- 'role', 'badge', 'boost', 'custom'
  item_data JSONB DEFAULT '{}',
  stock INTEGER, -- null = unlimited
  enabled BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User Inventory - Purchased items
CREATE TABLE IF NOT EXISTS user_inventory (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32) NOT NULL,
  item_id INTEGER REFERENCES shop_items(id),
  quantity INTEGER DEFAULT 1,
  purchased_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE
);

-- ============ MODERATION ============

-- Warnings - User warnings
CREATE TABLE IF NOT EXISTS warnings (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  moderator_id VARCHAR(32) NOT NULL,
  reason TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Mod Actions - Moderation action log
CREATE TABLE IF NOT EXISTS mod_actions (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  moderator_id VARCHAR(32) NOT NULL,
  action VARCHAR(32) NOT NULL, -- 'warn', 'kick', 'ban', 'timeout', 'unban'
  reason TEXT,
  duration INTEGER, -- for timeouts, in seconds
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Automod Config - Auto-moderation settings
CREATE TABLE IF NOT EXISTS automod_config (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  link_filter BOOLEAN DEFAULT false,
  link_action VARCHAR(32) DEFAULT 'delete',
  spam_filter BOOLEAN DEFAULT false,
  spam_threshold INTEGER DEFAULT 5,
  spam_action VARCHAR(32) DEFAULT 'warn',
  word_filter BOOLEAN DEFAULT false,
  word_list JSONB DEFAULT '[]',
  word_action VARCHAR(32) DEFAULT 'delete',
  invite_filter BOOLEAN DEFAULT false,
  invite_action VARCHAR(32) DEFAULT 'delete',
  mention_limit INTEGER DEFAULT 10,
  mention_action VARCHAR(32) DEFAULT 'warn',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ COMMUNITY FEATURES ============

-- Giveaways - Active giveaways
CREATE TABLE IF NOT EXISTS giveaways (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  channel_id VARCHAR(32) NOT NULL,
  message_id VARCHAR(32) NOT NULL,
  host_id VARCHAR(32) NOT NULL,
  prize VARCHAR(255) NOT NULL,
  winner_count INTEGER DEFAULT 1,
  ends_at TIMESTAMP WITH TIME ZONE NOT NULL,
  ended BOOLEAN DEFAULT false,
  winners JSONB DEFAULT '[]',
  requirements JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Role Panels - Button-based role selection
CREATE TABLE IF NOT EXISTS role_panels (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  channel_id VARCHAR(32) NOT NULL,
  message_id VARCHAR(32),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  roles JSONB DEFAULT '[]', -- [{role_id, label, emoji, style}]
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Scheduled Messages - Timed announcements
CREATE TABLE IF NOT EXISTS scheduled_messages (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  channel_id VARCHAR(32) NOT NULL,
  content TEXT,
  embed JSONB,
  send_at TIMESTAMP WITH TIME ZONE NOT NULL,
  recurring VARCHAR(32), -- null, 'daily', 'weekly', 'monthly'
  sent BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Tickets - Support tickets
CREATE TABLE IF NOT EXISTS tickets (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  channel_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  status VARCHAR(32) DEFAULT 'open', -- 'open', 'closed'
  subject VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  closed_at TIMESTAMP WITH TIME ZONE
);

-- Community Posts - Cross-platform posts
CREATE TABLE IF NOT EXISTS community_posts (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  guild_id VARCHAR(32),
  content TEXT NOT NULL,
  likes INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ FEDERATION ============

-- Federation Servers - Member servers
CREATE TABLE IF NOT EXISTS federation_servers (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  guild_name VARCHAR(255),
  guild_icon VARCHAR(512),
  owner_id VARCHAR(32),
  member_count INTEGER DEFAULT 0,
  status VARCHAR(32) DEFAULT 'pending',
  tier VARCHAR(32) DEFAULT 'free',
  trust_level VARCHAR(32) DEFAULT 'bronze',
  reputation_score INTEGER DEFAULT 0,
  subscription_id VARCHAR(255),
  subscription_status VARCHAR(32),
  invite_code VARCHAR(32),
  description TEXT,
  joined_federation_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Bans - Shared ban list
CREATE TABLE IF NOT EXISTS federation_bans (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(32) NOT NULL,
  username VARCHAR(255),
  reason TEXT,
  evidence TEXT,
  severity VARCHAR(32) DEFAULT 'low',
  reported_by VARCHAR(32),
  reported_by_guild_id VARCHAR(32),
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Applications - Join applications
CREATE TABLE IF NOT EXISTS federation_applications (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  guild_name VARCHAR(255),
  guild_icon VARCHAR(512),
  admin_id VARCHAR(32),
  member_count INTEGER DEFAULT 0,
  reason TEXT,
  description TEXT,
  category VARCHAR(64),
  status VARCHAR(32) DEFAULT 'pending',
  reviewed_by VARCHAR(32),
  reviewed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Mappings - Cross-server role mappings
CREATE TABLE IF NOT EXISTS federation_mappings (
  id SERIAL PRIMARY KEY,
  role_id VARCHAR(32) UNIQUE NOT NULL,
  role_name VARCHAR(255),
  guild_id VARCHAR(32) NOT NULL,
  federation_role VARCHAR(64),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Featured - Premium featured slots
CREATE TABLE IF NOT EXISTS federation_featured (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  subscription_id VARCHAR(255),
  active BOOLEAN DEFAULT true,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Reputation - Server reputation metrics
CREATE TABLE IF NOT EXISTS federation_reputation (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  valid_reports INTEGER DEFAULT 0,
  false_positives INTEGER DEFAULT 0,
  total_reports INTEGER DEFAULT 0,
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Federation Alerts - Alert log
CREATE TABLE IF NOT EXISTS federation_alerts (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  ban_id INTEGER,
  action_taken VARCHAR(32),
  auto_action BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ MISC FEATURES ============

-- Command Cooldowns - Rate limiting
CREATE TABLE IF NOT EXISTS command_cooldowns (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  command VARCHAR(64) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  UNIQUE(guild_id, user_id, command)
);

-- Command Logs - Usage tracking
CREATE TABLE IF NOT EXISTS command_logs (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NOT NULL,
  command VARCHAR(64) NOT NULL,
  options JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Server Backups - Backup data
CREATE TABLE IF NOT EXISTS server_backups (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  name VARCHAR(100),
  description TEXT,
  backup_type VARCHAR(20) DEFAULT 'manual', -- manual, auto
  backup_data JSONB NOT NULL,
  roles_count INTEGER DEFAULT 0,
  channels_count INTEGER DEFAULT 0,
  size_bytes BIGINT DEFAULT 0,
  created_by VARCHAR(32),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_server_backups_guild ON server_backups(guild_id);

-- Backup Settings - Auto-backup config
CREATE TABLE IF NOT EXISTS backup_settings (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) UNIQUE NOT NULL,
  auto_backup BOOLEAN DEFAULT false,
  backup_interval INTEGER DEFAULT 24, -- hours
  max_backups INTEGER DEFAULT 5,
  last_backup_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Seasonal Events - Holiday/special events
CREATE TABLE IF NOT EXISTS seasonal_events (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  xp_multiplier DECIMAL(3,2) DEFAULT 1.00,
  special_rewards JSONB DEFAULT '{}',
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Discord Role Mappings - Platform role sync
CREATE TABLE IF NOT EXISTS discord_role_mappings (
  id SERIAL PRIMARY KEY,
  guild_id VARCHAR(32) NOT NULL,
  discord_role_id VARCHAR(32) NOT NULL,
  platform_role VARCHAR(64) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(guild_id, discord_role_id)
);

-- Foundation Activity - Foundation contributions
CREATE TABLE IF NOT EXISTS foundation_activity (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES user_profiles(id),
  activity_type VARCHAR(64) NOT NULL,
  amount DECIMAL(12,2),
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============ LEADERBOARD VIEWS ============

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_guild_user_xp_guild ON guild_user_xp(guild_id);
CREATE INDEX IF NOT EXISTS idx_guild_user_xp_xp ON guild_user_xp(xp DESC);
CREATE INDEX IF NOT EXISTS idx_user_stats_guild ON user_stats(guild_id);
CREATE INDEX IF NOT EXISTS idx_periodic_xp_period ON periodic_xp(period_type, period_start);
CREATE INDEX IF NOT EXISTS idx_warnings_guild ON warnings(guild_id);
CREATE INDEX IF NOT EXISTS idx_mod_actions_guild ON mod_actions(guild_id);
-- Partial indexes (run after tables exist if needed)
-- CREATE INDEX IF NOT EXISTS idx_giveaways_ends ON giveaways(ends_at) WHERE ended = false;
-- CREATE INDEX IF NOT EXISTS idx_scheduled_messages_send ON scheduled_messages(send_at) WHERE sent = false;

-- ============ RLS POLICIES ============

-- Enable RLS on all tables
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE discord_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE discord_verifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE server_config ENABLE ROW LEVEL SECURITY;
ALTER TABLE xp_config ENABLE ROW LEVEL SECURITY;
ALTER TABLE guild_user_xp ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_stats ENABLE ROW LEVEL SECURITY;
ALTER TABLE periodic_xp ENABLE ROW LEVEL SECURITY;
ALTER TABLE level_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE achievements ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_achievements ENABLE ROW LEVEL SECURITY;
ALTER TABLE quests ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_quests ENABLE ROW LEVEL SECURITY;
ALTER TABLE shop_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_inventory ENABLE ROW LEVEL SECURITY;
ALTER TABLE warnings ENABLE ROW LEVEL SECURITY;
ALTER TABLE mod_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE automod_config ENABLE ROW LEVEL SECURITY;
ALTER TABLE giveaways ENABLE ROW LEVEL SECURITY;
ALTER TABLE role_panels ENABLE ROW LEVEL SECURITY;
ALTER TABLE scheduled_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE community_posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_servers ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_bans ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_mappings ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_featured ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_reputation ENABLE ROW LEVEL SECURITY;
ALTER TABLE federation_alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE command_cooldowns ENABLE ROW LEVEL SECURITY;
ALTER TABLE command_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE server_backups ENABLE ROW LEVEL SECURITY;
ALTER TABLE backup_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE seasonal_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE discord_role_mappings ENABLE ROW LEVEL SECURITY;
ALTER TABLE foundation_activity ENABLE ROW LEVEL SECURITY;

-- Create permissive policies for service role
DO $$
DECLARE
  t text;
  tables text[] := ARRAY[
    'user_profiles', 'discord_links', 'discord_verifications', 'server_config',
    'xp_config', 'guild_user_xp', 'user_stats', 'periodic_xp', 'level_roles',
    'achievements', 'user_achievements', 'quests', 'user_quests', 'shop_items',
    'user_inventory', 'warnings', 'mod_actions', 'automod_config', 'giveaways',
    'role_panels', 'scheduled_messages', 'tickets', 'community_posts',
    'federation_servers', 'federation_bans', 'federation_applications',
    'federation_mappings', 'federation_featured', 'federation_reputation',
    'federation_alerts', 'command_cooldowns', 'command_logs', 'server_backups',
    'backup_settings', 'seasonal_events', 'discord_role_mappings', 'foundation_activity'
  ];
BEGIN
  FOREACH t IN ARRAY tables
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS "Allow all for service role" ON %I', t);
    EXECUTE format('CREATE POLICY "Allow all for service role" ON %I FOR ALL USING (true) WITH CHECK (true)', t);
  END LOOP;
END $$;

-- Grant permissions
GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO service_role;