import database from "../../../config/db";

export async function createProductsFaqsTable() {
    try {
        // Table creation
        const tableQuery = `
        CREATE TABLE IF NOT EXISTS products_faqs (
         id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

         -- Product reference
         product_id UUID NOT NULL,

         -- FAQ content
         question TEXT NOT NULL CHECK (char_length(trim(question)) > 0),
         answer TEXT NOT NULL CHECK (char_length(trim(answer)) > 0),
         
         -- Categorization
         category VARCHAR(50) NOT NULL,
         subcategory VARCHAR(100),
         category_order INTEGER DEFAULT 0,
         faq_order INTEGER DEFAULT 0,
         
         -- Tags and classification
         tags TEXT[] DEFAULT '{}',
         keywords TEXT[] DEFAULT '{}',
         complexity VARCHAR(20) DEFAULT 'beginner' 
           CHECK (complexity IN ('beginner', 'intermediate', 'advanced', 'expert')),
         
         -- Helpfulness metrics
         helpful_count INTEGER DEFAULT 0 CHECK (helpful_count >= 0),
         not_helpful_count INTEGER DEFAULT 0 CHECK (not_helpful_count >= 0),
         view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
         click_count INTEGER DEFAULT 0 CHECK (click_count >= 0),
         
         -- Status and visibility
         status VARCHAR(20) DEFAULT 'published' 
           CHECK (status IN ('draft', 'published', 'archived', 'pending')),
         is_featured BOOLEAN DEFAULT false,
         is_pinned BOOLEAN DEFAULT false,
         requires_login BOOLEAN DEFAULT false,
         
         -- Author information (optional - could be product team or support staff)
         author_id UUID,
         last_updated_by UUID,
         
         -- Related resources
         related_faqs UUID[] DEFAULT '{}',
         documentation_url VARCHAR(500),
         video_url VARCHAR(500),
         
         -- SEO and metadata
         slug VARCHAR(120) UNIQUE,
         meta_title VARCHAR(255),
         meta_description TEXT,
         meta_keywords TEXT[] DEFAULT '{}',
         
         -- Timestamps
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         published_at TIMESTAMP,
         last_viewed_at TIMESTAMP,
         
         -- Foreign key constraints
         FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (author_id) REFERENCES accounts(id) ON DELETE SET NULL,
         FOREIGN KEY (last_updated_by) REFERENCES accounts(id) ON DELETE SET NULL,
         
         -- Valid URL format for slugs
         CONSTRAINT valid_slug_format 
           CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
         
         -- Published FAQs must have published_at date
         CONSTRAINT published_requires_date 
           CHECK (status != 'published' OR published_at IS NOT NULL),
         
         -- Order values should be non-negative
         CONSTRAINT non_negative_category_order 
           CHECK (category_order >= 0),
         CONSTRAINT non_negative_faq_order 
           CHECK (faq_order >= 0)
        );`;

        await database.query(tableQuery);

        // Index creation for better performance
        const indexes = [
            // Foreign key indexes
            `CREATE INDEX IF NOT EXISTS idx_faqs_product_id ON products_faqs(product_id);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_author_id ON products_faqs(author_id);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_last_updated_by ON products_faqs(last_updated_by);`,
            
            // For categorization and filtering
            `CREATE INDEX IF NOT EXISTS idx_faqs_category ON products_faqs(category);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_subcategory ON products_faqs(subcategory);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_status ON products_faqs(status) WHERE status = 'published';`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_complexity ON products_faqs(complexity);`,
            
            // For sorting and ordering
            `CREATE INDEX IF NOT EXISTS idx_faqs_category_order ON products_faqs(category_order);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_faq_order ON products_faqs(faq_order);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_created_at ON products_faqs(created_at DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_published_at ON products_faqs(published_at DESC NULLS LAST);`,
            
            // For featured and pinned FAQs
            `CREATE INDEX IF NOT EXISTS idx_faqs_is_featured ON products_faqs(is_featured) WHERE is_featured = true;`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_is_pinned ON products_faqs(is_pinned) WHERE is_pinned = true;`,
            
            // For helpfulness metrics
            `CREATE INDEX IF NOT EXISTS idx_faqs_helpful_score ON products_faqs((helpful_count - not_helpful_count) DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_view_count ON products_faqs(view_count DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_click_count ON products_faqs(click_count DESC);`,
            
            // For search functionality
            `CREATE INDEX IF NOT EXISTS idx_faqs_search_question ON products_faqs USING gin(to_tsvector('english', question));`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_search_answer ON products_faqs USING gin(to_tsvector('english', answer));`,
            
            // For array fields search
            `CREATE INDEX IF NOT EXISTS idx_faqs_tags ON products_faqs USING gin(tags);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_keywords ON products_faqs USING gin(keywords);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_meta_keywords ON products_faqs USING gin(meta_keywords);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_related_faqs ON products_faqs USING gin(related_faqs);`,
            
            // For slug access
            `CREATE INDEX IF NOT EXISTS idx_faqs_slug ON products_faqs(slug);`,
            
            // Composite indexes for common queries
            `CREATE INDEX IF NOT EXISTS idx_faqs_product_category ON products_faqs(product_id, category, faq_order);`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_product_status ON products_faqs(product_id, status, published_at DESC) WHERE status = 'published';`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_product_featured ON products_faqs(product_id, is_featured) WHERE is_featured = true;`,
            `CREATE INDEX IF NOT EXISTS idx_faqs_product_complexity ON products_faqs(product_id, complexity, faq_order);`,
            
            // For statistics and analytics
            `CREATE INDEX IF NOT EXISTS idx_faqs_last_viewed ON products_faqs(last_viewed_at DESC NULLS LAST);`,
        ];

        for (const indexQuery of indexes) {
            try {
                await database.query(indexQuery);
            } catch (err) {
                // Index already exists, ignore
                if (!(err instanceof Error) || !err.message.includes("already exists")) {
                    throw err;
                }
            }
        }

        // Create unique index for case-insensitive question per product
        const uniqueQuestionIndex = `
          CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_question_product 
          ON products_faqs(product_id, lower(trim(question)));
        `;

        await database.query(uniqueQuestionIndex);

        // Drop existing functions and triggers first to avoid conflicts
        const cleanupQueries = [
            `DROP TRIGGER IF EXISTS set_faqs_updated_at ON products_faqs;`,
            `DROP FUNCTION IF EXISTS update_faqs_updated_at();`,
            `DROP FUNCTION IF EXISTS increment_faq_view_count();`,
            `DROP FUNCTION IF EXISTS increment_faq_click_count();`,
            `DROP FUNCTION IF EXISTS mark_faq_helpful();`,
            `DROP FUNCTION IF EXISTS generate_faq_slug();`,
        ];

        for (const cleanupQuery of cleanupQueries) {
            try {
                await database.query(cleanupQuery);
            } catch (err) {
                // Ignore errors if functions/triggers don't exist
            }
        }

        // Create function to automatically update updated_at
        const updateTriggerFunction = `
          CREATE OR REPLACE FUNCTION update_faqs_updated_at()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            IF NEW.status = 'published' AND OLD.status != 'published' THEN
              NEW.published_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(updateTriggerFunction);

        // Create trigger for automatic updated_at
        const updateTrigger = `
          CREATE TRIGGER set_faqs_updated_at
          BEFORE UPDATE ON products_faqs
          FOR EACH ROW
          EXECUTE FUNCTION update_faqs_updated_at();
        `;

        await database.query(updateTrigger);

        // Create function to generate FAQ slug from question
        const slugFunction = `
          CREATE OR REPLACE FUNCTION generate_faq_slug(question TEXT)
          RETURNS TEXT AS $$
          DECLARE
            base_slug TEXT;
            counter INTEGER := 1;
            final_slug TEXT;
          BEGIN
            -- Create base slug from question (take first 100 chars)
            base_slug := lower(
              regexp_replace(
                regexp_replace(
                  substring(question from 1 for 100),
                  '[^a-zA-Z0-9\\s-]', '', 'g'
                ),
                '\\s+', '-', 'g'
              )
            );
            
            -- If slug is empty, use a default
            IF base_slug = '' OR base_slug IS NULL THEN
              base_slug := 'faq';
            END IF;
            
            -- Remove trailing hyphens
            base_slug := trim(both '-' from base_slug);
            
            -- Check if slug exists and append counter if needed
            final_slug := base_slug;
            WHILE EXISTS (SELECT 1 FROM products_faqs WHERE slug = final_slug) LOOP
              final_slug := base_slug || '-' || counter;
              counter := counter + 1;
            END LOOP;
            
            RETURN final_slug;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(slugFunction);

        // Create function to increment view count
        const viewCountFunction = `
          CREATE OR REPLACE FUNCTION increment_faq_view_count(faq_id UUID)
          RETURNS VOID AS $$
          BEGIN
            UPDATE products_faqs 
            SET 
              view_count = view_count + 1,
              last_viewed_at = CURRENT_TIMESTAMP
            WHERE id = faq_id
            AND status = 'published';
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(viewCountFunction);

        // Create function to update click count
        const clickCountFunction = `
          CREATE OR REPLACE FUNCTION increment_faq_click_count(faq_id UUID)
          RETURNS VOID AS $$
          BEGIN
            UPDATE products_faqs 
            SET click_count = click_count + 1
            WHERE id = faq_id
            AND status = 'published';
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(clickCountFunction);

        // Create function to mark FAQ as helpful
        const helpfulFunction = `
          CREATE OR REPLACE FUNCTION mark_faq_helpful(faq_id UUID, is_helpful BOOLEAN)
          RETURNS INTEGER AS $$
          DECLARE
            new_count INTEGER;
          BEGIN
            IF is_helpful THEN
              UPDATE products_faqs 
              SET helpful_count = helpful_count + 1
              WHERE id = faq_id
              AND status = 'published'
              RETURNING helpful_count INTO new_count;
            ELSE
              UPDATE products_faqs 
              SET not_helpful_count = not_helpful_count + 1
              WHERE id = faq_id
              AND status = 'published'
              RETURNING not_helpful_count INTO new_count;
            END IF;
            
            RETURN COALESCE(new_count, 0);
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(helpfulFunction);

        console.log("✅ Products FAQs table created successfully");
    } catch (error) {
        console.error("❌ Failed To Create Products FAQs Table.", error);
        process.exit(1);
    }
}