# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html

import logging
import psycopg2
import os
from scrapy.exceptions import DropItem
class PostgresPipeline:
    
    def __init__(self, db_config):
        # 1. Store the config
        self.db_config = db_config
        # 2. Initialize the set here so it exists when process_item is called
        self.seen_urls = set()

    @classmethod
    def from_crawler(cls, crawler):
        db_config = {
        'host': crawler.settings.get('DB_HOST'),
        'database': crawler.settings.get('DB_NAME'),
        'user': crawler.settings.get('DB_USER'),
        'password': crawler.settings.get('DB_PASSWORD'),
        }
        return cls(db_config=db_config)




    def open_spider(self, spider):
        # Connect using environment variables
        self.connection = psycopg2.connect(
            host=os.getenv('DB_HOST'),
            database=os.getenv('DB_NAME'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            port=os.getenv('DB_PORT', '5432')
        )
        self.cur = self.connection.cursor()
        
        # Create table if it doesn't exist
        self.cur.execute("""
            CREATE TABLE IF NOT EXISTS articles (
                id SERIAL PRIMARY KEY,
                title TEXT,
                url TEXT UNIQUE,
                content TEXT,
                domain TEXT,
                timestamp TIMESTAMPTZ
            )
        """)
        self.connection.commit()

    def process_item(self, item, spider):
        url = item['url']
        if url in self.seen_urls:
            raise DropItem(f"Duplicate URL (in-memory): {url}")
        self.seen_urls.add(url)
        try:
            self.cur.execute("""
                INSERT INTO articles (title, url, content, domain, timestamp)
                VALUES (%s, %s, %s, %s, %s)
                ON CONFLICT (url) DO NOTHING
            """, (
                item['title'],
                item['url'],
                item['text'],
                item['domain'],
                item['timestamp']
            ))
            if self.cur.rowcount == 0:
                raise DropItem(f"Duplicate URL (database): {url}")
            self.connection.commit()
            return item
        except DropItem:
            self.connection.rollback()
            raise
        except Exception as e:
            spider.logger.error(f"Error saving to Postgres: {e}")
            self.connection.rollback()
            raise

    def close_spider(self, spider):
        self.cur.close()
        self.connection.close()

from itemadapter import ItemAdapter


class NewsscraperPipeline:
    def process_item(self, item, spider):
        return item