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

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

         -- References
         product_id UUID NOT NULL,
         suggestor_id UUID NOT NULL,
         
         -- Feature suggestion details
         title VARCHAR(200) NOT NULL CHECK (char_length(trim(title)) > 0),
         description TEXT NOT NULL CHECK (char_length(trim(description)) > 0),
         
         -- Categorization
         tags JSONB DEFAULT '[]'::JSONB,
         
         -- Voting and engagement
         upvotes INTEGER DEFAULT 1 CHECK (upvotes >= 0),
         downvotes INTEGER DEFAULT 0 CHECK (downvotes >= 0),
         view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
         unique_voters UUID[] DEFAULT '{}'::UUID[],
         
         -- Status tracking
         status VARCHAR(20) DEFAULT 'suggested' 
           CHECK (status IN (
             'suggested', 'under-review', 'planned', 
             'in-development', 'completed', 'declined', 'duplicate'
           )),
         
         -- Implementation details (filled by product team)
         estimated_release_date DATE,
         assigned_to UUID,
         development_notes TEXT,
         
         -- Analytics
         trending_score DECIMAL(5,2) DEFAULT 0.00,
         engagement_score DECIMAL(5,2) DEFAULT 0.00,
         last_interaction_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         
         -- Metadata
         ip_address INET,
         user_agent TEXT,
         
         -- Timestamps
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         status_changed_at TIMESTAMP,
         
         -- Foreign key constraints
         FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (suggestor_id) REFERENCES accounts(id) ON DELETE CASCADE,
         FOREIGN KEY (assigned_to) REFERENCES accounts(id) ON DELETE SET NULL,
         
         -- Constraints
         CONSTRAINT valid_trending_score CHECK (trending_score >= 0),
         CONSTRAINT valid_engagement_score CHECK (engagement_score >= 0),
         CONSTRAINT valid_upvotes CHECK (upvotes >= 0),
         CONSTRAINT valid_downvotes CHECK (downvotes >= 0),
         CONSTRAINT valid_view_count CHECK (view_count >= 0)
        );`;

    await database.query(tableQuery);

    // Index creation for better performance
    const indexes = [
      // Foreign key indexes
      `CREATE INDEX IF NOT EXISTS idx_suggestions_product_id ON products_suggested_features(product_id);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_suggestor_id ON products_suggested_features(suggestor_id);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_assigned_to ON products_suggested_features(assigned_to);`,

      // For status and workflow management
      `CREATE INDEX IF NOT EXISTS idx_suggestions_status ON products_suggested_features(status);`,

      // For voting and popularity
      `CREATE INDEX IF NOT EXISTS idx_suggestions_upvotes ON products_suggested_features(upvotes DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_score ON products_suggested_features((upvotes - downvotes) DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_trending_score ON products_suggested_features(trending_score DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_engagement_score ON products_suggested_features(engagement_score DESC);`,

      // For date-based queries
      `CREATE INDEX IF NOT EXISTS idx_suggestions_created_at ON products_suggested_features(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_updated_at ON products_suggested_features(updated_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_last_interaction ON products_suggested_features(last_interaction_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_estimated_release ON products_suggested_features(estimated_release_date) WHERE estimated_release_date IS NOT NULL;`,

      // For full-text search
      `CREATE INDEX IF NOT EXISTS idx_suggestions_search_title ON products_suggested_features USING gin(to_tsvector('english', title));`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_search_description ON products_suggested_features USING gin(to_tsvector('english', description));`,

      // For array field searches
      `CREATE INDEX IF NOT EXISTS idx_suggestions_tags ON products_suggested_features USING gin(tags);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_unique_voters ON products_suggested_features USING gin(unique_voters);`,

      // Composite indexes for common queries
      `CREATE INDEX IF NOT EXISTS idx_suggestions_product_status_score ON products_suggested_features(product_id, status, (upvotes - downvotes) DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_product_status_date ON products_suggested_features(product_id, status, created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_suggestions_suggestor_product ON products_suggested_features(suggestor_id, product_id, created_at DESC);`,

      // For analytics and reporting
      `CREATE INDEX IF NOT EXISTS idx_suggestions_view_count ON products_suggested_features(view_count DESC);`,
    ];

    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;
        }
      }
    }

    // Drop existing functions and triggers first to avoid conflicts
    const cleanupQueries = [
      `DROP TRIGGER IF EXISTS set_suggestions_updated_at ON products_suggested_features;`,
      `DROP TRIGGER IF EXISTS prevent_duplicate_votes ON products_suggested_features;`,
      `DROP FUNCTION IF EXISTS update_suggestions_updated_at();`,
      `DROP FUNCTION IF EXISTS prevent_duplicate_voting();`,
      `DROP FUNCTION IF EXISTS calculate_trending_score();`,
      `DROP FUNCTION IF EXISTS vote_feature_suggestion();`,
    ];

    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_suggestions_updated_at()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            NEW.last_interaction_at = CURRENT_TIMESTAMP;
            
            -- Update status_changed_at when status changes
            IF NEW.status != OLD.status THEN
              NEW.status_changed_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_suggestions_updated_at
          BEFORE UPDATE ON products_suggested_features
          FOR EACH ROW
          EXECUTE FUNCTION update_suggestions_updated_at();
        `;

    await database.query(updateTrigger);

    // Create function to calculate trending score
    const calculateScoreFunction = `
          CREATE OR REPLACE FUNCTION calculate_trending_score(
            p_upvotes INTEGER,
            p_downvotes INTEGER,
            p_view_count INTEGER,
            p_created_at TIMESTAMP
          )
          RETURNS DECIMAL(5,2) AS $$
          DECLARE
            vote_score DECIMAL(5,2);
            engagement_score DECIMAL(5,2);
            recency_score DECIMAL(5,2);
            total_score DECIMAL(5,2);
          BEGIN
            -- Calculate vote score (weighted more heavily)
            vote_score := (p_upvotes * 2.0 - p_downvotes) / 100.0;
            
            
            
            -- Calculate recency score (newer suggestions get a boost)
            recency_score := GREATEST(0, 10.0 - EXTRACT(DAY FROM NOW() - p_created_at)) / 10.0;
            
            -- Total score calculation
            total_score := (vote_score * 0.6 + engagement_score * 0.3 + recency_score * 0.1) * 100.0;
            
            RETURN ROUND(GREATEST(0, total_score), 2);
          END;
          $$ LANGUAGE plpgsql IMMUTABLE;
        `;

    await database.query(calculateScoreFunction);

    // Create function to handle voting with duplicate prevention
    const voteFunction = `
          CREATE OR REPLACE FUNCTION vote_feature_suggestion(
            suggestion_id UUID,
            voter_id UUID,
            vote_type VARCHAR(10)
          )
          RETURNS JSON AS $$
          DECLARE
            suggestion RECORD;
            has_voted BOOLEAN;
            result JSON;
          BEGIN
            -- Get current suggestion
            SELECT * INTO suggestion 
            FROM products_suggested_features 
            WHERE id = suggestion_id;
            
            IF NOT FOUND THEN
              RETURN json_build_object('success', false, 'message', 'Suggestion not found');
            END IF;
            
            -- Check if user has already voted
            has_voted := voter_id = ANY(suggestion.unique_voters);
            
            IF vote_type = 'upvote' THEN
              IF has_voted THEN
                -- User already voted, remove their vote
                UPDATE products_suggested_features 
                SET 
                  upvotes = upvotes - 1,
                  unique_voters = array_remove(unique_voters, voter_id),
                  trending_score = calculate_trending_score(
                    upvotes - 1, 
                    downvotes, 
                    view_count, 
                    created_at
                  )
                WHERE id = suggestion_id
                RETURNING upvotes INTO suggestion.upvotes;
                
                result := json_build_object(
                  'success', true, 
                  'message', 'Upvote removed',
                  'upvotes', suggestion.upvotes,
                  'action', 'removed'
                );
              ELSE
                -- Add new upvote
                UPDATE products_suggested_features 
                SET 
                  upvotes = upvotes + 1,
                  unique_voters = array_append(unique_voters, voter_id),
                  trending_score = calculate_trending_score(
                    upvotes + 1, 
                    downvotes, 
                    view_count, 
                    created_at
                  )
                WHERE id = suggestion_id
                RETURNING upvotes INTO suggestion.upvotes;
                
                result := json_build_object(
                  'success', true, 
                  'message', 'Upvote added',
                  'upvotes', suggestion.upvotes,
                  'action', 'added'
                );
              END IF;
              
            ELSIF vote_type = 'downvote' THEN
              IF has_voted THEN
                -- User already voted, remove their vote
                UPDATE products_suggested_features 
                SET 
                  downvotes = downvotes - 1,
                  unique_voters = array_remove(unique_voters, voter_id),
                  trending_score = calculate_trending_score(
                    upvotes, 
                    downvotes - 1, 
                    view_count, 
                    created_at
                  )
                WHERE id = suggestion_id
                RETURNING downvotes INTO suggestion.downvotes;
                
                result := json_build_object(
                  'success', true, 
                  'message', 'Downvote removed',
                  'downvotes', suggestion.downvotes,
                  'action', 'removed'
                );
              ELSE
                -- Add new downvote
                UPDATE products_suggested_features 
                SET 
                  downvotes = downvotes + 1,
                  unique_voters = array_append(unique_voters, voter_id),
                  trending_score = calculate_trending_score(
                    upvotes, 
                    downvotes + 1, 
                    view_count, 
                    created_at
                  )
                WHERE id = suggestion_id
                RETURNING downvotes INTO suggestion.downvotes;
                
                result := json_build_object(
                  'success', true, 
                  'message', 'Downvote added',
                  'downvotes', suggestion.downvotes,
                  'action', 'added'
                );
              END IF;
              
            ELSE
              RETURN json_build_object('success', false, 'message', 'Invalid vote type');
            END IF;
            
            RETURN result;
          END;
          $$ LANGUAGE plpgsql;
        `;

    await database.query(voteFunction);

    // Create function to increment view count
    const viewCountFunction = `
          CREATE OR REPLACE FUNCTION increment_suggestion_view_count(suggestion_id UUID)
          RETURNS VOID AS $$
          BEGIN
            UPDATE products_suggested_features 
            SET 
              view_count = view_count + 1,
              last_interaction_at = CURRENT_TIMESTAMP,
              engagement_score = calculate_trending_score(
                upvotes, 
                downvotes, 
                view_count + 1, 
                created_at
              )
            WHERE id = suggestion_id;
          END;
          $$ LANGUAGE plpgsql;
        `;

    await database.query(viewCountFunction);

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