-- =========== Roles ===========
CREATE TYPE public.app_role AS ENUM ('admin', 'manager', 'client');

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role)
$$;

CREATE OR REPLACE FUNCTION public.is_staff(_user_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role IN ('admin','manager'))
$$;

CREATE POLICY "users read own roles" ON public.user_roles FOR SELECT TO authenticated USING (user_id = auth.uid());
CREATE POLICY "staff read all roles" ON public.user_roles FOR SELECT TO authenticated USING (public.is_staff(auth.uid()));

-- =========== Profiles ===========
CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username text UNIQUE,
  display_name text,
  email text,
  telegram_handle text,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "own profile" ON public.profiles FOR SELECT TO authenticated USING (id = auth.uid());
CREATE POLICY "own profile update" ON public.profiles FOR UPDATE TO authenticated USING (id = auth.uid());
CREATE POLICY "staff all profiles" ON public.profiles FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, email, display_name, username)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email,'@',1)),
    COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email,'@',1))
  )
  ON CONFLICT (id) DO NOTHING;
  -- default to client role
  INSERT INTO public.user_roles (user_id, role)
  VALUES (NEW.id, 'client')
  ON CONFLICT DO NOTHING;
  RETURN NEW;
END;
$$;
CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

CREATE OR REPLACE FUNCTION public.touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END $$;

CREATE TRIGGER trg_profiles_touch BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- =========== Currencies ===========
CREATE TABLE public.currencies (
  code text PRIMARY KEY,
  name text NOT NULL,
  symbol text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL DEFAULT 100
);
GRANT SELECT ON public.currencies TO anon, authenticated;
GRANT ALL ON public.currencies TO service_role;
ALTER TABLE public.currencies ENABLE ROW LEVEL SECURITY;
CREATE POLICY "currencies public read" ON public.currencies FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "staff manage currencies" ON public.currencies FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

INSERT INTO public.currencies (code, name, symbol, sort_order) VALUES
  ('USD','US Dollar','$',1),('EUR','Euro','€',2),('GBP','British Pound','£',3),
  ('BDT','Bangladeshi Taka','৳',4),('INR','Indian Rupee','₹',5),
  ('SAR','Saudi Riyal','﷼',6),('AED','UAE Dirham','د.إ',7),
  ('MYR','Malaysian Ringgit','RM',8),('THB','Thai Baht','฿',9),
  ('IDR','Indonesian Rupiah','Rp',10),('PHP','Philippine Peso','₱',11),
  ('VND','Vietnamese Dong','₫',12),('SGD','Singapore Dollar','S$',13),
  ('HKD','Hong Kong Dollar','HK$',14),('JPY','Japanese Yen','¥',15),
  ('KRW','South Korean Won','₩',16),('CNY','Chinese Yuan','¥',17),
  ('AUD','Australian Dollar','A$',18),('CAD','Canadian Dollar','C$',19),
  ('CHF','Swiss Franc','CHF',20),('NZD','New Zealand Dollar','NZ$',21),
  ('ZAR','South African Rand','R',22),('BRL','Brazilian Real','R$',23),
  ('MXN','Mexican Peso','$',24),('ARS','Argentine Peso','$',25),
  ('TRY','Turkish Lira','₺',26),('RUB','Russian Ruble','₽',27),
  ('PLN','Polish Zloty','zł',28),('NOK','Norwegian Krone','kr',29),
  ('SEK','Swedish Krona','kr',30),('DKK','Danish Krone','kr',31),
  ('PKR','Pakistani Rupee','₨',32),('LKR','Sri Lankan Rupee','Rs',33),
  ('NPR','Nepalese Rupee','Rs',34),('KWD','Kuwaiti Dinar','د.ك',35),
  ('QAR','Qatari Riyal','﷼',36),('OMR','Omani Rial','﷼',37),
  ('BHD','Bahraini Dinar','.د.ب',38),('EGP','Egyptian Pound','£',39),
  ('NGN','Nigerian Naira','₦',40),('KES','Kenyan Shilling','KSh',41);

-- =========== Wallets ===========
CREATE TABLE public.wallets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  currency_code text NOT NULL REFERENCES public.currencies(code),
  balance numeric(20,4) NOT NULL DEFAULT 0,
  is_primary boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (client_id, currency_code)
);
GRANT SELECT ON public.wallets TO authenticated;
GRANT ALL ON public.wallets TO service_role;
ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;
CREATE POLICY "client own wallets" ON public.wallets FOR SELECT TO authenticated USING (client_id = auth.uid());
CREATE POLICY "staff all wallets" ON public.wallets FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE TRIGGER trg_wallets_touch BEFORE UPDATE ON public.wallets FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- =========== API Keys ===========
CREATE TABLE public.api_keys (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  label text NOT NULL DEFAULT 'Default',
  key_prefix text NOT NULL,
  key_hash text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  last_used_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz
);
GRANT SELECT ON public.api_keys TO authenticated;
GRANT ALL ON public.api_keys TO service_role;
ALTER TABLE public.api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "client own keys" ON public.api_keys FOR SELECT TO authenticated USING (client_id = auth.uid());
CREATE POLICY "staff all keys" ON public.api_keys FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

