-- Social Media Publisher Tables
-- Created: 2026-02-17

-- Table 1: social_media_posts (posty do publikacji)
CREATE TABLE IF NOT EXISTS social_media_posts (
    id SERIAL PRIMARY KEY,

    -- Typ i platforma
    post_type VARCHAR(50) NOT NULL,  -- member_spotlight, regional_news, event_invitation, event_recap, chamber_news
    platform VARCHAR(20) NOT NULL DEFAULT 'facebook',

    -- Treść
    content TEXT NOT NULL,
    hashtags TEXT,
    image_path VARCHAR(500),  -- ścieżka w static/uploads/social/

    -- Kontekst (opcjonalny)
    company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL,
    event_id INTEGER REFERENCES norda_events(id) ON DELETE SET NULL,

    -- Workflow status
    status VARCHAR(20) NOT NULL DEFAULT 'draft',  -- draft, approved, scheduled, published, failed

    -- Scheduling
    scheduled_at TIMESTAMP,
    published_at TIMESTAMP,

    -- Facebook response
    meta_post_id VARCHAR(100),
    meta_response JSONB,

    -- Engagement (cache z FB API)
    engagement_likes INTEGER DEFAULT 0,
    engagement_comments INTEGER DEFAULT 0,
    engagement_shares INTEGER DEFAULT 0,
    engagement_reach INTEGER,
    engagement_updated_at TIMESTAMP,

    -- AI metadata
    ai_model VARCHAR(100),
    ai_prompt_template VARCHAR(100),

    -- Audyt
    created_by INTEGER NOT NULL REFERENCES users(id),
    approved_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indeksy
CREATE INDEX IF NOT EXISTS idx_social_posts_status ON social_media_posts(status);
CREATE INDEX IF NOT EXISTS idx_social_posts_type ON social_media_posts(post_type);
CREATE INDEX IF NOT EXISTS idx_social_posts_company ON social_media_posts(company_id);
CREATE INDEX IF NOT EXISTS idx_social_posts_scheduled ON social_media_posts(scheduled_at) WHERE status = 'scheduled';

-- Table 2: social_media_config (konfiguracja strony FB NORDA)
CREATE TABLE IF NOT EXISTS social_media_config (
    id SERIAL PRIMARY KEY,
    platform VARCHAR(50) NOT NULL UNIQUE,  -- facebook
    page_id VARCHAR(100),
    page_name VARCHAR(255),
    access_token TEXT,           -- Page Access Token (never-expiring)
    token_expires_at TIMESTAMP,  -- NULL = never expires
    is_active BOOLEAN DEFAULT TRUE,
    debug_mode BOOLEAN DEFAULT TRUE,  -- True = drafty, False = live
    config_data JSONB,  -- dodatkowa konfiguracja
    updated_by INTEGER REFERENCES users(id),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Uprawnienia
GRANT ALL ON TABLE social_media_posts TO nordabiz_app;
GRANT ALL ON TABLE social_media_config TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE social_media_posts_id_seq TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE social_media_config_id_seq TO nordabiz_app;