-- ============================================================
-- Migration: wp_imported_posts
-- Timestamp: 20260320230000
-- ============================================================

-- 1. wp_imported_posts table
CREATE TABLE IF NOT EXISTS public.wp_imported_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wp_id INTEGER NOT NULL,
  wp_slug TEXT NOT NULL,
  title TEXT NOT NULL,
  excerpt TEXT,
  content TEXT,
  author_name TEXT,
  category TEXT,
  tags TEXT[],
  featured_image TEXT,
  featured_image_alt TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  wp_published_at TIMESTAMPTZ,
  imported_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_wp_imported_posts_wp_id ON public.wp_imported_posts(wp_id);
CREATE INDEX IF NOT EXISTS idx_wp_imported_posts_slug ON public.wp_imported_posts(wp_slug);
CREATE INDEX IF NOT EXISTS idx_wp_imported_posts_imported_at ON public.wp_imported_posts(imported_at DESC);

-- 2. wp_import_logs table (tracks import runs)
CREATE TABLE IF NOT EXISTS public.wp_import_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMPTZ,
  total_fetched INTEGER DEFAULT 0,
  total_imported INTEGER DEFAULT 0,
  total_skipped INTEGER DEFAULT 0,
  total_errors INTEGER DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'running',
  error_message TEXT
);

CREATE INDEX IF NOT EXISTS idx_wp_import_logs_started_at ON public.wp_import_logs(started_at DESC);

-- 3. update_updated_at function (reuse if exists)
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$;

-- 4. Enable RLS
ALTER TABLE public.wp_imported_posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.wp_import_logs ENABLE ROW LEVEL SECURITY;

-- 5. RLS Policies for wp_imported_posts
DROP POLICY IF EXISTS "public_read_wp_imported_posts" ON public.wp_imported_posts;
CREATE POLICY "public_read_wp_imported_posts"
ON public.wp_imported_posts
FOR SELECT
TO public
USING (true);

DROP POLICY IF EXISTS "authenticated_manage_wp_imported_posts" ON public.wp_imported_posts;
CREATE POLICY "authenticated_manage_wp_imported_posts"
ON public.wp_imported_posts
FOR ALL
TO authenticated
USING (true)
WITH CHECK (true);

-- 6. RLS Policies for wp_import_logs
DROP POLICY IF EXISTS "public_read_wp_import_logs" ON public.wp_import_logs;
CREATE POLICY "public_read_wp_import_logs"
ON public.wp_import_logs
FOR SELECT
TO public
USING (true);

DROP POLICY IF EXISTS "authenticated_manage_wp_import_logs" ON public.wp_import_logs;
CREATE POLICY "authenticated_manage_wp_import_logs"
ON public.wp_import_logs
FOR ALL
TO authenticated
USING (true)
WITH CHECK (true);

-- 7. Trigger for updated_at
DROP TRIGGER IF EXISTS update_wp_imported_posts_updated_at ON public.wp_imported_posts;
CREATE TRIGGER update_wp_imported_posts_updated_at
  BEFORE UPDATE ON public.wp_imported_posts
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();