import database from "../../config/db";
import { QueryResult } from "pg";

export interface FaqCreateData {
  product_id: string;
  question: string;
  answer: string;
  category: string;
  subcategory?: string;
  category_order?: number;
  faq_order?: number;
  tags?: string[];
  keywords?: string[];
  complexity?: "beginner" | "intermediate" | "advanced" | "expert";
  status?: string;
  is_featured?: boolean;
  is_pinned?: boolean;
  requires_login?: boolean;
  author_id?: string;
  documentation_url?: string;
  video_url?: string;
  related_faqs?: string[];
  meta_title?: string;
  meta_description?: string;
  meta_keywords?: string[];
  slug?: string;
}

export interface FaqUpdateData extends Partial<FaqCreateData> {
  helpful_count?: number;
  not_helpful_count?: number;
  view_count?: number;
  click_count?: number;
  last_updated_by?: string;
}

export interface FaqFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  category?: string;
  subcategory?: string;
  status?: string;
  is_featured?: boolean;
  is_pinned?: boolean;
  complexity?: string;
  requires_login?: boolean;
  search?: string;
  tags?: string[];
  author_id?: string;
  sort_by?: string;
  order?: "asc" | "desc";
}

export const productFaqService = {
  // Create new FAQ
  async createFaq(data: FaqCreateData) {
    const {
      product_id,
      question,
      answer,
      category,
      subcategory,
      category_order = 0,
      faq_order = 0,
      tags = [],
      keywords = [],
      complexity = "beginner",
      status = "draft",
      is_featured = false,
      is_pinned = false,
      requires_login = false,
      author_id,
      documentation_url,
      video_url,
      related_faqs = [],
      meta_title,
      meta_description,
      meta_keywords = [],
      slug,
    } = data;

    // Validation
    if (!product_id) {
      throw new Error("Product ID is required");
    }

    if (!question || question.trim().length < 3) {
      throw new Error("Question must be at least 3 characters");
    }

    if (!answer || answer.trim().length < 10) {
      throw new Error("Answer must be at least 10 characters");
    }

    if (!category || category.trim().length < 2) {
      throw new Error("Category is required");
    }

    // Check if product exists
    const productCheck = await database.query(
      `SELECT id FROM products WHERE id = $1 AND status = 'active'`,
      [product_id]
    );

    if (productCheck.rows.length === 0) {
      throw new Error("Product not found or not active");
    }

    // Check if author exists (if provided)
    if (author_id) {
      const authorCheck = await database.query(
        `SELECT id FROM accounts WHERE id = $1 AND is_active = true`,
        [author_id]
      );

      if (authorCheck.rows.length === 0) {
        throw new Error("Author not found");
      }
    }

    // Check if question already exists for this product
    const existingQuestion = await database.query(
      `SELECT id FROM products_faqs 
       WHERE product_id = $1 AND LOWER(TRIM(question)) = LOWER(TRIM($2))`,
      [product_id, question]
    );

    if (existingQuestion.rows.length > 0) {
      throw new Error("FAQ with this question already exists for this product");
    }

    // Generate slug if not provided
    let faqSlug = slug;
    if (!faqSlug) {
      const slugResult = await database.query(
        `SELECT generate_faq_slug($1) as slug`,
        [question]
      );
      faqSlug = slugResult.rows[0].slug;
    }

    // Set published_at if status is published
    let publishedAt = null;
    if (status === "published") {
      publishedAt = new Date();
    }

    const query = `
      INSERT INTO products_faqs (
        product_id, question, answer, category, subcategory,
        category_order, faq_order, tags, keywords, complexity,
        status, is_featured, is_pinned, requires_login, author_id,
        documentation_url, video_url, related_faqs, meta_title,
        meta_description, meta_keywords, slug, published_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23)
      RETURNING *;
    `;

    const values = [
      product_id,
      question.trim(),
      answer.trim(),
      category.trim(),
      subcategory?.trim(),
      category_order,
      faq_order,
      tags,
      keywords,
      complexity,
      status,
      is_featured,
      is_pinned,
      requires_login,
      author_id,
      documentation_url,
      video_url,
      related_faqs,
      meta_title,
      meta_description,
      meta_keywords,
      faqSlug,
      publishedAt,
    ];

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Get FAQ by ID
  async getFaqById(id: string, incrementViews: boolean = true) {
    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        author.username as author_username,
        author.display_name as author_display_name,
        author.avatar as author_avatar,
        updater.username as last_updated_by_username,
        updater.display_name as last_updated_by_display_name,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', rf.id,
              'question', rf.question,
              'slug', rf.slug
            )
          )
          FROM products_faqs rf
          WHERE rf.id = ANY(f.related_faqs)
          AND rf.status = 'published'
        ) as related_faqs_details,
        (
          SELECT COUNT(*) as total_faqs
          FROM products_faqs pf
          WHERE pf.product_id = f.product_id
          AND pf.status = 'published'
        ) as product_total_faqs
      FROM products_faqs f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts author ON f.author_id = author.id
      LEFT JOIN accounts updater ON f.last_updated_by = updater.id
      WHERE f.id = $1;
    `;

    const result = await database.query(query, [id]);
    const faq = result.rows[0];

    if (faq && incrementViews) {
      await this.incrementViewCount(id);
      faq.view_count += 1;
    }

    return faq || null;
  },

  // Get FAQ by slug
  async getFaqBySlug(slug: string, incrementViews: boolean = true) {
    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        author.username as author_username,
        author.display_name as author_display_name
      FROM products_faqs f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts author ON f.author_id = author.id
      WHERE f.slug = $1;
    `;

    const result = await database.query(query, [slug]);
    const faq = result.rows[0];

    if (faq && incrementViews) {
      await this.incrementViewCount(faq.id);
      faq.view_count += 1;
    }

    return faq || null;
  },

  // Get all FAQs with filters
  async getAllFaqs(filters: FaqFilters = {}) {
    const {
      page = 1,
      limit = 20,
      product_id,
      category,
      subcategory,
      status = "published",
      is_featured,
      is_pinned,
      complexity,
      requires_login,
      search,
      tags = [],
      author_id,
      sort_by = "faq_order",
      order = "asc",
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    // Status filter
    if (status) {
      paramCount++;
      whereClauses.push(`f.status = $${paramCount}`);
      values.push(status);
    }

    // Product filter
    if (product_id) {
      paramCount++;
      whereClauses.push(`f.product_id = $${paramCount}`);
      values.push(product_id);
    }

    // Category filter
    if (category) {
      paramCount++;
      whereClauses.push(`f.category = $${paramCount}`);
      values.push(category);
    }

    // Subcategory filter
    if (subcategory) {
      paramCount++;
      whereClauses.push(`f.subcategory = $${paramCount}`);
      values.push(subcategory);
    }

    // Featured filter
    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`f.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

    // Pinned filter
    if (is_pinned !== undefined) {
      paramCount++;
      whereClauses.push(`f.is_pinned = $${paramCount}`);
      values.push(is_pinned);
    }

    // Complexity filter
    if (complexity) {
      paramCount++;
      whereClauses.push(`f.complexity = $${paramCount}`);
      values.push(complexity);
    }

    // Requires login filter
    if (requires_login !== undefined) {
      paramCount++;
      whereClauses.push(`f.requires_login = $${paramCount}`);
      values.push(requires_login);
    }

    // Author filter
    if (author_id) {
      paramCount++;
      whereClauses.push(`f.author_id = $${paramCount}`);
      values.push(author_id);
    }

    // Search filter
    if (search) {
      paramCount++;
      whereClauses.push(`
        (f.question ILIKE $${paramCount} OR 
         f.answer ILIKE $${paramCount} OR
         f.category ILIKE $${paramCount} OR
         f.subcategory ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

    // Tags filter
    if (tags.length > 0) {
      paramCount++;
      whereClauses.push(`f.tags @> $${paramCount}::text[]`);
      values.push(tags);
    }

    const whereClause = whereClauses.length > 0 
      ? `WHERE ${whereClauses.join(" AND ")}` 
      : "";

    // Validate sort field
    const validSortFields = [
      "faq_order", "category_order", "created_at", "updated_at", 
      "published_at", "helpful_count", "view_count", "click_count"
    ];
    const sortField = validSortFields.includes(sort_by) ? sort_by : "faq_order";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total FAQs
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_faqs f
      ${whereClause};
    `;

    const countResult = await database.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Get paginated FAQs
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        author.username as author_username,
        author.display_name as author_display_name,
        (f.helpful_count - f.not_helpful_count) as helpfulness_score
      FROM products_faqs f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts author ON f.author_id = author.id
      ${whereClause}
      ORDER BY 
        f.is_pinned DESC,
        f.is_featured DESC,
        f.${sortField} ${sortOrder},
        f.category_order ASC,
        f.faq_order ASC
      LIMIT $${paramCount + 1} OFFSET $${paramCount + 2};
    `;

    const dataValues = [...values, limit, offset];
    const dataResult = await database.query(dataQuery, dataValues);

    return {
      data: dataResult.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
        has_next: page * limit < total,
        has_prev: page > 1,
      },
    };
  },

  // Get FAQs by product
  async getProductFaqs(productId: string, filters: FaqFilters = {}) {
    return this.getAllFaqs({
      ...filters,
      product_id: productId,
    });
  },

  // Get FAQ categories for a product
  async getProductFaqCategories(productId: string) {
    const query = `
      SELECT 
        category,
        subcategory,
        COUNT(*) as faq_count,
        JSON_AGG(
          JSON_BUILD_OBJECT(
            'id', id,
            'question', question,
            'slug', slug,
            'is_featured', is_featured,
            'is_pinned', is_pinned,
            'complexity', complexity,
            'helpful_count', helpful_count,
            'view_count', view_count
          ) ORDER BY faq_order ASC, created_at ASC
        ) as faqs
      FROM products_faqs
      WHERE product_id = $1 AND status = 'published'
      GROUP BY category, subcategory
      ORDER BY MIN(category_order) ASC, category, subcategory;
    `;

    const result = await database.query(query, [productId]);
    return result.rows;
  },

  // Update FAQ
  async updateFaq(id: string, data: FaqUpdateData, userId?: string) {
    // Check if FAQ exists
    const existingFaq = await this.getFaqById(id, false);
    if (!existingFaq) {
      throw new Error("FAQ not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkFaqPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to update this FAQ");
      }

      // Set last_updated_by if user is editing
      data.last_updated_by = userId;
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = [
      "question", "answer", "category", "subcategory", "category_order", 
      "faq_order", "tags", "keywords", "complexity", "status", "is_featured", 
      "is_pinned", "requires_login", "documentation_url", "video_url", 
      "related_faqs", "meta_title", "meta_description", "meta_keywords", 
      "slug", "helpful_count", "not_helpful_count", "view_count", 
      "click_count", "last_updated_by"
    ];

    fields.forEach((field) => {
      if (data[field as keyof FaqUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof FaqUpdateData]);
      }
    });

    // Handle status change to published
    if (data.status === "published" && existingFaq.status !== "published") {
      paramCount++;
      setClauses.push(`published_at = CURRENT_TIMESTAMP`);
    }

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    paramCount++;
    setClauses.push(`updated_at = CURRENT_TIMESTAMP`);
    values.push(id);

    const query = `
      UPDATE products_faqs
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Delete FAQ
  async deleteFaq(id: string, userId?: string) {
    // Check if FAQ exists
    const existingFaq = await this.getFaqById(id, false);
    if (!existingFaq) {
      throw new Error("FAQ not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkFaqPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to delete this FAQ");
      }
    }

    const query = `
      DELETE FROM products_faqs
      WHERE id = $1
      RETURNING *;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0];
  },

  // Mark FAQ as helpful
  async markFaqHelpful(faqId: string, userId: string, helpful: boolean = true) {
    // Check if FAQ exists and is published
    const faq = await database.query(
      `SELECT id FROM products_faqs WHERE id = $1 AND status = 'published'`,
      [faqId]
    );

    if (faq.rows.length === 0) {
      throw new Error("FAQ not found or not published");
    }

    // Use the database function
    const query = `
      SELECT mark_faq_helpful($1, $2) as count;
    `;

    const result = await database.query(query, [faqId, helpful]);
    const count = parseInt(result.rows[0].count);

    return { 
      count, 
      type: helpful ? "helpful_count" : "not_helpful_count" 
    };
  },

  // Increment view count
  async incrementViewCount(faqId: string) {
    const query = `
      SELECT increment_faq_view_count($1);
    `;

    await database.query(query, [faqId]);
  },

  // Increment click count
  async incrementClickCount(faqId: string) {
    const query = `
      SELECT increment_faq_click_count($1);
    `;

    await database.query(query, [faqId]);
  },

  // Get FAQ statistics for a product
  async getFaqStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_faqs,
        COUNT(CASE WHEN status = 'published' THEN 1 END) as published_faqs,
        COUNT(CASE WHEN status = 'draft' THEN 1 END) as draft_faqs,
        COUNT(CASE WHEN is_featured = true THEN 1 END) as featured_faqs,
        COUNT(CASE WHEN is_pinned = true THEN 1 END) as pinned_faqs,
        COUNT(DISTINCT category) as total_categories,
        COUNT(DISTINCT subcategory) as total_subcategories,
        SUM(helpful_count) as total_helpful,
        SUM(not_helpful_count) as total_not_helpful,
        SUM(view_count) as total_views,
        SUM(click_count) as total_clicks,
        AVG(EXTRACT(EPOCH FROM (updated_at - created_at))/3600)::DECIMAL(5,2) as avg_update_hours
      FROM products_faqs
      WHERE product_id = $1;
    `;

    const result = await database.query(query, [productId]);
    return result.rows[0];
  },

  // Get popular FAQs
  async getPopularFaqs(productId: string, limit: number = 10) {
    const query = `
      SELECT 
        f.*,
        (f.helpful_count - f.not_helpful_count) as helpfulness_score
      FROM products_faqs f
      WHERE f.product_id = $1 
        AND f.status = 'published'
      ORDER BY helpfulness_score DESC, f.view_count DESC
      LIMIT $2;
    `;

    const result = await database.query(query, [productId, limit]);
    return result.rows;
  },

  // Search FAQs
  async searchFaqs(queryText: string, filters: any = {}) {
    const { product_id, category, limit = 20 } = filters;

    const whereClauses: string[] = [
      `f.status = 'published'`,
      `(
        f.question ILIKE $1 OR 
        f.answer ILIKE $1 OR
        f.category ILIKE $1 OR
        f.subcategory ILIKE $1 OR
        EXISTS (SELECT 1 FROM unnest(f.tags) tag WHERE tag ILIKE $1)
      )`
    ];

    const values: any[] = [`%${queryText}%`];
    let paramCount = 1;

    if (product_id) {
      paramCount++;
      whereClauses.push(`f.product_id = $${paramCount}`);
      values.push(product_id);
    }

    if (category) {
      paramCount++;
      whereClauses.push(`f.category = $${paramCount}`);
      values.push(category);
    }

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        ts_rank(
          setweight(to_tsvector('english', f.question), 'A') || 
          setweight(to_tsvector('english', f.answer), 'B') || 
          setweight(to_tsvector('english', COALESCE(f.category, '')), 'C'),
          plainto_tsquery('english', $1)
        ) as rank
      FROM products_faqs f
      LEFT JOIN products product ON f.product_id = product.id
      ${whereClause}
      ORDER BY rank DESC, (f.helpful_count - f.not_helpful_count) DESC
      LIMIT $${paramCount + 1};
    `;

    values.push(limit);
    const result = await database.query(query, values);
    return result.rows;
  },

  // Check if user has permission to modify FAQ
  async checkFaqPermission(faqId: string, userId: string): Promise<boolean> {
    // Check if user is FAQ author
    const faqQuery = await database.query(
      `SELECT author_id, product_id FROM products_faqs WHERE id = $1`,
      [faqId]
    );

    if (faqQuery.rows.length === 0) {
      return false;
    }

    const faq = faqQuery.rows[0];
    
    if (faq.author_id === userId) {
      return true;
    }

    // Check if user is product owner
    const productQuery = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [faq.product_id]
    );

    if (productQuery.rows.length > 0 && productQuery.rows[0].creator_id === userId) {
      return true;
    }

    // Check if user is admin
    const adminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = $1 
          AND ra.is_active = true
          AND (r.name = 'admin' OR r.name = 'super_admin')
      ) as is_admin`,
      [userId]
    );

    return adminCheck.rows[0]?.is_admin || false;
  },

  // Reorder FAQs within a category
  async reorderFaqs(productId: string, faqIds: string[]) {
    if (!Array.isArray(faqIds) || faqIds.length === 0) {
      throw new Error("FAQ IDs array is required");
    }

    const updates = [];
    for (let i = 0; i < faqIds.length; i++) {
      const updateQuery = `
        UPDATE products_faqs
        SET faq_order = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2 AND product_id = $3
        RETURNING id, question, faq_order;
      `;

      const result = await database.query(updateQuery, [i, faqIds[i], productId]);
      if (result.rows.length > 0) {
        updates.push(result.rows[0]);
      }
    }

    return updates;
  },

  // Bulk update FAQs
  async bulkUpdateFaqs(faqIds: string[], data: Partial<FaqUpdateData>) {
    if (!Array.isArray(faqIds) || faqIds.length === 0) {
      throw new Error("FAQ IDs array is required");
    }

    if (faqIds.length > 100) {
      throw new Error("Cannot update more than 100 FAQs at once");
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = ["status", "category", "subcategory", "is_featured", "is_pinned"];

    fields.forEach((field) => {
      if (data[field as keyof FaqUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof FaqUpdateData]);
      }
    });

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    // Add updated_at
    setClauses.push("updated_at = CURRENT_TIMESTAMP");

    // Create placeholders for FAQ IDs
    const idPlaceholders = faqIds.map((_, index) => 
      `$${paramCount + index + 1}`
    ).join(", ");

    const query = `
      UPDATE products_faqs
      SET ${setClauses.join(", ")}
      WHERE id IN (${idPlaceholders})
      RETURNING id, question, status;
    `;

    const allValues = [...values, ...faqIds];
    const result = await database.query(query, allValues);
    return result.rows;
  },

  // Get related FAQs
  async getRelatedFaqs(faqId: string, limit: number = 5) {
    const query = `
      WITH target_faq AS (
        SELECT product_id, tags, category, subcategory
        FROM products_faqs
        WHERE id = $1 AND status = 'published'
      )
      SELECT 
        f.*,
        (
          -- Calculate relevance score
          (CASE WHEN f.category = tf.category THEN 3 ELSE 0 END) +
          (CASE WHEN f.subcategory = tf.subcategory THEN 2 ELSE 0 END) +
          (array_length(f.tags & tf.tags, 1) * 1)
        ) as relevance_score
      FROM products_faqs f, target_faq tf
      WHERE f.id != $1
        AND f.product_id = tf.product_id
        AND f.status = 'published'
      ORDER BY relevance_score DESC, f.helpful_count DESC
      LIMIT $2;
    `;

    const result = await database.query(query, [faqId, limit]);
    return result.rows;
  },

  // Get FAQ by category
  async getFaqsByCategory(productId: string, category: string, subcategory?: string) {
    const whereClauses: string[] = [
      `product_id = $1`,
      `category = $2`,
      `status = 'published'`
    ];

    const values: any[] = [productId, category];
    let paramCount = 2;

    if (subcategory) {
      paramCount++;
      whereClauses.push(`subcategory = $${paramCount}`);
      values.push(subcategory);
    }

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    const query = `
      SELECT *
      FROM products_faqs
      ${whereClause}
      ORDER BY faq_order ASC, created_at ASC;
    `;

    const result = await database.query(query, values);
    return result.rows;
  },
};