-- Websites table: jisme admin dwara add kiye gaye URLs store honge
CREATE TABLE websites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    url TEXT NOT NULL,
    category VARCHAR(100) NOT NULL,
    title_selector VARCHAR(255) NOT NULL,
    content_selector VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Scraped data table: jo scraped content, metadata category wise save karega
CREATE TABLE scraped_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    website_id INT NOT NULL,
    category VARCHAR(100) NOT NULL,
    title TEXT,
    content LONGTEXT,
    url TEXT,
    metadata JSON,
    scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE
);