-- migrations/001_setup_multisite_structure.sql

-- دالة لإنشاء قواعد البيانات للمواقع المتعددة
CREATE OR REPLACE FUNCTION create_site_databases()
RETURNS VOID AS $$
DECLARE
    site_category TEXT;
BEGIN
    -- التكرار خلال كل فئة موقع
    FOR site_category IN 
        SELECT unnest(ARRAY['tech', 'health', 'news', 'sports', 'lifestyle'])
    LOOP
        -- إنشاء قاعدة بيانات لكل فئة موقع
        EXECUTE format('CREATE DATABASE article_generator_%I', site_category);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- تنفيذ الدالة لإنشاء قواعد البيانات
SELECT create_site_databases();

-- حذف الدالة بعد الاستخدام
DROP FUNCTION create_site_databases();

-- تنفيذ ما يلي داخل كل قاعدة بيانات تم إنشاؤها

-- دالة لإنشاء سكيمات الدول
CREATE OR REPLACE FUNCTION create_country_schemas()
RETURNS VOID AS $$
DECLARE
    country_code TEXT;
BEGIN
    -- التكرار خلال كل رمز دولة
    FOR country_code IN 
        SELECT unnest(ARRAY['usa', 'canada', 'uk', 'australia', 'uae', 'sa', 'egypt'])
    LOOP
        -- إنشاء سكيما لكل دولة
        EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', country_code);
        
        -- إنشاء جدول المقالات لكل سكيما
        EXECUTE format('
            CREATE TABLE IF NOT EXISTS %I.articles (
              id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
              title VARCHAR(255) NOT NULL,
              slug VARCHAR(255) NOT NULL UNIQUE,
              description TEXT NOT NULL,
              content TEXT NOT NULL,
              author VARCHAR(100) DEFAULT ''AI Writer'',
              published_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              website_category VARCHAR(50) NOT NULL,
              subcategory VARCHAR(100) NOT NULL,
              tags TEXT[] NOT NULL DEFAULT ''{}'',
              image_url TEXT NOT NULL,
              language VARCHAR(10) NOT NULL DEFAULT ''en'',
              country_target VARCHAR(10) NOT NULL DEFAULT %L,
              read_time INTEGER NOT NULL,
              keywords TEXT[] NOT NULL DEFAULT ''{}'',
              views INTEGER NOT NULL DEFAULT 0,
              is_trending BOOLEAN NOT NULL DEFAULT FALSE,
              trend_score FLOAT DEFAULT 0,
              created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
            )', country_code, country_code);
        
        -- إنشاء فهارس البحث
        EXECUTE format('
            CREATE INDEX IF NOT EXISTS articles_title_idx ON %I.articles USING GIN (to_tsvector(''english'', title));
            CREATE INDEX IF NOT EXISTS articles_content_idx ON %I.articles USING GIN (to_tsvector(''english'', content));
            CREATE INDEX IF NOT EXISTS articles_subcategory_idx ON %I.articles(subcategory);
            CREATE INDEX IF NOT EXISTS articles_language_idx ON %I.articles(language);
            CREATE INDEX IF NOT EXISTS articles_tags_idx ON %I.articles USING GIN (tags);
            CREATE INDEX IF NOT EXISTS articles_is_trending_idx ON %I.articles(is_trending);
        ', country_code, country_code, country_code, country_code, country_code, country_code);
        
        -- إنشاء جدول المواضيع الشائعة
        EXECUTE format('
            CREATE TABLE IF NOT EXISTS %I.trending_topics (
              id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
              keyword VARCHAR(255) NOT NULL,
              language VARCHAR(10) NOT NULL DEFAULT ''en'',
              country VARCHAR(10) NOT NULL DEFAULT %L,
              score INTEGER NOT NULL DEFAULT 0,
              fetched_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              generated BOOLEAN NOT NULL DEFAULT FALSE,
              UNIQUE(keyword, language)
            )', country_code, country_code);
        
        -- إنشاء فهارس المواضيع الشائعة
        EXECUTE format('
            CREATE INDEX IF NOT EXISTS trending_topics_language_idx ON %I.trending_topics(language);
            CREATE INDEX IF NOT EXISTS trending_topics_generated_idx ON %I.trending_topics(generated);
            CREATE INDEX IF NOT EXISTS trending_topics_score_idx ON %I.trending_topics(score DESC);
        ', country_code, country_code, country_code);
        
        -- إنشاء جدول المقالات ذات الصلة
        EXECUTE format('
            CREATE TABLE IF NOT EXISTS %I.related_articles (
              id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
              article_id UUID NOT NULL REFERENCES %I.articles(id) ON DELETE CASCADE,
              related_article_id UUID NOT NULL REFERENCES %I.articles(id) ON DELETE CASCADE,
              relevance_score FLOAT NOT NULL DEFAULT 0,
              created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(article_id, related_article_id)
            )', country_code, country_code, country_code);
        
        -- إنشاء فهارس المقالات ذات الصلة
        EXECUTE format('
            CREATE INDEX IF NOT EXISTS related_articles_article_id_idx ON %I.related_articles(article_id);
            CREATE INDEX IF NOT EXISTS related_articles_relevance_score_idx ON %I.related_articles(relevance_score DESC);
        ', country_code, country_code);
        
        -- إنشاء وظيفة تحديث updated_at_column
        EXECUTE format('
            CREATE OR REPLACE FUNCTION %I.update_updated_at_column()
            RETURNS TRIGGER AS $$
            BEGIN
               NEW.updated_at = CURRENT_TIMESTAMP;
               RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        ', country_code);
        
        -- إنشاء مشغل لـ updated_at
        EXECUTE format('
            DROP TRIGGER IF EXISTS update_articles_updated_at ON %I.articles;
            CREATE TRIGGER update_articles_updated_at
            BEFORE UPDATE ON %I.articles
            FOR EACH ROW
            EXECUTE FUNCTION %I.update_updated_at_column();
        ', country_code, country_code, country_code);
        
        -- إضافة قدرات البحث النصي الكامل
        EXECUTE format('
            ALTER TABLE %I.articles ADD COLUMN IF NOT EXISTS search_vector tsvector;
        ', country_code);
        
        -- إنشاء وظيفة تحديث متجه البحث
        EXECUTE format('
            CREATE OR REPLACE FUNCTION %I.articles_search_vector_update() RETURNS TRIGGER AS $$
            BEGIN
              NEW.search_vector = 
                setweight(to_tsvector(''english'', NEW.title), ''A'') ||
                setweight(to_tsvector(''english'', NEW.description), ''B'') ||
                setweight(to_tsvector(''english'', array_to_string(NEW.tags, '' '')), ''B'') ||
                setweight(to_tsvector(''english'', NEW.content), ''C'');
              RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        ', country_code);
        
        -- إنشاء مشغل لتحديث متجه البحث
        EXECUTE format('
            DROP TRIGGER IF EXISTS articles_search_vector_update ON %I.articles;
            CREATE TRIGGER articles_search_vector_update
            BEFORE INSERT OR UPDATE ON %I.articles
            FOR EACH ROW
            EXECUTE FUNCTION %I.articles_search_vector_update();
        ', country_code, country_code, country_code);
        
        -- إنشاء فهرس GIN للبحث النصي الكامل
        EXECUTE format('
            CREATE INDEX IF NOT EXISTS articles_search_idx ON %I.articles USING GIN (search_vector);
        ', country_code);
        
        -- إنشاء جدول تفضيلات المستخدم
        EXECUTE format('
            CREATE TABLE IF NOT EXISTS %I.user_preferences (
              id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
              user_id VARCHAR(255) NOT NULL,
              preferences JSONB NOT NULL DEFAULT ''{}'',
              interests TEXT[] NOT NULL DEFAULT ''{}'',
              created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(user_id)
            )', country_code);
            
        -- إنشاء فهرس لتفضيلات المستخدم
        EXECUTE format('
            CREATE INDEX IF NOT EXISTS user_preferences_user_id_idx ON %I.user_preferences(user_id);
        ', country_code);
        
        -- إنشاء مشغل لـ updated_at في تفضيلات المستخدم
        EXECUTE format('
            DROP TRIGGER IF EXISTS update_user_preferences_updated_at ON %I.user_preferences;
            CREATE TRIGGER update_user_preferences_updated_at
            BEFORE UPDATE ON %I.user_preferences
            FOR EACH ROW
            EXECUTE FUNCTION %I.update_updated_at_column();
        ', country_code, country_code, country_code);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- تنفيذ الدالة لإنشاء جميع السكيمات والجداول
SELECT create_country_schemas();

-- حذف الدالة بعد الاستخدام
DROP FUNCTION create_country_schemas();