-- ============================================
-- CLEAN SUPABASE SCHEMA FOR PREDICTION MARKET
-- ============================================

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

-- Create profiles table with all user data
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  username TEXT UNIQUE,
  avatar_url TEXT,
  balance DECIMAL(10,2) DEFAULT 1000.00,
  is_admin BOOLEAN DEFAULT FALSE,
  
  -- User preferences and settings
  first_name TEXT,
  last_name TEXT,
  phone TEXT,
  date_of_birth DATE,
  country TEXT,
  timezone TEXT DEFAULT 'UTC',
  
  -- KYC and verification
  kyc_verified BOOLEAN DEFAULT FALSE,
  kyc_verified_at TIMESTAMP WITH TIME ZONE,
  kyc_status TEXT DEFAULT 'pending' CHECK (kyc_status IN ('pending', 'approved', 'rejected')),
  
  -- Rewards and points
  total_points INTEGER DEFAULT 0,
  referral_code TEXT UNIQUE,
  
  -- Wallet and payment settings
  wallet_address TEXT,
  preferred_payment_method TEXT DEFAULT 'credit_card',
  
  -- Daily/monthly limits
  daily_deposit_limit DECIMAL(10,2) DEFAULT 5000.00,
  daily_withdrawal_limit DECIMAL(10,2) DEFAULT 5000.00,
  monthly_deposit_limit DECIMAL(10,2) DEFAULT 50000.00,
  monthly_withdrawal_limit DECIMAL(10,2) DEFAULT 50000.00,
  
  -- Account status
  account_status TEXT DEFAULT 'active' CHECK (account_status IN ('active', 'suspended', 'banned')),
  last_login TIMESTAMP WITH TIME ZONE,
  login_count INTEGER DEFAULT 0,
  
  -- Timestamps
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create categories table
CREATE TABLE categories (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  color TEXT DEFAULT '#3B82F6',
  icon TEXT DEFAULT '📊',
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create markets table
CREATE TABLE markets (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
  category TEXT NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  image_url TEXT,
  creator_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'closed', 'resolved', 'cancelled', 'refunded')),
  outcome TEXT, -- Can be any outcome name now (e.g., winning candidate)
  resolution_date TIMESTAMP WITH TIME ZONE,
  total_volume DECIMAL(15,2) DEFAULT 0.00,
  total_liquidity DECIMAL(15,2) DEFAULT 0.00,
  market_type TEXT DEFAULT 'binary' CHECK (market_type IN ('binary', 'multi_outcome')),
  min_bet_amount DECIMAL(10,2) DEFAULT 1.00,
  max_bet_amount DECIMAL(10,2) DEFAULT 10000.00,
  creator_earnings DECIMAL(15,2) DEFAULT 0.00,
  admin_fee DECIMAL(5,4) DEFAULT 0.02,
  -- Refund protection fields
  refund_threshold DECIMAL(5,4) DEFAULT 0.10, -- Minimum % of seeded amount that must be traded
  refund_deadline INTEGER DEFAULT 7, -- Days after creation when refund check occurs
  refund_checked BOOLEAN DEFAULT FALSE, -- Whether refund check has been performed
  refund_amount DECIMAL(15,2) DEFAULT 0.00, -- Amount refunded to creator
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create bets table
CREATE TABLE bets (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  market_id UUID REFERENCES markets(id) ON DELETE CASCADE,
  outcome TEXT NOT NULL, -- Can be 'yes', 'no', or any outcome name
  outcome_id UUID, -- Reference to specific outcome for multi-outcome markets
  amount DECIMAL(10,2) NOT NULL,
  price DECIMAL(4,3) NOT NULL,
  shares DECIMAL(10,2) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'matched', 'cancelled')),
  liquidity_contribution DECIMAL(10,2) DEFAULT 0.00, -- Amount contributed to liquidity
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create transactions table
CREATE TABLE transactions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  type TEXT NOT NULL CHECK (type IN ('deposit', 'withdrawal', 'bet', 'win', 'refund')),
  amount DECIMAL(10,2) NOT NULL,
  description TEXT NOT NULL,
  market_id UUID REFERENCES markets(id) ON DELETE SET NULL,
  bet_id UUID REFERENCES bets(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================
-- REWARDS AND KYC SYSTEM
-- ============================================

-- Create rewards table
CREATE TABLE rewards (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  type VARCHAR(50) NOT NULL,
  points INTEGER NOT NULL DEFAULT 0,
  description TEXT,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create achievements table
CREATE TABLE achievements (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  icon VARCHAR(50),
  points INTEGER NOT NULL DEFAULT 0,
  criteria JSONB NOT NULL,
  category VARCHAR(50) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create user_achievements table
CREATE TABLE user_achievements (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  achievement_id UUID REFERENCES achievements(id) ON DELETE CASCADE NOT NULL,
  unlocked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, achievement_id)
);

-- Create kyc_verifications table
CREATE TABLE kyc_verifications (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  status VARCHAR(20) DEFAULT 'pending' NOT NULL,
  document_type VARCHAR(50) NOT NULL,
  document_number VARCHAR(100),
  document_front_url TEXT,
  document_back_url TEXT,
  selfie_url TEXT,
  verified_at TIMESTAMP WITH TIME ZONE,
  rejected_reason TEXT,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create referrals table
CREATE TABLE referrals (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  referrer_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  referee_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  referral_code VARCHAR(20) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending' NOT NULL,
  reward_points INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(referrer_id, referee_id)
);

-- ============================================
-- WALLET SYSTEM
-- ============================================

-- Create payment_methods table
CREATE TABLE payment_methods (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  type VARCHAR(50) NOT NULL,
  provider VARCHAR(50) NOT NULL,
  provider_account_id VARCHAR(100),
  last_four VARCHAR(4),
  brand VARCHAR(50),
  is_default BOOLEAN DEFAULT FALSE,
  is_verified BOOLEAN DEFAULT FALSE,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create wallet_transactions table
CREATE TABLE wallet_transactions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  type VARCHAR(50) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending' NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD' NOT NULL,
  payment_method_id UUID REFERENCES payment_methods(id) ON DELETE SET NULL,
  external_transaction_id VARCHAR(100),
  description TEXT,
  fee_amount DECIMAL(10,2) DEFAULT 0.00,
  net_amount DECIMAL(10,2) NOT NULL,
  metadata JSONB,
  processed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create withdrawal_requests table
CREATE TABLE withdrawal_requests (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method_id UUID REFERENCES payment_methods(id) ON DELETE CASCADE NOT NULL,
  status VARCHAR(20) DEFAULT 'pending' NOT NULL,
  admin_notes TEXT,
  rejection_reason TEXT,
  processed_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  processed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create daily_limits table
CREATE TABLE daily_limits (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  date DATE NOT NULL,
  deposits_count INTEGER DEFAULT 0,
  deposits_amount DECIMAL(10,2) DEFAULT 0.00,
  withdrawals_count INTEGER DEFAULT 0,
  withdrawals_amount DECIMAL(10,2) DEFAULT 0.00,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, date)
);

-- ============================================
-- INDEXES
-- ============================================

-- Core table indexes
CREATE INDEX idx_markets_status ON markets(status);
CREATE INDEX idx_markets_category ON markets(category);
CREATE INDEX idx_markets_end_date ON markets(end_date);
CREATE INDEX idx_bets_user_id ON bets(user_id);
CREATE INDEX idx_bets_market_id ON bets(market_id);
CREATE INDEX idx_bets_status ON bets(status);
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_type ON transactions(type);

-- Rewards and KYC indexes
CREATE INDEX idx_rewards_user_id ON rewards(user_id);
CREATE INDEX idx_rewards_type ON rewards(type);
CREATE INDEX idx_user_achievements_user_id ON user_achievements(user_id);
CREATE INDEX idx_kyc_user_id ON kyc_verifications(user_id);
CREATE INDEX idx_kyc_status ON kyc_verifications(status);
CREATE INDEX idx_referrals_referrer_id ON referrals(referrer_id);
CREATE INDEX idx_referrals_referee_id ON referrals(referee_id);

-- Wallet indexes
CREATE INDEX idx_payment_methods_user_id ON payment_methods(user_id);
CREATE INDEX idx_payment_methods_type ON payment_methods(type);
CREATE INDEX idx_wallet_transactions_user_id ON wallet_transactions(user_id);
CREATE INDEX idx_wallet_transactions_type ON wallet_transactions(type);
CREATE INDEX idx_wallet_transactions_status ON wallet_transactions(status);
CREATE INDEX idx_withdrawal_requests_user_id ON withdrawal_requests(user_id);
CREATE INDEX idx_withdrawal_requests_status ON withdrawal_requests(status);
CREATE INDEX idx_daily_limits_user_date ON daily_limits(user_id, date);

-- ============================================
-- FUNCTIONS
-- ============================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Function to generate unique referral code
CREATE OR REPLACE FUNCTION generate_referral_code()
RETURNS TEXT AS $$
DECLARE
  code TEXT;
  exists BOOLEAN;
BEGIN
  LOOP
    code := UPPER(SUBSTRING(MD5(RANDOM()::TEXT), 1, 8));
    SELECT EXISTS(SELECT 1 FROM profiles WHERE referral_code = code) INTO exists;
    IF NOT exists THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN code;
END;
$$ LANGUAGE plpgsql;

-- Function to handle new user registration
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  user_referral_code TEXT;
  username_value TEXT;
BEGIN
  -- Generate unique referral code
  user_referral_code := generate_referral_code();
  
  -- Get username from metadata or generate one
  username_value := COALESCE(NEW.raw_user_meta_data->>'username', 'user_' || substr(NEW.id::text, 1, 8));
  
  -- Insert user profile with all default values
  INSERT INTO profiles (
    id, 
    email, 
    username, 
    balance, 
    is_admin,
    referral_code,
    total_points,
    daily_deposit_limit,
    daily_withdrawal_limit,
    monthly_deposit_limit,
    monthly_withdrawal_limit,
    account_status,
    last_login,
    login_count,
    created_at,
    updated_at
  )
  VALUES (
    NEW.id, 
    NEW.email, 
    username_value, 
    1000.00, 
    FALSE,
    user_referral_code,
    100, -- Welcome bonus points
    5000.00,
    5000.00,
    50000.00,
    50000.00,
    'active',
    NOW(),
    1,
    NOW(),
    NOW()
  );
  
  RETURN NEW;
EXCEPTION
  WHEN unique_violation THEN
    -- If username already exists, try with a different suffix
    username_value := 'user_' || substr(NEW.id::text, 1, 8) || '_' || extract(epoch from now())::text;
    
    INSERT INTO profiles (
      id, 
      email, 
      username, 
      balance, 
      is_admin,
      referral_code,
      total_points,
      daily_deposit_limit,
      daily_withdrawal_limit,
      monthly_deposit_limit,
      monthly_withdrawal_limit,
      account_status,
      last_login,
      login_count,
      created_at,
      updated_at
    )
    VALUES (
      NEW.id, 
      NEW.email, 
      username_value, 
      1000.00, 
      FALSE,
      user_referral_code,
      100,
      5000.00,
      5000.00,
      50000.00,
      50000.00,
      'active',
      NOW(),
      1,
      NOW(),
      NOW()
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to update user login information
CREATE OR REPLACE FUNCTION update_user_login(user_id UUID)
RETURNS VOID AS $$
BEGIN
  UPDATE profiles 
  SET 
    last_login = NOW(),
    login_count = login_count + 1,
    updated_at = NOW()
  WHERE id = user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to award points
CREATE OR REPLACE FUNCTION award_points(user_uuid UUID, points_amount INTEGER, reward_type VARCHAR(50), description TEXT DEFAULT NULL)
RETURNS VOID AS $$
BEGIN
  -- Insert reward record
  INSERT INTO rewards (user_id, type, points, description)
  VALUES (user_uuid, reward_type, points_amount, description);
  
  -- Update user's total points
  UPDATE profiles 
  SET total_points = total_points + points_amount
  WHERE id = user_uuid;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger for new user registration
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Triggers for updated_at
CREATE TRIGGER update_profiles_updated_at BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_markets_updated_at BEFORE UPDATE ON markets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_bets_updated_at BEFORE UPDATE ON bets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kyc_verifications_updated_at BEFORE UPDATE ON kyc_verifications
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_payment_methods_updated_at BEFORE UPDATE ON payment_methods
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_wallet_transactions_updated_at BEFORE UPDATE ON wallet_transactions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_withdrawal_requests_updated_at BEFORE UPDATE ON withdrawal_requests
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_daily_limits_updated_at BEFORE UPDATE ON daily_limits
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================

-- Enable RLS on all tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE markets ENABLE ROW LEVEL SECURITY;
ALTER TABLE bets ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE rewards ENABLE ROW LEVEL SECURITY;
ALTER TABLE achievements ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_achievements ENABLE ROW LEVEL SECURITY;
ALTER TABLE kyc_verifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE referrals ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_methods ENABLE ROW LEVEL SECURITY;
ALTER TABLE wallet_transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE withdrawal_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE daily_limits ENABLE ROW LEVEL SECURITY;

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

-- Profiles policies
CREATE POLICY "Users can view all profiles" ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can update own profile" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Categories policies
CREATE POLICY "Users can view all categories" ON categories FOR SELECT USING (true);
CREATE POLICY "Admins can manage categories" ON categories FOR ALL USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- Markets policies
CREATE POLICY "Users can view all markets" ON markets FOR SELECT USING (true);
CREATE POLICY "Admins can insert markets" ON markets FOR INSERT WITH CHECK (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);
CREATE POLICY "Admins can update markets" ON markets FOR UPDATE USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- Bets policies
CREATE POLICY "Users can view all bets" ON bets FOR SELECT USING (true);
CREATE POLICY "Users can insert own bets" ON bets FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own bets" ON bets FOR UPDATE USING (auth.uid() = user_id);

-- Transactions policies
CREATE POLICY "Users can view own transactions" ON transactions FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own transactions" ON transactions FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Rewards policies
CREATE POLICY "Users can view their own rewards" ON rewards FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "System can create rewards" ON rewards FOR INSERT WITH CHECK (true);

-- Achievements policies
CREATE POLICY "Achievements are viewable by everyone" ON achievements FOR SELECT USING (true);
CREATE POLICY "Admins can manage achievements" ON achievements FOR ALL USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- User achievements policies
CREATE POLICY "Users can view their own achievements" ON user_achievements FOR SELECT USING (auth.uid() = user_id);


-- KYC policies
CREATE POLICY "Users can view their own KYC" ON kyc_verifications FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own KYC" ON kyc_verifications FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own KYC" ON kyc_verifications FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Admins can view all KYC" ON kyc_verifications FOR SELECT USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- Referrals policies
CREATE POLICY "Users can view their own referrals" ON referrals FOR SELECT USING (auth.uid() = referrer_id OR auth.uid() = referee_id);
CREATE POLICY "System can create referrals" ON referrals FOR INSERT WITH CHECK (true);

-- Payment methods policies
CREATE POLICY "Users can view their own payment methods" ON payment_methods FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own payment methods" ON payment_methods FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own payment methods" ON payment_methods FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own payment methods" ON payment_methods FOR DELETE USING (auth.uid() = user_id);

-- Wallet transactions policies
CREATE POLICY "Users can view their own wallet transactions" ON wallet_transactions FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "System can create wallet transactions" ON wallet_transactions FOR INSERT WITH CHECK (true);
CREATE POLICY "Admins can update wallet transactions" ON wallet_transactions FOR UPDATE USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- Withdrawal requests policies
CREATE POLICY "Users can view their own withdrawal requests" ON withdrawal_requests FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own withdrawal requests" ON withdrawal_requests FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Admins can manage withdrawal requests" ON withdrawal_requests FOR ALL USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND is_admin = true)
);

-- Daily limits policies
CREATE POLICY "Users can view their own daily limits" ON daily_limits FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "System can manage daily limits" ON daily_limits FOR ALL WITH CHECK (true);

-- ============================================
-- SAMPLE DATA
-- ============================================

-- Insert sample categories
INSERT INTO categories (name, slug, description, color, icon, sort_order) VALUES
('Politics', 'politics', 'Political events, elections, and government decisions', '#DC2626', '🏛️', 1),
('Economics', 'economics', 'Economic indicators, market trends, and financial events', '#059669', '📈', 2),
('Technology', 'technology', 'Tech developments, AI, and digital innovations', '#7C3AED', '💻', 3),
('Sports', 'sports', 'Sports events, tournaments, and athletic competitions', '#EA580C', '⚽', 4),
('Entertainment', 'entertainment', 'Movies, TV shows, music, and celebrity events', '#DB2777', '🎬', 5),
('Cryptocurrency', 'crypto', 'Bitcoin, Ethereum, and other digital currencies', '#F59E0B', '₿', 6),
('Weather', 'weather', 'Weather predictions and natural events', '#0EA5E9', '🌤️', 7),
('Science', 'science', 'Scientific discoveries and research breakthroughs', '#8B5CF6', '🔬', 8)
ON CONFLICT (slug) DO NOTHING;

-- Insert default achievements
INSERT INTO achievements (name, description, icon, points, criteria, category) VALUES
('First Bet', 'Place your first bet', '🎯', 100, '{"type": "total_bets", "count": 1}', 'betting'),
('Lucky Streak', 'Win 5 bets in a row', '🔥', 500, '{"type": "winning_bets", "count": 5}', 'betting'),
('High Roller', 'Place bets totaling $10,000', '💰', 1000, '{"type": "total_volume", "amount": 10000}', 'milestone'),
('Market Master', 'Trade on 50 different markets', '📈', 2000, '{"type": "total_bets", "count": 50}', 'betting'),
('Big Winner', 'Win $5,000 total', '🏆', 1500, '{"type": "total_winnings", "amount": 5000}', 'milestone'),
('Sharp Shooter', 'Achieve 80% win rate (min 10 bets)', '🎯', 3000, '{"type": "win_rate", "rate": 0.8}', 'betting'),
('Point Collector', 'Earn 10,000 points', '⭐', 1000, '{"type": "total_points", "points": 10000}', 'milestone'),
('KYC Verified', 'Complete identity verification', '✅', 500, '{"type": "kyc_verified"}', 'social'),
('Referral Master', 'Refer 10 friends', '👥', 2000, '{"type": "referrals", "count": 10}', 'social'),
('First Deposit', 'Make your first deposit', '💳', 200, '{"type": "first_deposit"}', 'milestone'),
('Big Depositor', 'Deposit $1,000 or more', '🏦', 500, '{"type": "large_deposit", "amount": 1000}', 'milestone')
ON CONFLICT DO NOTHING;

-- ============================================
-- MULTI-OUTCOME AND LIQUIDITY TABLES
-- ============================================

-- Create market_outcomes table for multi-outcome markets
CREATE TABLE market_outcomes (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  market_id UUID REFERENCES markets(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(4,3) DEFAULT 0.0, -- Current price (0-1)
  pool DECIMAL(15,2) DEFAULT 0.00, -- Total liquidity in this outcome
  volume DECIMAL(15,2) DEFAULT 0.00, -- Total volume traded
  is_resolved BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(market_id, name)
);

-- Create liquidity_providers table to track who provides liquidity
CREATE TABLE liquidity_providers (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  market_id UUID REFERENCES markets(id) ON DELETE CASCADE,
  outcome_id UUID REFERENCES market_outcomes(id) ON DELETE SET NULL,
  amount DECIMAL(15,2) NOT NULL,
  shares DECIMAL(15,2) NOT NULL, -- Shares received for providing liquidity
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create watchlist table for user bookmarks
CREATE TABLE watchlist (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  market_id UUID REFERENCES markets(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, market_id) -- Prevent duplicate bookmarks
);

-- Create notifications table for user notifications
CREATE TABLE notifications (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  type TEXT DEFAULT 'info' CHECK (type IN ('info', 'success', 'warning', 'error')),
  is_read BOOLEAN DEFAULT FALSE,
  data JSONB, -- Additional data for the notification
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create leaderboard view
CREATE OR REPLACE VIEW leaderboard AS
SELECT 
  p.id as user_id,
  p.username,
  p.total_points,
  COALESCE(bet_stats.win_rate, 0) as win_rate,
  COALESCE(bet_stats.total_bets, 0) as total_bets,
  COALESCE(bet_stats.total_profit, 0) as total_profit,
  COALESCE(bet_stats.total_volume, 0) as total_volume
FROM profiles p
LEFT JOIN (
  SELECT 
    b.user_id,
    COUNT(*) as total_bets,
    ROUND(
      (COUNT(CASE WHEN b.status = 'won' THEN 1 END) * 100.0) / NULLIF(COUNT(*), 0)
    ) as win_rate,
    SUM(CASE 
      WHEN b.status = 'won' THEN COALESCE(b.potential_payout, b.amount * 2)
      WHEN b.status = 'lost' THEN -b.amount
      ELSE 0 
    END) as total_profit,
    SUM(b.amount) as total_volume
  FROM bets b
  WHERE b.status IN ('won', 'lost', 'matched')
  GROUP BY b.user_id
) bet_stats ON p.id = bet_stats.user_id
WHERE p.account_status = 'active'
ORDER BY p.total_points DESC, total_profit DESC;

-- Function to check and process market refunds
CREATE OR REPLACE FUNCTION check_market_refunds()
RETURNS VOID AS $$
DECLARE
    market_record RECORD;
    trading_threshold DECIMAL(15,2);
    total_traded DECIMAL(15,2);
    refund_amount DECIMAL(15,2);
BEGIN
    -- Find markets that are past their refund deadline and haven't been checked
    FOR market_record IN 
        SELECT * FROM markets 
        WHERE status = 'active' 
        AND refund_checked = FALSE 
        AND created_at + INTERVAL '1 day' * refund_deadline < NOW()
    LOOP
        -- Calculate the trading threshold amount
        trading_threshold := market_record.seeded_volume * market_record.refund_threshold;
        
        -- Get total volume traded on this market
        SELECT COALESCE(SUM(amount), 0) INTO total_traded
        FROM bets 
        WHERE market_id = market_record.id 
        AND status = 'matched';
        
        -- If trading volume is below threshold, process refund
        IF total_traded < trading_threshold THEN
            refund_amount := market_record.seeded_volume + market_record.total_liquidity;
            
            -- Update market status and refund info
            UPDATE markets 
            SET 
                status = 'refunded',
                refund_checked = TRUE,
                refund_amount = refund_amount,
                updated_at = NOW()
            WHERE id = market_record.id;
            
            -- Refund the creator
            UPDATE profiles 
            SET 
                balance = balance + refund_amount,
                updated_at = NOW()
            WHERE id = market_record.creator_id;
            
            -- Insert refund notification
            INSERT INTO notifications (user_id, title, message, type, data)
            VALUES (
                market_record.creator_id,
                'Market Refunded',
                'Your market "' || market_record.title || '" has been refunded due to insufficient trading activity.',
                'success',
                jsonb_build_object(
                    'market_id', market_record.id,
                    'refund_amount', refund_amount,
                    'trading_volume', total_traded,
                    'threshold', trading_threshold
                )
            );
        ELSE
            -- Mark as checked but no refund needed
            UPDATE markets 
            SET 
                refund_checked = TRUE,
                updated_at = NOW()
            WHERE id = market_record.id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- COMPLETION MESSAGE
-- ============================================

-- This schema is now complete and ready to use!
-- All tables, functions, triggers, and policies have been created.
-- New features: Multi-outcome markets, liquidity pools, and enhanced betting system.
-- Users can now sign up and their profiles will be automatically created.
