-- Phase 2: eBay OAuth state storage + sync jobs/history + minimal import tables

-- OAuth state table for Authorization Code flow CSRF protection
CREATE TABLE IF NOT EXISTS public.ebay_oauth_states (
  id text PRIMARY KEY, -- state hash
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  callback_uri text NOT NULL,
  redirect_target text,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

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

CREATE POLICY "states self manage" ON public.ebay_oauth_states
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Sync history table (per account + type)
CREATE TABLE IF NOT EXISTS public.ebay_sync_history (
  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,
  workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  sync_type text NOT NULL, -- initial_import | incremental
  status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','success','failed')),
  error text,
  started_at timestamptz,
  finished_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  last_cursor jsonb,
  UNIQUE(account_id, sync_type, created_at)
);

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

CREATE POLICY "sync_history self" ON public.ebay_sync_history
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Minimal imported entities tables (phase2 launch skeleton)
-- These tables prevent duplicates and allow dashboard aggregation later.

CREATE TABLE IF NOT EXISTS public.ebay_orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  order_id text NOT NULL,
  buyer_name text,
  status text,
  total_value numeric,
  currency text,
  order_date timestamptz,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(account_id, order_id)
);

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

CREATE POLICY "orders self" ON public.ebay_orders
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_orders_set_updated_at
BEFORE UPDATE ON public.ebay_orders
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Listings (minimal)
CREATE TABLE IF NOT EXISTS public.ebay_listings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  listing_id text NOT NULL,
  title text,
  status text,
  quantity integer,
  price numeric,
  currency text,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(account_id, listing_id)
);

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

CREATE POLICY "listings self" ON public.ebay_listings
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_listings_set_updated_at
BEFORE UPDATE ON public.ebay_listings
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Inventory (minimal)
CREATE TABLE IF NOT EXISTS public.ebay_inventory (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  sku text NOT NULL,
  available integer,
  reserved integer,
  status text,
  location text,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(account_id, sku)
);

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

CREATE POLICY "inventory self" ON public.ebay_inventory
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_inventory_set_updated_at
BEFORE UPDATE ON public.ebay_inventory
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Messages (minimal)
CREATE TABLE IF NOT EXISTS public.ebay_messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  conversation_id text NOT NULL,
  message_id text NOT NULL,
  from_name text,
  body text,
  status text,
  sent_at timestamptz,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(account_id, message_id)
);

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

CREATE POLICY "messages self" ON public.ebay_messages
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_messages_set_updated_at
BEFORE UPDATE ON public.ebay_messages
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Returns (minimal)
CREATE TABLE IF NOT EXISTS public.ebay_returns (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  return_id text NOT NULL,
  status text,
  order_id text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE(account_id, return_id)
);

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

CREATE POLICY "returns self" ON public.ebay_returns
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_returns_set_updated_at
BEFORE UPDATE ON public.ebay_returns
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Analytics (minimal)
CREATE TABLE IF NOT EXISTS public.ebay_analytics_daily (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES public.ebay_accounts(id) ON DELETE CASCADE,
  day date NOT NULL,
  revenue numeric,
  orders_count integer,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(account_id, day)
);

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

CREATE POLICY "analytics self" ON public.ebay_analytics_daily
  FOR ALL TO authenticated
  USING (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id))
  WITH CHECK (auth.uid() = (SELECT user_id FROM public.ebay_accounts a WHERE a.id = account_id));

CREATE TRIGGER ebay_analytics_set_updated_at
BEFORE UPDATE ON public.ebay_analytics_daily
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();