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

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

         -- References
         product_id UUID NOT NULL,  -- The main product
         alternative_product_id UUID NOT NULL,  -- The alternative product
         creator_id UUID,  -- NULL if auto-suggested, creator_id if manually assigned
         
         -- Relationship type
         relationship_type VARCHAR(20) DEFAULT 'auto-suggested' 
           CHECK (relationship_type IN ('auto-suggested', 'manual', 'competitor', 'similar', 'better-alternative', 'cheaper-alternative')),
         
         -- Matching criteria (for auto-suggested alternatives)
         match_score DECIMAL(3,2) DEFAULT 0.00 CHECK (match_score >= 0 AND match_score <= 1),
         match_criteria JSONB DEFAULT '{}'::JSONB,
         
         -- User interaction and voting
         is_user_confirmed BOOLEAN DEFAULT false,  -- User confirmed this as a valid alternative
         is_user_rejected BOOLEAN DEFAULT false,   -- User rejected this alternative
         user_vote VARCHAR(10) CHECK (user_vote IN ('upvote', 'downvote', NULL)),
         upvotes INTEGER DEFAULT 0 CHECK (upvotes >= 0),
         downvotes INTEGER DEFAULT 0 CHECK (downvotes >= 0),
         
         -- Ranking and display
         display_order INTEGER DEFAULT 0,
         is_featured BOOLEAN DEFAULT false,
         is_sponsored BOOLEAN DEFAULT false,
         
         -- Comparison metadata
         comparison_notes TEXT,
         pros TEXT[] DEFAULT '{}',
         cons TEXT[] DEFAULT '{}',
         price_comparison VARCHAR(20) CHECK (price_comparison IN ('cheaper', 'same', 'more-expensive', 'unknown')),
         feature_comparison JSONB DEFAULT '{}'::JSONB,
         
         -- Status
         status VARCHAR(20) DEFAULT 'active' 
           CHECK (status IN ('active', 'pending', 'archived', 'flagged')),
         
         -- Timestamps
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         confirmed_at TIMESTAMP,
         rejected_at TIMESTAMP,
         
         -- Foreign key constraints
         FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (alternative_product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (creator_id) REFERENCES accounts(id) ON DELETE SET NULL,
         
         -- Ensure no duplicate alternative relationships
         CONSTRAINT unique_alternative_relationship 
           UNIQUE (product_id, alternative_product_id),
         
         -- Constraints
         CONSTRAINT valid_display_order CHECK (display_order >= 0),
         CONSTRAINT cannot_be_both_confirmed_rejected 
           CHECK (NOT (is_user_confirmed = true AND is_user_rejected = true)),
         CONSTRAINT user_vote_only_for_manual 
           CHECK ((user_vote IS NOT NULL AND relationship_type = 'manual') OR user_vote IS NULL)
        );`;

        await database.query(tableQuery);

        // Index creation for better performance
        const indexes = [
            // Foreign key indexes
            `CREATE INDEX IF NOT EXISTS idx_alternatives_product_id ON products_alternatives_products(product_id);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_alt_product_id ON products_alternatives_products(alternative_product_id);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_creator_id ON products_alternatives_products(creator_id);`,
            
            // For relationship type queries
            `CREATE INDEX IF NOT EXISTS idx_alternatives_relationship_type ON products_alternatives_products(relationship_type);`,
            
            // For user interaction
            `CREATE INDEX IF NOT EXISTS idx_alternatives_user_confirmed ON products_alternatives_products(is_user_confirmed) WHERE is_user_confirmed = true;`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_user_rejected ON products_alternatives_products(is_user_rejected) WHERE is_user_rejected = true;`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_auto_suggested ON products_alternatives_products(relationship_type) WHERE relationship_type = 'auto-suggested';`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_manual ON products_alternatives_products(relationship_type) WHERE relationship_type = 'manual';`,
            
            // For ranking and display
            `CREATE INDEX IF NOT EXISTS idx_alternatives_display_order ON products_alternatives_products(display_order);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_match_score ON products_alternatives_products(match_score DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_is_featured ON products_alternatives_products(is_featured) WHERE is_featured = true;`,
            
            // For voting and popularity
            `CREATE INDEX IF NOT EXISTS idx_alternatives_upvotes ON products_alternatives_products(upvotes DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_vote_score ON products_alternatives_products((upvotes - downvotes) DESC);`,
            
            // For price comparison
            `CREATE INDEX IF NOT EXISTS idx_alternatives_price_comparison ON products_alternatives_products(price_comparison);`,
            
            // For status
            `CREATE INDEX IF NOT EXISTS idx_alternatives_status ON products_alternatives_products(status) WHERE status = 'active';`,
            
            // Composite indexes for common queries
            `CREATE INDEX IF NOT EXISTS idx_alternatives_product_type ON products_alternatives_products(product_id, relationship_type, display_order);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_product_confirmed ON products_alternatives_products(product_id, is_user_confirmed) WHERE is_user_confirmed = true;`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_product_auto_manual ON products_alternatives_products(product_id) WHERE relationship_type IN ('auto-suggested', 'manual');`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_product_active ON products_alternatives_products(product_id, status, match_score DESC) WHERE status = 'active';`,
            
            // For array field searches
            `CREATE INDEX IF NOT EXISTS idx_alternatives_pros ON products_alternatives_products USING gin(pros);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_cons ON products_alternatives_products USING gin(cons);`,
            
            // For date-based queries
            `CREATE INDEX IF NOT EXISTS idx_alternatives_created_at ON products_alternatives_products(created_at DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_alternatives_confirmed_at ON products_alternatives_products(confirmed_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 view for enriched alternative products data
        const alternativesViewQuery = `
          CREATE OR REPLACE VIEW products_alternatives_enriched AS
          SELECT 
            ap.id,
            ap.product_id,
            ap.alternative_product_id,
            ap.creator_id,
            ap.relationship_type,
            ap.match_score,
            ap.match_criteria,
            ap.is_user_confirmed,
            ap.is_user_rejected,
            ap.user_vote,
            ap.upvotes,
            ap.downvotes,
            ap.display_order,
            ap.is_featured,
            ap.is_sponsored,
            ap.comparison_notes,
            ap.pros,
            ap.cons,
            ap.price_comparison,
            ap.feature_comparison,
            ap.status,
            ap.created_at,
            ap.updated_at,
            ap.confirmed_at,
            ap.rejected_at,
            
            -- Main product details
            mp.name as main_product_name,
            mp.slug as main_product_slug,
            mp.logo as main_product_logo,
            mp.description as main_product_description,
            -- mp.category as main_product_category,
            -- mp.price as main_product_price,
            mp.rating as main_product_rating,
            
            -- Alternative product details
            apd.name as alternative_product_name,
            apd.slug as alternative_product_slug,
            apd.logo as alternative_product_logo,
            apd.description as alternative_product_description,
            -- apd.category as alternative_product_category,
            -- apd.price as alternative_product_price,
            apd.rating as alternative_product_rating,
            apd.website as alternative_product_website,
            -- apd.project_url as alternative_product_url,
            
            -- User details (if manual assignment)
            a.user_full_name as assigned_by_name,
            a.avatar as assigned_by_avatar
            
          FROM products_alternatives_products ap
          LEFT JOIN products mp ON ap.product_id = mp.id
          LEFT JOIN products apd ON ap.alternative_product_id = apd.id
          LEFT JOIN accounts a ON ap.creator_id = a.id
          WHERE ap.status = 'active'
          AND mp.status = 'active'
          AND apd.status = 'active';
        `;

        await database.query(alternativesViewQuery);

        // Drop existing functions and triggers first to avoid conflicts
        const cleanupQueries = [
            `DROP TRIGGER IF EXISTS set_alternatives_updated_at ON products_alternatives_products;`,
            `DROP TRIGGER IF EXISTS handle_alternative_confirmation ON products_alternatives_products;`,
            `DROP FUNCTION IF EXISTS update_alternatives_updated_at();`,
            `DROP FUNCTION IF EXISTS handle_confirmation_rejection();`,
            `DROP FUNCTION IF EXISTS get_alternative_products_for_user();`,
            `DROP FUNCTION IF EXISTS auto_suggest_alternatives();`,
        ];

        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_alternatives_updated_at()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            
            -- Update confirmed_at when user confirms
            IF NEW.is_user_confirmed = true AND OLD.is_user_confirmed = false THEN
              NEW.confirmed_at = CURRENT_TIMESTAMP;
            END IF;
            
            -- Update rejected_at when user rejects
            IF NEW.is_user_rejected = true AND OLD.is_user_rejected = false THEN
              NEW.rejected_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_alternatives_updated_at
          BEFORE UPDATE ON products_alternatives_products
          FOR EACH ROW
          EXECUTE FUNCTION update_alternatives_updated_at();
        `;

        await database.query(updateTrigger);

        // Create function to auto-suggest alternatives
        const autoSuggestFunction = `
          CREATE OR REPLACE FUNCTION auto_suggest_alternatives(product_uuid UUID)
          RETURNS INTEGER AS $$
          DECLARE
            main_product RECORD;
            similar_product RECORD;
            match_score DECIMAL(3,2);
            suggested_count INTEGER := 0;
            match_criteria JSONB;
          BEGIN
            -- Get main product details
            SELECT * INTO main_product 
            FROM products 
            WHERE id = product_uuid AND status = 'active';
            
            IF NOT FOUND THEN
              RETURN 0;
            END IF;
            
            -- Find similar products based on various criteria
            FOR similar_product IN 
              SELECT * FROM products 
              WHERE id != product_uuid 
                AND status = 'active'
                AND (category = main_product.category 
                     OR (tags && main_product.tags)
                     OR (tech_stack && main_product.tech_stack)
                     OR pricing_model = main_product.pricing_model)
              ORDER BY (rating * 0.4 + followers * 0.3 + upvotes * 0.3) DESC
              LIMIT 10
            LOOP
              -- Calculate match score
              match_score := 0.0;
              match_criteria := '{}'::JSONB;
              
              -- Category match (30% weight)
              IF similar_product.category = main_product.category THEN
                match_score := match_score + 0.3;
                match_criteria := match_criteria || '{"category": true}'::JSONB;
              END IF;
              
              -- Tags match (25% weight)
              IF array_length(similar_product.tags & main_product.tags, 1) > 0 THEN
                match_score := match_score + 0.25;
                match_criteria := match_criteria || '{"tags": true}'::JSONB;
              END IF;
              
              -- Tech stack match (20% weight)
              IF array_length(similar_product.tech_stack & main_product.tech_stack, 1) > 0 THEN
                match_score := match_score + 0.20;
                match_criteria := match_criteria || '{"tech_stack": true}'::JSONB;
              END IF;
              
              -- Pricing model match (15% weight)
              IF similar_product.pricing_model = main_product.pricing_model THEN
                match_score := match_score + 0.15;
                match_criteria := match_criteria || '{"pricing": true}'::JSONB;
              END IF;
              
              -- Platform match (10% weight)
              IF array_length(similar_product.platform & main_product.platform, 1) > 0 THEN
                match_score := match_score + 0.10;
                match_criteria := match_criteria || '{"platform": true}'::JSONB;
              END IF;
              
              -- Only insert if match score is above threshold and not already exists
              IF match_score >= 0.4 THEN
                BEGIN
                  INSERT INTO products_alternatives_products (
                    product_id,
                    alternative_product_id,
                    relationship_type,
                    match_score,
                    match_criteria,
                    display_order
                  ) VALUES (
                    product_uuid,
                    similar_product.id,
                    'auto-suggested',
                    match_score,
                    match_criteria,
                    suggested_count
                  ) ON CONFLICT (product_id, alternative_product_id) DO NOTHING;
                  
                  suggested_count := suggested_count + 1;
                EXCEPTION WHEN OTHERS THEN
                  -- Skip if any error occurs
                  CONTINUE;
                END;
              END IF;
            END LOOP;
            
            RETURN suggested_count;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(autoSuggestFunction);

        // Create function to get alternatives for user view
        const getAlternativesFunction = `
          CREATE OR REPLACE FUNCTION get_alternative_products_for_user(
            product_uuid UUID,
            user_uuid UUID DEFAULT NULL
          )
          RETURNS TABLE (
            alternative_product_id UUID,
            relationship_type VARCHAR(20),
            match_score DECIMAL(3,2),
            is_user_confirmed BOOLEAN,
            is_user_rejected BOOLEAN,
            user_vote VARCHAR(10),
            upvotes INTEGER,
            downvotes INTEGER,
            display_order INTEGER,
            product_name VARCHAR(255),
            product_slug VARCHAR(120),
            product_logo JSONB,
            product_description TEXT,
            product_rating DECIMAL(3,2),
            product_price DECIMAL(10,2),
            price_comparison VARCHAR(20),
            is_manual_assignment BOOLEAN,
            assigned_by_name VARCHAR(100)
          ) AS $$
          BEGIN
            RETURN QUERY
            SELECT 
              ap.alternative_product_id,
              ap.relationship_type,
              ap.match_score,
              ap.is_user_confirmed,
              ap.is_user_rejected,
              ap.user_vote,
              ap.upvotes,
              ap.downvotes,
              ap.display_order,
              p.name as product_name,
              p.slug as product_slug,
              p.logo as product_logo,
              p.description as product_description,
              p.rating as product_rating,
              p.price as product_price,
              ap.price_comparison,
              (ap.creator_id IS NOT NULL) as is_manual_assignment,
              u.user_full_name as assigned_by_name
            FROM products_alternatives_products ap
            JOIN products p ON ap.alternative_product_id = p.id
            LEFT JOIN accounts u ON ap.creator_id = u.id
            WHERE ap.product_id = product_uuid
              AND ap.status = 'active'
              AND p.status = 'active'
              AND (
                -- Show all alternatives if no user specified
                user_uuid IS NULL 
                OR 
                -- For specific user, show both manual assignments and auto-suggested (unless rejected)
                (ap.creator_id = user_uuid AND ap.relationship_type = 'manual')
                OR
                (ap.relationship_type = 'auto-suggested' AND ap.is_user_rejected = false)
              )
            ORDER BY 
              -- First show manual assignments by the user
              CASE WHEN ap.creator_id = user_uuid THEN 0 ELSE 1 END,
              -- Then show confirmed alternatives
              CASE WHEN ap.is_user_confirmed = true THEN 0 ELSE 1 END,
              -- Then by match score (for auto-suggested)
              ap.match_score DESC,
              -- Then by popularity
              (ap.upvotes - ap.downvotes) DESC,
              -- Finally by display order
              ap.display_order;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(getAlternativesFunction);

        // Create function for user to add manual alternative
        const addManualAlternativeFunction = `
          CREATE OR REPLACE FUNCTION add_manual_alternative(
            product_uuid UUID,
            alternative_product_uuid UUID,
            user_uuid UUID,
            p_comparison_notes TEXT DEFAULT NULL,
            p_pros TEXT[] DEFAULT '{}',
            p_cons TEXT[] DEFAULT '{}',
            p_price_comparison VARCHAR(20) DEFAULT NULL
          )
          RETURNS JSON AS $$
          DECLARE
            existing_record RECORD;
            result JSON;
          BEGIN
            -- Check if alternative already exists
            SELECT * INTO existing_record
            FROM products_alternatives_products
            WHERE product_id = product_uuid 
              AND alternative_product_id = alternative_product_uuid;
            
            IF FOUND THEN
              -- Update existing record to be manual
              UPDATE products_alternatives_products
              SET 
                creator_id = user_uuid,
                relationship_type = 'manual',
                comparison_notes = COALESCE(p_comparison_notes, comparison_notes),
                pros = COALESCE(p_pros, pros),
                cons = COALESCE(p_cons, cons),
                price_comparison = COALESCE(p_price_comparison, price_comparison),
                is_user_confirmed = true,
                confirmed_at = CURRENT_TIMESTAMP,
                updated_at = CURRENT_TIMESTAMP
              WHERE id = existing_record.id
              RETURNING id INTO existing_record.id;
              
              result := json_build_object(
                'success', true,
                'message', 'Alternative updated to manual assignment',
                'id', existing_record.id,
                'action', 'updated'
              );
            ELSE
              -- Insert new manual alternative
              INSERT INTO products_alternatives_products (
                product_id,
                alternative_product_id,
                creator_id,
                relationship_type,
                comparison_notes,
                pros,
                cons,
                price_comparison,
                is_user_confirmed,
                confirmed_at
              ) VALUES (
                product_uuid,
                alternative_product_uuid,
                user_uuid,
                'manual',
                p_comparison_notes,
                p_pros,
                p_cons,
                p_price_comparison,
                true,
                CURRENT_TIMESTAMP
              ) RETURNING id INTO existing_record.id;
              
              result := json_build_object(
                'success', true,
                'message', 'Manual alternative added successfully',
                'id', existing_record.id,
                'action', 'created'
              );
            END IF;
            
            RETURN result;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(addManualAlternativeFunction);

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