-- 1. Reset subscription plans to only Free Forever and Premium
DELETE FROM public.user_subscriptions
WHERE plan_id IN (SELECT id FROM public.subscription_plans WHERE code NOT IN ('free', 'premium'));

DELETE FROM public.subscription_plans WHERE code NOT IN ('free', 'premium');

INSERT INTO public.subscription_plans (code, name, description, price_cents, currency, interval, features, limits, is_active, sort_order)
VALUES
  ('free', 'Free Forever', 'Everything you need to run one eBay store. Free, forever.', 0, 'USD', 'month',
    '{"listings": true, "orders": true, "inventory": true, "messages": true, "returns": true, "analytics": true, "reports": true}'::jsonb,
    '{"ebay_accounts": 1, "workspaces": 1}'::jsonb, true, 1),
  ('premium', 'Premium', 'Unlimited eBay accounts and all features unlocked.', 1900, 'USD', 'month',
    '{"listings": true, "orders": true, "inventory": true, "messages": true, "returns": true, "analytics": true, "reports": true, "priority_support": true, "advanced_automation": true}'::jsonb,
    '{"ebay_accounts": -1, "workspaces": -1}'::jsonb, true, 2)
ON CONFLICT (code) DO UPDATE SET
  name = EXCLUDED.name,
  description = EXCLUDED.description,
  price_cents = EXCLUDED.price_cents,
  features = EXCLUDED.features,
  limits = EXCLUDED.limits,
  is_active = true,
  sort_order = EXCLUDED.sort_order;

-- Ensure everyone without a subscription is on free
INSERT INTO public.user_subscriptions (user_id, plan_id, status)
SELECT p.id, (SELECT id FROM public.subscription_plans WHERE code = 'free'), 'active'
FROM public.profiles p
WHERE NOT EXISTS (SELECT 1 FROM public.user_subscriptions us WHERE us.user_id = p.id)
ON CONFLICT (user_id) DO NOTHING;

-- 2. Upgrade ebay_accounts table for multi-account, per-user ownership
ALTER TABLE public.ebay_accounts
  ADD COLUMN IF NOT EXISTS user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS ebay_user_id text,
  ADD COLUMN IF NOT EXISTS scopes text[],
  ADD COLUMN IF NOT EXISTS expires_at timestamptz,
  ADD COLUMN IF NOT EXISTS created_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();

-- Drop old permissive policies and recreate with user scope
DO $$ DECLARE r record; BEGIN
  FOR r IN SELECT polname FROM pg_policy WHERE polrelid = 'public.ebay_accounts'::regclass LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON public.ebay_accounts', r.polname);
  END LOOP;
END $$;

GRANT SELECT, INSERT, UPDATE, DELETE ON public.ebay_accounts TO authenticated;
GRANT ALL ON public.ebay_accounts TO service_role;
ALTER TABLE public.ebay_accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users manage own ebay accounts" ON public.ebay_accounts
  FOR ALL TO authenticated
  USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "admins read all ebay accounts" ON public.ebay_accounts
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

DROP TRIGGER IF EXISTS trg_ebay_accounts_updated ON public.ebay_accounts;
CREATE TRIGGER trg_ebay_accounts_updated
  BEFORE UPDATE ON public.ebay_accounts
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- 3. Ebay tokens table (encrypted-at-rest via Supabase; store refresh separately)
CREATE TABLE IF NOT EXISTS public.ebay_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  access_token text NOT NULL,
  refresh_token text NOT NULL,
  expires_at timestamptz NOT NULL,
  scopes text[],
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (account_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.ebay_tokens TO authenticated;
GRANT ALL ON public.ebay_tokens TO service_role;
ALTER TABLE public.ebay_tokens ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users manage own ebay tokens" ON public.ebay_tokens
  FOR ALL TO authenticated
  USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

DROP TRIGGER IF EXISTS trg_ebay_tokens_updated ON public.ebay_tokens;
CREATE TRIGGER trg_ebay_tokens_updated
  BEFORE UPDATE ON public.ebay_tokens
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE INDEX IF NOT EXISTS idx_ebay_accounts_user ON public.ebay_accounts(user_id);
CREATE INDEX IF NOT EXISTS idx_ebay_accounts_workspace ON public.ebay_accounts(workspace_id);