import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import { Pageable } from '../model/page';
import {
  ProductResponse,
  CreateProductRequest,
  UpdateProductRequest,
  ProductQueryParams,
  Product,
} from '../model/product-model';
import { ProductValidation } from '../validation/product-validation';
import { Validation } from '../validation/validation';

export class ProductService {
  static async create(request: CreateProductRequest): Promise<ProductResponse> {
    // Validate the create request
    const createRequest = Validation.validate(ProductValidation.CREATE, request);

    // Generate a UUID for the new product
    const productId = uuidv4();

    // Insert the new product into the database with the UUID
    await db.query('INSERT INTO product (id, name, description, price, has_variation) VALUES (?, ?, ?, ?, ?)', [
      productId,
      createRequest.name,
      createRequest.description ?? null,
      createRequest.price ?? null,
      createRequest.has_variation,
    ]);

    // Insert the thumbnails into the database
    if (createRequest.thumbnails && createRequest.thumbnails.length > 0) {
      await db.query(
        'INSERT INTO product_thumbnail (id, product_id, image_url) VALUES ' +
          createRequest.thumbnails.map(() => '(?, ?, ?)').join(', '),
        createRequest.thumbnails.flatMap((thumbnail) => [uuidv4(), productId, thumbnail])
      );
    }

    // Insert the categories into the database
    if (createRequest.category_ids && createRequest.category_ids.length > 0) {
      await db.query(
        'INSERT INTO product_category (id, product_id, category_id) VALUES ' +
          createRequest.category_ids.map(() => '(?, ?, ?)').join(', '),
        createRequest.category_ids.flatMap((category_id) => [uuidv4(), productId, category_id])
      );
    }

    // Insert the variations into the database
    if (createRequest.variations && createRequest.variations.length > 0) {
      await db.query(
        'INSERT INTO product_variation (id, product_id, name, price) VALUES ' +
          createRequest.variations.map(() => '(?, ?, ?, ?)').join(', '),
        createRequest.variations.flatMap((variation) => [uuidv4(), productId, variation.name, variation.price])
      );
    }

    // Insert the includes into the database
    if (createRequest.includes && createRequest.includes.length > 0) {
      await db.query(
        'INSERT INTO product_include (id, product_id, description) VALUES ' +
          createRequest.includes.map(() => '(?, ?, ?)').join(', '),
        createRequest.includes.flatMap((include) => [uuidv4(), productId, include])
      );
    }

    // Insert the excludes into the database
    if (createRequest.excludes && createRequest.excludes.length > 0) {
      await db.query(
        'INSERT INTO product_exclude (id, product_id, description) VALUES ' +
          createRequest.excludes.map(() => '(?, ?, ?)').join(', '),
        createRequest.excludes.flatMap((exclude) => [uuidv4(), productId, exclude])
      );
    }

    return await this.get(productId);
  }

