-- Migration: Add News Package Tables
-- Run this migration to add news functionality to your existing database

-- Step 1: Create RSS Feeds table
CREATE TABLE IF NOT EXISTS "news_rss_feeds" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" VARCHAR(255) NOT NULL,
  "url" TEXT NOT NULL UNIQUE,
  "category" VARCHAR(100) NOT NULL,
  "is_active" BOOLEAN NOT NULL DEFAULT true,
  "last_fetched_at" TIMESTAMP,
  "fetch_interval" INTEGER NOT NULL DEFAULT 30,
  "error_count" INTEGER NOT NULL DEFAULT 0,
  "created_at" TIMESTAMP NOT NULL DEFAULT NOW(),
  "updated_at" TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Step 2: Create Articles table
CREATE TABLE IF NOT EXISTS "news_articles" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "title" TEXT NOT NULL,
  "description" TEXT,
  "content" TEXT,
  "url" TEXT NOT NULL UNIQUE,
  "image_url" TEXT,
  "published_at" TIMESTAMP NOT NULL,
  "fetched_at" TIMESTAMP NOT NULL DEFAULT NOW(),
  "feed_id" UUID NOT NULL,
  "category" VARCHAR(100) NOT NULL,
  "tags" JSONB NOT NULL DEFAULT '[]',
  "is_active" BOOLEAN NOT NULL DEFAULT true,
  "media_elements" JSONB NOT NULL DEFAULT '[]',
  "has_audio" BOOLEAN NOT NULL DEFAULT false,
  "has_video" BOOLEAN NOT NULL DEFAULT false,
  "media_extracted_at" TIMESTAMP,
  "media_extraction_error" TEXT,
  "created_at" TIMESTAMP NOT NULL DEFAULT NOW(),
  "updated_at" TIMESTAMP NOT NULL DEFAULT NOW(),
  
  CONSTRAINT "fk_articles_feed" FOREIGN KEY ("feed_id") 
    REFERENCES "news_rss_feeds" ("id") ON DELETE CASCADE
);

-- Step 3: Create Saved Articles table
-- Note: Replace 'users' with your actual user table name
CREATE TABLE IF NOT EXISTS "news_saved_articles" (
  "user_id" UUID NOT NULL,
  "article_id" UUID NOT NULL,
  "saved_at" TIMESTAMP NOT NULL DEFAULT NOW(),
  
  PRIMARY KEY ("user_id", "article_id"),
  CONSTRAINT "fk_saved_articles_article" FOREIGN KEY ("article_id") 
    REFERENCES "news_articles" ("id") ON DELETE CASCADE
  -- Add user foreign key based on your user table:
  -- CONSTRAINT "fk_saved_articles_user" FOREIGN KEY ("user_id") 
  --   REFERENCES "users" ("id") ON DELETE CASCADE
);

-- Step 4: Create indexes
CREATE INDEX IF NOT EXISTS "idx_articles_published_at" ON "news_articles" ("published_at");
CREATE INDEX IF NOT EXISTS "idx_articles_category" ON "news_articles" ("category");
CREATE INDEX IF NOT EXISTS "idx_articles_feed_id" ON "news_articles" ("feed_id");
CREATE INDEX IF NOT EXISTS "idx_articles_is_active" ON "news_articles" ("is_active");
CREATE INDEX IF NOT EXISTS "idx_articles_category_active" ON "news_articles" ("category", "is_active");
CREATE INDEX IF NOT EXISTS "idx_articles_has_audio" ON "news_articles" ("has_audio");
CREATE INDEX IF NOT EXISTS "idx_articles_has_video" ON "news_articles" ("has_video");
CREATE INDEX IF NOT EXISTS "idx_feeds_is_active" ON "news_rss_feeds" ("is_active");
CREATE INDEX IF NOT EXISTS "idx_feeds_last_fetched" ON "news_rss_feeds" ("last_fetched_at");
CREATE INDEX IF NOT EXISTS "idx_saved_articles_user" ON "news_saved_articles" ("user_id");

-- Step 5: Create full-text search index (PostgreSQL)
CREATE INDEX IF NOT EXISTS "idx_articles_fulltext" ON "news_articles" 
  USING GIN (to_tsvector('english', title || ' ' || COALESCE(description, '') || ' ' || COALESCE(content, '')));

-- Step 6: Create update trigger function (PostgreSQL)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Step 7: Create triggers
CREATE TRIGGER update_news_rss_feeds_updated_at 
  BEFORE UPDATE ON "news_rss_feeds" 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_news_articles_updated_at 
  BEFORE UPDATE ON "news_articles" 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Step 8: Insert sample RSS feeds (optional)
INSERT INTO "news_rss_feeds" ("name", "url", "category", "fetch_interval") VALUES
  ('TechCrunch', 'https://techcrunch.com/feed/', 'technology', 30),
  ('BBC News', 'http://feeds.bbci.co.uk/news/rss.xml', 'world-news', 30),
  ('Reuters Business', 'https://www.reuters.com/business/feed/', 'business', 30),
  ('CNN Top Stories', 'http://rss.cnn.com/rss/edition.rss', 'general', 30)
ON CONFLICT (url) DO NOTHING;