-- =========== Transactions ===========
CREATE TABLE public.transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type text NOT NULL CHECK (type IN ('deposit','withdraw','adjustment','bet','win','fee')),
  currency_code text NOT NULL REFERENCES public.currencies(code),
  amount numeric(20,4) NOT NULL,
  balance_after numeric(20,4),
  note text,
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.transactions TO authenticated;
GRANT ALL ON public.transactions TO service_role;
ALTER TABLE public.transactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "client own tx" ON public.transactions FOR SELECT TO authenticated USING (client_id = auth.uid());
CREATE POLICY "staff all tx" ON public.transactions FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

-- =========== Game Providers ===========
CREATE TABLE public.game_providers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text NOT NULL UNIQUE,
  category text,
  logo_url text,
  games_count int NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'active',
  sort_order int NOT NULL DEFAULT 100,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.game_providers TO anon, authenticated;
GRANT ALL ON public.game_providers TO service_role;
ALTER TABLE public.game_providers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "providers public read" ON public.game_providers FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "staff manage providers" ON public.game_providers FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

INSERT INTO public.game_providers (name, slug, category, games_count, sort_order) VALUES
  ('Pragmatic Play','pragmatic-play','Slots & Live',450,1),
  ('Evolution','evolution','Live Casino',380,2),
  ('PG Soft','pg-soft','Slots',220,3),
  ('JILI','jili','Slots & Fishing',180,4),
  ('Spribe','spribe','Crash & Instant',45,5),
  ('Hacksaw Gaming','hacksaw','Slots',120,6),
  ('Play''n GO','playngo','Slots',300,7),
  ('NetEnt','netent','Slots',280,8),
  ('Microgaming','microgaming','Slots',520,9),
  ('Habanero','habanero','Slots',180,10),
  ('CQ9','cq9','Slots & Live',200,11),
  ('Booongo','booongo','Slots',95,12);

-- =========== Support Tickets ===========
CREATE TABLE public.support_tickets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  subject text NOT NULL,
  status text NOT NULL DEFAULT 'open',
  priority text NOT NULL DEFAULT 'normal',
  assigned_to uuid REFERENCES auth.users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.support_tickets TO authenticated;
GRANT ALL ON public.support_tickets TO service_role;
ALTER TABLE public.support_tickets ENABLE ROW LEVEL SECURITY;
CREATE POLICY "client own tickets" ON public.support_tickets FOR SELECT TO authenticated USING (client_id = auth.uid());
CREATE POLICY "client create tickets" ON public.support_tickets FOR INSERT TO authenticated WITH CHECK (client_id = auth.uid());
CREATE POLICY "staff all tickets" ON public.support_tickets FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE TRIGGER trg_tickets_touch BEFORE UPDATE ON public.support_tickets FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE public.support_ticket_messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id uuid NOT NULL REFERENCES public.support_tickets(id) ON DELETE CASCADE,
  author_id uuid NOT NULL REFERENCES auth.users(id),
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.support_ticket_messages TO authenticated;
GRANT ALL ON public.support_ticket_messages TO service_role;
ALTER TABLE public.support_ticket_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "ticket party read" ON public.support_ticket_messages FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()) OR EXISTS (SELECT 1 FROM public.support_tickets t WHERE t.id = ticket_id AND t.client_id = auth.uid()));
CREATE POLICY "ticket party insert" ON public.support_ticket_messages FOR INSERT TO authenticated
  WITH CHECK (author_id = auth.uid() AND (public.is_staff(auth.uid()) OR EXISTS (SELECT 1 FROM public.support_tickets t WHERE t.id = ticket_id AND t.client_id = auth.uid())));

-- =========== Contact Submissions ===========
CREATE TABLE public.contact_submissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  email text NOT NULL,
  company text,
  phone text,
  telegram_handle text,
  message text NOT NULL,
  source text NOT NULL DEFAULT 'contact',
  status text NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.contact_submissions TO anon, authenticated;
GRANT ALL ON public.contact_submissions TO service_role;
ALTER TABLE public.contact_submissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "anyone submit" ON public.contact_submissions FOR INSERT TO anon, authenticated WITH CHECK (true);
CREATE POLICY "staff read submissions" ON public.contact_submissions FOR SELECT TO authenticated USING (public.is_staff(auth.uid()));
CREATE POLICY "staff update submissions" ON public.contact_submissions FOR UPDATE TO authenticated USING (public.is_staff(auth.uid()));

-- =========== Settings (single row key/value) ===========
CREATE TABLE public.app_settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL DEFAULT '{}'::jsonb,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.app_settings TO authenticated;
GRANT ALL ON public.app_settings TO service_role;
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "staff settings" ON public.app_settings FOR ALL TO authenticated USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

INSERT INTO public.app_settings (key, value) VALUES
  ('telegram', '{"chat_id":"","handle":"casinoapi_support"}'::jsonb),
  ('brand', '{"name":"Casino API Provider","tagline":"#1 Casino Gaming API Provider"}'::jsonb);