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

export interface CategoryCreateData {
  name: string;
  slug?: string;
  description?: string;
  icon?: string;
  cover_image?: any;
  parent_id?: string;
  sort_order?: number;
  meta_title?: string;
  meta_description?: string;
  meta_keywords?: string[];
  is_active?: boolean;
  is_featured?: boolean;
}

export interface CategoryUpdateData extends Partial<CategoryCreateData> {}

export const productCategoryService = {
  // Create new category
  async createCategory(data: CategoryCreateData) {
    const {
      name,
      slug,
      description,
      icon,
      cover_image,
      parent_id,
      sort_order,
      meta_title,
      meta_description,
      meta_keywords,
      is_active = true,
      is_featured = false,
    } = data;

    // Generate slug if not provided
    const categorySlug = slug || this.generateSlug(name);

    const query = `
      INSERT INTO products_categories (
        name, slug, description, icon, cover_image, 
        parent_id, sort_order, meta_title, meta_description, 
        meta_keywords, is_active, is_featured
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
      RETURNING *;
    `;

    const values = [
      name,
      categorySlug,
      description,
      icon,
      cover_image,
      parent_id,
      sort_order || 0,
      meta_title,
      meta_description,
      meta_keywords || [],
      is_active,
      is_featured,
    ];

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

  // Get category by ID
  async getCategoryById(id: string) {
    const query = `
      SELECT 
        c.*,
        parent.name as parent_name,
        parent.slug as parent_slug,
        COUNT(p.id) as total_products
      FROM products_categories c
      LEFT JOIN products_categories parent ON c.parent_id = parent.id
      LEFT JOIN products p ON p.category_id = c.id AND p.status = 'active'
      WHERE c.id = $1
      GROUP BY c.id, parent.name, parent.slug;
    `;

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

  // Get category by slug
  async getCategoryBySlug(slug: string) {
    const query = `
      SELECT 
        c.*,
        parent.name as parent_name,
        parent.slug as parent_slug,
        COUNT(p.id) as total_products
      FROM products_categories c
      LEFT JOIN products_categories parent ON c.parent_id = parent.id
      LEFT JOIN products p ON p.category_id = c.id AND p.status = 'active'
      WHERE c.slug = $1
      GROUP BY c.id, parent.name, parent.slug;
    `;

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

  // Get all categories with pagination
  async getAllCategories(filters: {
    page?: number;
    limit?: number;
    is_active?: boolean;
    is_featured?: boolean;
    parent_id?: string;
    search?: string;
    sort_by?: string;
    order?: "asc" | "desc";
  }) {
    const {
      page = 1,
      limit = 20,
      is_active,
      is_featured,
      parent_id,
      search,
      sort_by = "sort_order",
      order = "asc",
    } = filters;

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

    if (is_active !== undefined) {
      paramCount++;
      whereClauses.push(`c.is_active = $${paramCount}`);
      values.push(is_active);
    }

    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`c.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

    if (parent_id) {
      paramCount++;
      whereClauses.push(`c.parent_id = $${paramCount}`);
      values.push(parent_id);
    } else if (parent_id === null) {
      whereClauses.push(`c.parent_id IS NULL`);
    }

    if (search) {
      paramCount++;
      whereClauses.push(`
        (c.name ILIKE $${paramCount} OR 
         c.description ILIKE $${paramCount} OR 
         c.meta_keywords::text ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

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

    // Validate sort_by field
    const validSortFields = [
      "name",
      "slug",
      "sort_order",
      "product_count",
      "view_count",
      "created_at",
      "updated_at",
    ];
    const sortField = validSortFields.includes(sort_by)
      ? sort_by
      : "sort_order";

    // Count total records
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_categories c
      ${whereClause};
    `;

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

    // Get paginated data
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        c.*,
        parent.name as parent_name,
        parent.slug as parent_slug,
        COUNT(p.id) as total_products
      FROM products_categories c
      LEFT JOIN products_categories parent ON c.parent_id = parent.id
      LEFT JOIN products p ON p.category_id = c.id AND p.status = 'active'
      ${whereClause}
      GROUP BY c.id, parent.name, parent.slug
      ORDER BY c.${sortField} ${order}
      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,
      },
    };
  },

  // Update category
  async updateCategory(id: string, data: CategoryUpdateData) {
    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = [
      "name",
      "slug",
      "description",
      "icon",
      "cover_image",
      "parent_id",
      "sort_order",
      "meta_title",
      "meta_description",
      "meta_keywords",
      "is_active",
      "is_featured",
    ];

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

    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_categories
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

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

  // Delete category (soft delete by deactivating)
  async deleteCategory(id: string) {
    // Check if category has products
    const productCheck = await database.query(
      `SELECT COUNT(*) as product_count FROM products WHERE category_id = $1 AND status != 'archived'`,
      [id],
    );

    if (parseInt(productCheck.rows[0].product_count) > 0) {
      throw new Error("Cannot delete category with active products");
    }

    // Check if category has sub-categories
    const subCategoryCheck = await database.query(
      `SELECT COUNT(*) as subcategory_count FROM products_categories WHERE parent_id = $1 AND is_active = true`,
      [id],
    );

    if (parseInt(subCategoryCheck.rows[0].subcategory_count) > 0) {
      throw new Error("Cannot delete category with active sub-categories");
    }

    const query = `
      UPDATE products_categories
      SET is_active = false, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Get category tree/hierarchy
  async getCategoryTree() {
    const query = `
      WITH RECURSIVE category_tree AS (
        -- Anchor member: top-level categories
        SELECT 
          id,
          name,
          slug,
          parent_id,
          sort_order,
          level,
          ARRAY[id] as path,
          ARRAY[name] as name_path,
          0 as depth
        FROM products_categories
        WHERE parent_id IS NULL AND is_active = true
        
        UNION ALL
        
        -- Recursive member: sub-categories
        SELECT 
          c.id,
          c.name,
          c.slug,
          c.parent_id,
          c.sort_order,
          c.level,
          ct.path || c.id,
          ct.name_path || c.name,
          ct.depth + 1
        FROM products_categories c
        INNER JOIN category_tree ct ON c.parent_id = ct.id
        WHERE c.is_active = true
      )
      SELECT 
        id,
        name,
        slug,
        parent_id,
        sort_order,
        level,
        path,
        name_path,
        depth,
        (SELECT COUNT(*) FROM products WHERE category_id = category_tree.id AND status = 'active') as product_count
      FROM category_tree
      ORDER BY path, sort_order;
    `;

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

  // Generate slug from name
  generateSlug(name: string): string {
    return name
      .toLowerCase()
      .replace(/[^a-z0-9\s-]/g, "")
      .replace(/\s+/g, "-")
      .replace(/--+/g, "-")
      .trim();
  },

  // Increment view count
  async incrementViewCount(id: string) {
    const query = `
      UPDATE products_categories
      SET view_count = view_count + 1
      WHERE id = $1
      RETURNING view_count;
    `;

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

  // Get featured categories
  async getFeaturedCategories(limit: number = 8) {
    const query = `
      SELECT 
        c.*,
        COUNT(p.id) as product_count
      FROM products_categories c
      LEFT JOIN products p ON p.category_id = c.id AND p.status = 'active'
      WHERE c.is_active = true AND c.is_featured = true
      GROUP BY c.id
      ORDER BY c.sort_order, c.created_at DESC
      LIMIT $1;
    `;

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