-- =============================================================================
-- NordaBiz - Migration: News Tables
-- =============================================================================
-- Created: 2025-12-29
-- Description: Creates tables for company news/mentions and user notifications
-- =============================================================================

-- =============================================================================
-- TABLE: company_news
-- =============================================================================
-- Stores news and mentions for companies.
-- Sources: web search (Brave), social media, press releases, awards.
-- Supports AI filtering and manual moderation.

CREATE TABLE IF NOT EXISTS company_news (
    id SERIAL PRIMARY KEY,

    -- Company reference
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    -- News content
    title VARCHAR(500) NOT NULL,               -- News headline
    summary TEXT,                               -- Short description/excerpt
    content TEXT,                               -- Full content if available

    -- Source information
    source_url VARCHAR(1000),                  -- Original article/post URL
    source_name VARCHAR(255),                  -- e.g., "Gazeta Wyborcza", "Facebook", "LinkedIn"
    source_type VARCHAR(50),                   -- web, facebook, linkedin, instagram, press, award

    -- News classification
    news_type VARCHAR(50) DEFAULT 'news_mention',
    -- Supported types:
    --   news_mention    - General media mention
    --   press_release   - Official company press release
    --   award           - Award or recognition
    --   social_post     - Social media post
    --   event           - Event announcement
    --   financial       - Financial news
    --   partnership     - Partnership or collaboration announcement

    -- Dates
    published_date TIMESTAMP,                  -- When the news was originally published
    discovered_at TIMESTAMP DEFAULT NOW(),     -- When our system found it

    -- AI filtering and relevance
    is_approved BOOLEAN DEFAULT FALSE,         -- Passed AI filter and is approved for display
    is_visible BOOLEAN DEFAULT TRUE,           -- Visible on company profile (can be hidden by user)
    relevance_score NUMERIC(3,2),              -- 0.00 - 1.00 (AI calculated relevance)
    ai_summary TEXT,                           -- Gemini-generated summary
    ai_tags TEXT[],                            -- AI-extracted tags (PostgreSQL array)

    -- Moderation workflow
    moderation_status VARCHAR(20) DEFAULT 'pending',  -- pending, approved, rejected
    moderated_by INTEGER REFERENCES users(id),         -- Admin who moderated
    moderated_at TIMESTAMP,                            -- When moderation happened
    rejection_reason VARCHAR(255),                     -- If rejected, why

    -- Engagement tracking
    view_count INTEGER DEFAULT 0,              -- Number of views on platform

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    -- Unique constraint: prevent duplicate news for same company
    CONSTRAINT uq_company_news_url UNIQUE (company_id, source_url)
);

-- Indexes for company_news
CREATE INDEX IF NOT EXISTS idx_company_news_company_id ON company_news(company_id);
CREATE INDEX IF NOT EXISTS idx_company_news_source_type ON company_news(source_type);
CREATE INDEX IF NOT EXISTS idx_company_news_news_type ON company_news(news_type);
CREATE INDEX IF NOT EXISTS idx_company_news_is_approved ON company_news(is_approved);
CREATE INDEX IF NOT EXISTS idx_company_news_published_date ON company_news(published_date DESC);
CREATE INDEX IF NOT EXISTS idx_company_news_discovered_at ON company_news(discovered_at DESC);
CREATE INDEX IF NOT EXISTS idx_company_news_moderation ON company_news(moderation_status);

-- Composite index for efficient querying of approved, visible news
CREATE INDEX IF NOT EXISTS idx_company_news_approved_visible
    ON company_news(company_id, is_approved, is_visible)
    WHERE is_approved = TRUE AND is_visible = TRUE;


-- =============================================================================
-- TABLE: user_notifications
-- =============================================================================
-- In-app notifications for users.
-- Supports notification center and unread badges.