  static async getAll(queryParams: ProductQueryParams): Promise<Pageable<Product>> {
    // Validate the query parameters
    const queryRequest = Validation.validate(ProductValidation.QUERY, queryParams);

    // Calculate the skip value
    const skip = (queryRequest.page - 1) * queryRequest.limit;

    // Create the WHERE clause
    let filterQuery = 'WHERE 1 = 1';
    const filterParams: any[] = [];

    if (queryRequest.name) {
      filterQuery += ' AND name LIKE ?';
      filterParams.push(`%${queryRequest.name}%`);
    }
    if (queryRequest.has_variation !== undefined) {
      filterQuery += ' AND has_variation = ?';
      filterParams.push(queryRequest.has_variation);
    }
    if (queryRequest.category_id?.length) {
      filterQuery += ' AND category_id IN (?)';
      filterParams.push(queryRequest.category_id);
    }

    // Create the ORDER BY clause
    const sortField = queryRequest.sort || 'created_at';
    const sortOrder = queryRequest.order || 'desc';

    // Query to get product details along with related entities
    const productsQuery = `
        SELECT product.*, 
          CONCAT(
            '[', GROUP_CONCAT(DISTINCT IF(thumbnail.id IS NOT NULL, JSON_OBJECT('id', thumbnail.id, 'increment', thumbnail.increment, 'image_url', thumbnail.image_url), NULL) ORDER BY thumbnail.increment), ']'
          ) AS thumbnails,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(category.id IS NOT NULL, JSON_OBJECT('id', category.id, 'name', category.name), NULL)), ']'
          ) AS categories,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(variation.id IS NOT NULL, JSON_OBJECT('id', variation.id, 'increment', variation.increment, 'name', variation.name, 'price', variation.price), NULL) ORDER BY variation.increment), ']'
          ) AS variations,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(include.id IS NOT NULL, JSON_OBJECT('id', include.id, 'increment', include.increment, 'description', include.description), NULL) ORDER BY include.increment), ']'
          ) AS includes,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(exclude.id IS NOT NULL, JSON_OBJECT('id', exclude.id, 'increment', exclude.increment, 'description', exclude.description), NULL) ORDER BY exclude.increment), ']'
          ) AS excludes
        FROM product
        LEFT JOIN product_thumbnail thumbnail ON product.id = thumbnail.product_id
        LEFT JOIN product_category pc ON product.id = pc.product_id
        LEFT JOIN category ON pc.category_id = category.id
        LEFT JOIN product_variation variation ON product.id = variation.product_id
        LEFT JOIN product_include include ON product.id = include.product_id
        LEFT JOIN product_exclude exclude ON product.id = exclude.product_id
        ${filterQuery}
        GROUP BY product.id
        ORDER BY ${sortField} ${sortOrder}
        LIMIT ? OFFSET ?
    `;
    const products = await db.query<Product>(productsQuery, [...filterParams, queryRequest.limit, skip]);

    // Parse JSON-like fields in each product
    products.forEach((product) => {
      product.thumbnails = product.thumbnails && product.thumbnails !== 'null' ? JSON.parse(product.thumbnails) : [];
      product.categories = product.categories && product.categories !== 'null' ? JSON.parse(product.categories) : [];
      product.variations = product.variations && product.variations !== 'null' ? JSON.parse(product.variations) : [];
      product.includes = product.includes && product.includes !== 'null' ? JSON.parse(product.includes) : [];
      product.excludes = product.excludes && product.excludes !== 'null' ? JSON.parse(product.excludes) : [];
    });

    // Get the total count
    const count = await db.queryOne<{ total: number }>(
      'SELECT COUNT(DISTINCT product.id) AS total FROM product LEFT JOIN product_category ON product.id = product_category.product_id LEFT JOIN category ON product_category.category_id = category.id ' +
        filterQuery,
      filterParams
    );

    if (!count) {
      throw new ResponseError(404, 'Produk tidak ditemukan');
    }

    return {
      data: products,
      pagination: {
        total: count.total,
        current_page: queryRequest.page,
        total_pages: Math.ceil(count.total / queryRequest.limit),
        limit: queryRequest.limit,
      },
    };
  }

  static async get(id: string): Promise<ProductResponse> {
    // Query to get product details along with related entities
    const productQuery = `
        SELECT product.*, 
          CONCAT(
            '[', GROUP_CONCAT(DISTINCT IF(thumbnail.id IS NOT NULL, JSON_OBJECT('id', thumbnail.id, 'increment', thumbnail.increment, 'image_url', thumbnail.image_url), NULL) ORDER BY thumbnail.increment), ']'
          ) AS thumbnails,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(category.id IS NOT NULL, JSON_OBJECT('id', category.id, 'name', category.name), NULL)), ']'
          ) AS categories,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(variation.id IS NOT NULL, JSON_OBJECT('id', variation.id, 'increment', variation.increment, 'name', variation.name, 'price', variation.price), NULL) ORDER BY variation.increment), ']'
          ) AS variations,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(include.id IS NOT NULL, JSON_OBJECT('id', include.id, 'increment', include.increment, 'description', include.description), NULL) ORDER BY include.increment), ']'
          ) AS includes,
          CONCAT(
              '[', GROUP_CONCAT(DISTINCT IF(exclude.id IS NOT NULL, JSON_OBJECT('id', exclude.id, 'increment', exclude.increment, 'description', exclude.description), NULL) ORDER BY exclude.increment), ']'
          ) AS excludes
        FROM product
        LEFT JOIN product_thumbnail thumbnail ON product.id = thumbnail.product_id
        LEFT JOIN product_category pc ON product.id = pc.product_id
        LEFT JOIN category ON pc.category_id = category.id
        LEFT JOIN product_variation variation ON product.id = variation.product_id
        LEFT JOIN product_include include ON product.id = include.product_id
        LEFT JOIN product_exclude exclude ON product.id = exclude.product_id
        WHERE product.id = ?
    `;
    const product = await db.queryOne<Product>(productQuery, [id]);

    if (!product) {
      throw new ResponseError(404, 'Produk tidak ditemukan');
    }

    // Manually construct ProductResponse with parsed fields
    const formattedProduct: ProductResponse = {
      ...product,
      description: product.description || '',
      thumbnails: product.thumbnails && product.thumbnails !== 'null' ? JSON.parse(product.thumbnails) : [],
      categories: product.categories && product.categories !== 'null' ? JSON.parse(product.categories) : [],
      variations: product.variations && product.variations !== 'null' ? JSON.parse(product.variations) : [],
      includes: product.includes && product.includes !== 'null' ? JSON.parse(product.includes) : [],
      excludes: product.excludes && product.excludes !== 'null' ? JSON.parse(product.excludes) : [],
    };

    return formattedProduct;
  }

