-- Add is_active field to profiles for provider status management
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

-- Create view for lead statistics with purchase counts and revenue
CREATE OR REPLACE VIEW admin_lead_stats AS
SELECT 
  l.id,
  l.destination,
  l.country,
  l.start_date,
  l.end_date,
  l.number_of_travelers,
  l.interests,
  l.email,
  l.whatsapp,
  l.planned_activities,
  l.timeline_snapshot,
  l.created_at,
  COALESCE(COUNT(DISTINCT lp.provider_id), 0) as providers_unlocked,
  COALESCE(SUM(lp.credits_spent), 0) as total_revenue,
  CASE 
    WHEN COUNT(lp.id) = 0 THEN 'new'
    WHEN l.end_date < CURRENT_DATE THEN 'expired'
    ELSE 'sold'
  END as status
FROM leads l
LEFT JOIN lead_purchases lp ON l.id = lp.lead_id
GROUP BY l.id;

-- Create view for provider statistics
CREATE OR REPLACE VIEW admin_provider_stats AS
SELECT 
  p.id,
  p.username,
  p.full_name,
  p.role,
  p.is_active,
  p.created_at,
  ps.business_name,
  ps.destinations,
  ps.activity_categories,
  COALESCE(pw.credit_balance, 0) as credit_balance,
  COALESCE(COUNT(DISTINCT lp.lead_id), 0) as purchased_leads_count,
  COALESCE(SUM(lp.credits_spent), 0) as total_spend
FROM profiles p
LEFT JOIN provider_services ps ON p.id = ps.provider_id
LEFT JOIN provider_wallets pw ON p.id = pw.provider_id
LEFT JOIN lead_purchases lp ON p.id = lp.provider_id
WHERE p.role = 'provider'
GROUP BY p.id, ps.business_name, ps.destinations, ps.activity_categories, pw.credit_balance;

-- Function to get overall analytics
CREATE OR REPLACE FUNCTION get_admin_analytics()
RETURNS JSON AS $$
DECLARE
  result JSON;
BEGIN
  SELECT json_build_object(
    'total_leads', (SELECT COUNT(*) FROM leads),
    'total_leads_sold', (SELECT COUNT(DISTINCT lead_id) FROM lead_purchases),
    'total_revenue', (SELECT COALESCE(SUM(credits_spent), 0) FROM lead_purchases),
    'total_providers', (SELECT COUNT(*) FROM profiles WHERE role = 'provider'),
    'active_providers', (SELECT COUNT(*) FROM profiles WHERE role = 'provider' AND is_active = true),
    'top_destinations', (
      SELECT COALESCE(json_agg(dest_stats), '[]'::json)
      FROM (
        SELECT 
          destination,
          country,
          COUNT(*) as lead_count,
          COALESCE(SUM(revenue), 0) as total_revenue
        FROM (
          SELECT 
            l.destination,
            l.country,
            COALESCE(SUM(lp.credits_spent), 0) as revenue
          FROM leads l
          LEFT JOIN lead_purchases lp ON l.id = lp.lead_id
          GROUP BY l.id, l.destination, l.country
        ) sub
        GROUP BY destination, country
        ORDER BY lead_count DESC
        LIMIT 10
      ) dest_stats
    ),
    'top_providers', (
      SELECT COALESCE(json_agg(prov_stats), '[]'::json)
      FROM (
        SELECT 
          p.id,
          ps.business_name,
          p.username,
          COUNT(DISTINCT lp.lead_id) as purchased_leads,
          COALESCE(SUM(lp.credits_spent), 0) as total_spend
        FROM profiles p
        LEFT JOIN provider_services ps ON p.id = ps.provider_id
        LEFT JOIN lead_purchases lp ON p.id = lp.provider_id
        WHERE p.role = 'provider'
        GROUP BY p.id, ps.business_name, p.username
        HAVING COUNT(lp.id) > 0
        ORDER BY total_spend DESC
        LIMIT 10
      ) prov_stats
    )
  ) INTO result;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to manually adjust provider credits (admin only)
CREATE OR REPLACE FUNCTION admin_adjust_credits(
  p_provider_id UUID,
  p_amount INTEGER,
  p_reason TEXT,
  p_admin_id UUID
)
RETURNS JSON AS $$
DECLARE
  v_wallet_id UUID;
  v_new_balance INTEGER;
  v_transaction_id UUID;
BEGIN
  -- Verify admin role
  IF NOT EXISTS (SELECT 1 FROM profiles WHERE id = p_admin_id AND role = 'admin') THEN
    RAISE EXCEPTION 'Unauthorized: Admin access required';
  END IF;

  -- Get wallet
  SELECT id, credit_balance INTO v_wallet_id, v_new_balance
  FROM provider_wallets
  WHERE provider_id = p_provider_id
  FOR UPDATE;

  IF v_wallet_id IS NULL THEN
    RAISE EXCEPTION 'Provider wallet not found';
  END IF;

  -- Calculate new balance
  v_new_balance := v_new_balance + p_amount;

  IF v_new_balance < 0 THEN
    RAISE EXCEPTION 'Insufficient balance for deduction';
  END IF;

  -- Update wallet
  UPDATE provider_wallets
  SET 
    credit_balance = v_new_balance,
    updated_at = NOW()
  WHERE id = v_wallet_id;

  -- Record transaction
  INSERT INTO credit_transactions (
    wallet_id,
    transaction_type,
    amount,
    description,
    balance_after
  ) VALUES (
    v_wallet_id,
    CASE WHEN p_amount > 0 THEN 'admin_credit' ELSE 'admin_debit' END,
    ABS(p_amount),
    p_reason || ' (Admin: ' || p_admin_id::TEXT || ')',
    v_new_balance
  ) RETURNING id INTO v_transaction_id;

  RETURN json_build_object(
    'success', true,
    'new_balance', v_new_balance,
    'transaction_id', v_transaction_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to toggle provider active status
CREATE OR REPLACE FUNCTION admin_toggle_provider_status(
  p_provider_id UUID,
  p_is_active BOOLEAN,
  p_admin_id UUID
)
RETURNS JSON AS $$
BEGIN
  -- Verify admin role
  IF NOT EXISTS (SELECT 1 FROM profiles WHERE id = p_admin_id AND role = 'admin') THEN
    RAISE EXCEPTION 'Unauthorized: Admin access required';
  END IF;

  UPDATE profiles
  SET 
    is_active = p_is_active,
    updated_at = NOW()
  WHERE id = p_provider_id AND role = 'provider';

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Provider not found';
  END IF;

  RETURN json_build_object(
    'success', true,
    'provider_id', p_provider_id,
    'is_active', p_is_active
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant permissions to authenticated users (admin check is in functions)
GRANT SELECT ON admin_lead_stats TO authenticated;
GRANT SELECT ON admin_provider_stats TO authenticated;
GRANT EXECUTE ON FUNCTION get_admin_analytics() TO authenticated;
GRANT EXECUTE ON FUNCTION admin_adjust_credits(UUID, INTEGER, TEXT, UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION admin_toggle_provider_status(UUID, BOOLEAN, UUID) TO authenticated;