CREATE TABLE IF NOT EXISTS user_notifications (
    id SERIAL PRIMARY KEY,

    -- User reference
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Notification content
    title VARCHAR(255) NOT NULL,               -- Notification title
    message TEXT,                              -- Full notification message
    notification_type VARCHAR(50) DEFAULT 'info',
    -- Supported types:
    --   news      - New company news
    --   system    - System announcements
    --   message   - Private message notification
    --   event     - Event reminder/update
    --   alert     - Important alert

    -- Related entity (optional polymorphic reference)
    related_type VARCHAR(50),                  -- company_news, event, message, etc.
    related_id INTEGER,                        -- ID of related entity

    -- Status
    is_read BOOLEAN DEFAULT FALSE,             -- Has user read the notification?
    read_at TIMESTAMP,                         -- When was it read?

    -- Action
    action_url VARCHAR(500),                   -- URL to navigate when clicked

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for user_notifications
CREATE INDEX IF NOT EXISTS idx_user_notifications_user_id ON user_notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_user_notifications_type ON user_notifications(notification_type);
CREATE INDEX IF NOT EXISTS idx_user_notifications_is_read ON user_notifications(is_read);
CREATE INDEX IF NOT EXISTS idx_user_notifications_created_at ON user_notifications(created_at DESC);

-- Composite index for unread notifications badge (most common query)
CREATE INDEX IF NOT EXISTS idx_user_notifications_unread
    ON user_notifications(user_id, is_read, created_at DESC)
    WHERE is_read = FALSE;


-- =============================================================================
-- COMMENTS
-- =============================================================================

COMMENT ON TABLE company_news IS 'News and mentions for companies from various sources (web, social media, press)';
COMMENT ON TABLE user_notifications IS 'In-app notifications for users with read/unread tracking';

-- company_news columns
COMMENT ON COLUMN company_news.company_id IS 'FK to companies table';
COMMENT ON COLUMN company_news.title IS 'News headline (max 500 chars)';
COMMENT ON COLUMN company_news.summary IS 'Short excerpt or description';
COMMENT ON COLUMN company_news.content IS 'Full article content if scraped';
COMMENT ON COLUMN company_news.source_url IS 'Original URL of the news article';
COMMENT ON COLUMN company_news.source_name IS 'Name of the source (e.g., Gazeta Wyborcza)';
COMMENT ON COLUMN company_news.source_type IS 'Type of source: web, facebook, linkedin, instagram, press, award';
COMMENT ON COLUMN company_news.news_type IS 'Classification: news_mention, press_release, award, social_post, event, financial, partnership';
COMMENT ON COLUMN company_news.published_date IS 'Original publication date';
COMMENT ON COLUMN company_news.discovered_at IS 'When our system found this news';
COMMENT ON COLUMN company_news.is_approved IS 'TRUE if passed AI filter and approved for display';
COMMENT ON COLUMN company_news.is_visible IS 'TRUE if visible on company profile';
COMMENT ON COLUMN company_news.relevance_score IS 'AI-calculated relevance score (0.00-1.00)';
COMMENT ON COLUMN company_news.ai_summary IS 'Gemini-generated summary';
COMMENT ON COLUMN company_news.ai_tags IS 'Array of AI-extracted tags';
COMMENT ON COLUMN company_news.moderation_status IS 'Status: pending, approved, rejected';
COMMENT ON COLUMN company_news.moderated_by IS 'Admin user ID who moderated';
COMMENT ON COLUMN company_news.moderated_at IS 'Timestamp of moderation';
COMMENT ON COLUMN company_news.rejection_reason IS 'Reason if rejected';
COMMENT ON COLUMN company_news.view_count IS 'Number of views on platform';

-- user_notifications columns
COMMENT ON COLUMN user_notifications.user_id IS 'FK to users table';
COMMENT ON COLUMN user_notifications.title IS 'Notification title (max 255 chars)';
COMMENT ON COLUMN user_notifications.message IS 'Full notification message';
COMMENT ON COLUMN user_notifications.notification_type IS 'Type: news, system, message, event, alert';
COMMENT ON COLUMN user_notifications.related_type IS 'Type of related entity (company_news, event, message)';
COMMENT ON COLUMN user_notifications.related_id IS 'ID of related entity';
COMMENT ON COLUMN user_notifications.is_read IS 'TRUE if user has read the notification';
COMMENT ON COLUMN user_notifications.read_at IS 'Timestamp when notification was read';
COMMENT ON COLUMN user_notifications.action_url IS 'URL to navigate when clicked';


-- =============================================================================
-- MIGRATION COMPLETE
-- =============================================================================
-- To run this migration:
--   psql -h 10.22.68.249 -U nordabiz_app -d nordabiz -f migrate_news_tables.sql
--
-- Or via Python:
--   python migrate_add_news_tables.py
-- =============================================================================
