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

export async function createProductsCommentsTable() {
  try {
    const tableQuery = `
    CREATE TABLE IF NOT EXISTS products_comments (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      
      -- Hierarchical comment structure
      parent_id UUID,
      
      -- References
      creator_id UUID NOT NULL,
      product_id UUID NOT NULL,
      
      -- Comment content
      comment TEXT NOT NULL CHECK (char_length(trim(comment)) > 0),
      
      -- Engagement metrics
      upvotes INTEGER DEFAULT 0 CHECK (upvotes >= 0),
      downvotes INTEGER DEFAULT 0 CHECK (downvotes >= 0),
      replies_count INTEGER DEFAULT 0 CHECK (replies_count >= 0),
      
      -- Status and moderation
      status VARCHAR(20) DEFAULT 'active' 
        CHECK (status IN ('active', 'hidden', 'deleted', 'pending', 'spam')),
      
      is_edited BOOLEAN DEFAULT false,
      is_pinned BOOLEAN DEFAULT false,
      is_owner_reply BOOLEAN DEFAULT false,
      
      -- Metadata
      user_ip INET,
      user_agent TEXT,
      
      -- Edit history (can store as JSON array of previous versions)
      edit_history JSONB DEFAULT '[]'::JSONB,
      
      -- Reaction counts (for emoji reactions)
      reactions JSONB DEFAULT '{}'::JSONB,
      
      -- Mentioned users
      mentioned_users UUID[] DEFAULT '{}',
      
      -- Timestamps
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      last_interaction_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      
      -- Foreign key constraints
      FOREIGN KEY (parent_id) REFERENCES products_comments(id) ON DELETE CASCADE,
      FOREIGN KEY (creator_id) REFERENCES accounts(id) ON DELETE CASCADE,
      FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
      
      -- Ensure a comment cannot be its own parent
      CONSTRAINT no_self_reference CHECK (id != parent_id OR parent_id IS NULL)
    );`;

    await database.query(tableQuery);

    // Index creation for better performance
    const indexes = [
      // Foreign key indexes
      `CREATE INDEX IF NOT EXISTS idx_comments_product_id ON products_comments(product_id);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_creator_id ON products_comments(creator_id);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_parent_id ON products_comments(parent_id) WHERE parent_id IS NOT NULL;`,

      // For hierarchical queries (nested comments)
      `CREATE INDEX IF NOT EXISTS idx_comments_product_parent ON products_comments(product_id, parent_id);`,

      // Sorting and filtering
      `CREATE INDEX IF NOT EXISTS idx_comments_created_at ON products_comments(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_upvotes ON products_comments(upvotes DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_score ON products_comments((upvotes - downvotes) DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_status ON products_comments(status) WHERE status = 'active';`,
      `CREATE INDEX IF NOT EXISTS idx_comments_is_pinned ON products_comments(is_pinned) WHERE is_pinned = true;`,
      `CREATE INDEX IF NOT EXISTS idx_comments_is_owner_reply ON products_comments(is_owner_reply) WHERE is_owner_reply = true;`,

      // For reply count updates
      `CREATE INDEX IF NOT EXISTS idx_comments_reply_counts ON products_comments(parent_id) WHERE parent_id IS NOT NULL;`,

      // For recent activity
      `CREATE INDEX IF NOT EXISTS idx_comments_last_interaction ON products_comments(last_interaction_at DESC) WHERE status = 'active';`,

      // Full-text search on comments
      `CREATE INDEX IF NOT EXISTS idx_comments_search ON products_comments USING gin(to_tsvector('english', comment));`,

      // For mentioned users queries
      `CREATE INDEX IF NOT EXISTS idx_comments_mentioned_users ON products_comments USING gin(mentioned_users);`,

      // Composite indexes for common queries
      `CREATE INDEX IF NOT EXISTS idx_comments_product_active ON products_comments(product_id, created_at DESC) WHERE status = 'active';`,
      `CREATE INDEX IF NOT EXISTS idx_comments_product_creator ON products_comments(product_id, creator_id, created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_comments_creator_product ON products_comments(creator_id, product_id, created_at DESC);`,
    ];

    for (const indexQuery of indexes) {
      await database.query(indexQuery);
    }

    // Create function to update replies_count
    const repliesCountFunction = `
      CREATE OR REPLACE FUNCTION update_comment_replies_count()
      RETURNS TRIGGER AS $$
      BEGIN
        IF TG_OP = 'INSERT' AND NEW.parent_id IS NOT NULL THEN
          UPDATE products_comments 
          SET replies_count = replies_count + 1,
              last_interaction_at = CURRENT_TIMESTAMP
          WHERE id = NEW.parent_id;
        ELSIF TG_OP = 'DELETE' AND OLD.parent_id IS NOT NULL THEN
          UPDATE products_comments 
          SET replies_count = GREATEST(0, replies_count - 1)
          WHERE id = OLD.parent_id;
        END IF;
        RETURN NULL;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(repliesCountFunction);

    // Create trigger for replies count
    await database.query(`
      DROP TRIGGER IF EXISTS update_replies_count ON products_comments;
    `);

    await database.query(`
     CREATE TRIGGER update_replies_count
     AFTER INSERT OR DELETE ON products_comments
     FOR EACH ROW
     EXECUTE FUNCTION update_comment_replies_count();
    `);

    // Create function to automatically update updated_at
    const updateTriggerFunction = `
      CREATE OR REPLACE FUNCTION update_comments_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        NEW.last_interaction_at = CURRENT_TIMESTAMP;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(updateTriggerFunction);

    // Create trigger for automatic updated_at
    await database.query(`
      DROP TRIGGER IF EXISTS set_comments_updated_at ON products_comments;
    `);

    await database.query(`
      CREATE TRIGGER set_comments_updated_at
      BEFORE UPDATE ON products_comments
      FOR EACH ROW
      EXECUTE FUNCTION update_comments_updated_at();
    `);

    // Create function to validate parent comment belongs to same product
    const validateParentProductFunction = `
      CREATE OR REPLACE FUNCTION validate_parent_product()
      RETURNS TRIGGER AS $$
      DECLARE
        parent_product_id UUID;
      BEGIN
        IF NEW.parent_id IS NOT NULL THEN
          SELECT product_id INTO parent_product_id 
          FROM products_comments 
          WHERE id = NEW.parent_id;
          
          IF parent_product_id IS NULL THEN
            RAISE EXCEPTION 'Parent comment not found';
          END IF;
          
          IF parent_product_id != NEW.product_id THEN
            RAISE EXCEPTION 'Parent comment must belong to the same product';
          END IF;
        END IF;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(validateParentProductFunction);

    // Create trigger for parent product validation
    await database.query(`
      DROP TRIGGER IF EXISTS validate_parent_product_trigger ON products_comments;
    `);

    await database.query(`
      CREATE TRIGGER validate_parent_product_trigger
      BEFORE INSERT OR UPDATE ON products_comments
      FOR EACH ROW
      EXECUTE FUNCTION validate_parent_product();
    `);

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