

-- Stories Table
CREATE TABLE IF NOT EXISTS stories (
    story_id INT AUTO_INCREMENT PRIMARY KEY,
    headline VARCHAR(255) NOT NULL,
    summary TEXT,
    main_image_url VARCHAR(255),
    importance_score INT DEFAULT 0,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_trending BOOLEAN DEFAULT FALSE,
    category VARCHAR(100)
);

-- Story Timeline Table
CREATE TABLE IF NOT EXISTS story_timeline (
    timeline_id INT AUTO_INCREMENT PRIMARY KEY,
    story_id INT NOT NULL,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    event_description TEXT NOT NULL,
    source_id INT,
    source_url VARCHAR(255),
    FOREIGN KEY (story_id) REFERENCES stories(story_id) ON DELETE CASCADE,
    FOREIGN KEY (source_id) REFERENCES news_sources(source_id) ON DELETE SET NULL
);

-- Story Articles Table (connects stories with individual articles)
CREATE TABLE IF NOT EXISTS story_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    story_id INT NOT NULL,
    source_id INT NOT NULL,
    article_url VARCHAR(255) NOT NULL,
    article_title VARCHAR(255) NOT NULL,
    published_date TIMESTAMP,
    fetched_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (story_id) REFERENCES stories(story_id) ON DELETE CASCADE,
    FOREIGN KEY (source_id) REFERENCES news_sources(source_id) ON DELETE CASCADE
);

-- Keywords Table
CREATE TABLE IF NOT EXISTS keywords (
    keyword_id INT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(100) NOT NULL,
    UNIQUE KEY (keyword)
);

-- Story Keywords Table
CREATE TABLE IF NOT EXISTS story_keywords (
    story_id INT NOT NULL,
    keyword_id INT NOT NULL,
    PRIMARY KEY (story_id, keyword_id),
    FOREIGN KEY (story_id) REFERENCES stories(story_id) ON DELETE CASCADE,
    FOREIGN KEY (keyword_id) REFERENCES keywords(keyword_id) ON DELETE CASCADE
);