  static async update(id: string, request: UpdateProductRequest): Promise<ProductResponse> {
    // Validate the update request parameters
    const updateRequest = Validation.validate(ProductValidation.UPDATE, request);

    return await db.transaction(async (connection) => {
      // Create an array to store the fields and values for the update query
      const fields = [];
      const values = [];

      // Add `name` to the update if provided
      if (updateRequest.name !== undefined) {
        fields.push('name = ?');
        values.push(updateRequest.name);
      }

      // Add `description` to the update if provided
      if (updateRequest.description !== undefined) {
        fields.push('description = ?');
        values.push(updateRequest.description);
      }

      // Add `price` to the update if provided
      if (updateRequest.price !== undefined) {
        fields.push('price = ?');
        values.push(updateRequest.price);
      }

      // Add `has_variation` to the update if provided
      if (updateRequest.has_variation !== undefined) {
        fields.push('has_variation = ?');
        values.push(updateRequest.has_variation);
      }

      // Update the product data in the database
      if (fields.length > 0) {
        await connection.query(`UPDATE product SET ${fields.join(', ')} WHERE id = ?`, [...values, id]);
      }

      // Update product thumbnails if provided
      if (updateRequest.thumbnails && updateRequest.thumbnails.length > 0) {
        await connection.query(`DELETE FROM product_thumbnail WHERE product_id = ?`, [id]);
        const thumbnailValues = updateRequest.thumbnails.map((url) => [uuidv4(), id, url]);
        await connection.query(`INSERT INTO product_thumbnail (id, product_id, image_url) VALUES ?`, [thumbnailValues]);
      }

      // Update product categories if provided
      if (updateRequest.category_ids) {
        await connection.query(`DELETE FROM product_category WHERE product_id = ?`, [id]);
        if (updateRequest.category_ids.length > 0) {
          const categoryValues = updateRequest.category_ids.map((category_id) => [uuidv4(), id, category_id]);
          await connection.query(`INSERT INTO product_category (id, product_id, category_id) VALUES ?`, [categoryValues]);
        }
      }

      // Update product variations if provided
      if (updateRequest.variations) {
        await connection.query(`DELETE FROM product_variation WHERE product_id = ?`, [id]);
        if (updateRequest.variations.length > 0) {
          const variationValues = updateRequest.variations.map((variation) => [
            uuidv4(),
            id,
            variation.name,
            variation.price,
          ]);
          await connection.query(`INSERT INTO product_variation (id, product_id, name, price) VALUES ?`, [
            variationValues,
          ]);
        }
      }

      // Update product includes if provided
      if (updateRequest.includes) {
        await connection.query(`DELETE FROM product_include WHERE product_id = ?`, [id]);
        if (updateRequest.includes.length > 0) {
          const includeValues = updateRequest.includes.map((description) => [uuidv4(), id, description]);
          await connection.query(`INSERT INTO product_include (id, product_id, description) VALUES ?`, [includeValues]);
        }
      }

      // Update product excludes if provided
      if (updateRequest.excludes) {
        await connection.query(`DELETE FROM product_exclude WHERE product_id = ?`, [id]);
        if (updateRequest.excludes.length > 0) {
          const excludeValues = updateRequest.excludes.map((description) => [uuidv4(), id, description]);
          await connection.query(`INSERT INTO product_exclude (id, product_id, description) VALUES ?`, [excludeValues]);
        }
      }

      // Commit the transaction
      await connection.commit();

      // Fetch and return the updated product details after transaction
      return await this.get(id);
    });
  }

  static async delete(id: string): Promise<void> {
    // Check if the product exists
    const product = await db.queryOne<Product>('SELECT * FROM product WHERE id = ?', [id]);

    if (!product) {
      throw new ResponseError(404, 'Produk tidak ditemukan');
    }

    // Delete the product
    await db.query('DELETE FROM product WHERE id = ?', [id]);
  }
}
