-- Migration: Social Media & Website Audit Extension
-- Date: 2025-12-29
-- Description: Adds columns for comprehensive social media and website audit

-- ============================================================
-- 1. SOCIAL MEDIA - activity tracking
-- ============================================================
ALTER TABLE company_social_media
ADD COLUMN IF NOT EXISTS total_posts INTEGER,
ADD COLUMN IF NOT EXISTS posts_30d INTEGER,
ADD COLUMN IF NOT EXISTS posts_365d INTEGER,
ADD COLUMN IF NOT EXISTS profile_name VARCHAR(255),
ADD COLUMN IF NOT EXISTS profile_description TEXT,
ADD COLUMN IF NOT EXISTS last_post_date DATE;

-- Add TikTok as valid platform (check constraint if exists)
COMMENT ON TABLE company_social_media IS 'Social media profiles: facebook, instagram, youtube, linkedin, twitter, tiktok';

-- ============================================================
-- 2. WEBSITE ANALYSIS - extended metadata
-- ============================================================
ALTER TABLE company_website_analysis
ADD COLUMN IF NOT EXISTS last_modified_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS hosting_provider VARCHAR(100),
ADD COLUMN IF NOT EXISTS hosting_ip VARCHAR(45),
ADD COLUMN IF NOT EXISTS server_software VARCHAR(100),
ADD COLUMN IF NOT EXISTS site_author VARCHAR(255),
ADD COLUMN IF NOT EXISTS site_generator VARCHAR(100),
ADD COLUMN IF NOT EXISTS domain_registrar VARCHAR(100),
ADD COLUMN IF NOT EXISTS is_mobile_friendly BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS has_viewport_meta BOOLEAN DEFAULT FALSE;

-- ============================================================
-- 3. GOOGLE BUSINESS PROFILE - reviews
-- ============================================================
ALTER TABLE company_website_analysis
ADD COLUMN IF NOT EXISTS google_rating NUMERIC(2,1),
ADD COLUMN IF NOT EXISTS google_reviews_count INTEGER,
ADD COLUMN IF NOT EXISTS google_place_id VARCHAR(100),
ADD COLUMN IF NOT EXISTS google_business_status VARCHAR(50);

-- ============================================================
-- 4. AUDIT METADATA
-- ============================================================
ALTER TABLE company_website_analysis
ADD COLUMN IF NOT EXISTS audit_source VARCHAR(50) DEFAULT 'automated',
ADD COLUMN IF NOT EXISTS audit_version VARCHAR(20) DEFAULT '1.0',
ADD COLUMN IF NOT EXISTS audit_errors TEXT;

-- ============================================================
-- 5. INDEXES for new columns
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_website_google_rating ON company_website_analysis(google_rating DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_website_hosting ON company_website_analysis(hosting_provider);
CREATE INDEX IF NOT EXISTS idx_social_media_posts ON company_social_media(total_posts DESC NULLS LAST);

-- ============================================================
-- 6. Verify migration
-- ============================================================
DO $$
BEGIN
    RAISE NOTICE 'Migration 20251229_social_media_audit completed successfully';
END $$;