import { Knex } from 'knex';
import db from '../../../infrastructure/database/connection';
import {
  IRestaurantGroupRepository,
  IRestaurantBrandRepository,
  IRestaurantOutletRepository
} from '../../../domain/interfaces/repositories/restaurant.repository.interface';
import {
  RestaurantGroup,
  RestaurantBrand,
  RestaurantOutlet,
  RestaurantHierarchy
} from '../../../domain/models/restaurant.model';
import logger from '../../../config/logger';
import { DatabaseError } from '../../../utils/errors/database.error';

/**
 * PostgreSQL implementation of Restaurant Group Repository
 */
export class PgRestaurantGroupRepository implements IRestaurantGroupRepository {
  private readonly tableName = 'restaurant_groups';
  private readonly db: Knex;

  constructor(dbInstance?: Knex) {
    this.db = dbInstance || db;
  }

  async findAll(page = 1, limit = 10, filters?: Record<string, any>): Promise<RestaurantGroup[]> {
    try {
      const offset = (page - 1) * limit;
      let query = this.db(this.tableName)
        .select('*')
        .offset(offset)
        .limit(limit)
        .orderBy('created_at', 'desc');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const groups = await query;
      return groups as RestaurantGroup[];
    } catch (error) {
      logger.error(`Error retrieving restaurant groups: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant groups', error);
    }
  }

  async findById(id: string): Promise<RestaurantGroup | null> {
    try {
      const group = await this.db(this.tableName)
        .where({ id })
        .first();

      return group || null;
    } catch (error) {
      logger.error(`Error retrieving restaurant group: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant group', error);
    }
  }

  async create(data: Partial<RestaurantGroup>): Promise<RestaurantGroup> {
    try {
      const [group] = await this.db(this.tableName)
        .insert({
          ...data,
          created_at: new Date(),
          updated_at: new Date()
        })
        .returning('*');

      return group as RestaurantGroup;
    } catch (error) {
      logger.error(`Error creating restaurant group: ${error}`);
      throw new DatabaseError('Failed to create restaurant group', error);
    }
  }

  async update(id: string, data: Partial<RestaurantGroup>): Promise<RestaurantGroup | null> {
    try {
      const [updated] = await this.db(this.tableName)
        .where({ id })
        .update({
          ...data,
          updated_at: new Date()
        })
        .returning('*');

      return updated || null;
    } catch (error) {
      logger.error(`Error updating restaurant group: ${error}`);
      throw new DatabaseError('Failed to update restaurant group', error);
    }
  }

  async delete(id: string): Promise<boolean> {
    try {
      const deleted = await this.db(this.tableName)
        .where({ id })
        .delete();

      return deleted > 0;
    } catch (error) {
      logger.error(`Error deleting restaurant group: ${error}`);
      throw new DatabaseError('Failed to delete restaurant group', error);
    }
  }

  async count(filters?: Record<string, any>): Promise<number> {
    try {
      let query = this.db(this.tableName).count('id as count');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const result = await query.first();
      return parseInt(result?.count as string) || 0;
    } catch (error) {
      logger.error(`Error counting restaurant groups: ${error}`);
      throw new DatabaseError('Failed to count restaurant groups', error);
    }
  }

  async findBy(query: Record<string, any>): Promise<RestaurantGroup[]> {
    try {
      const groups = await this.db(this.tableName)
        .where(query)
        .orderBy('created_at', 'desc');

      return groups as RestaurantGroup[];
    } catch (error) {
      logger.error(`Error finding restaurant groups by query: ${error}`);
      throw new DatabaseError('Failed to find restaurant groups', error);
    }
  }

  async getFullHierarchy(groupId: string): Promise<RestaurantHierarchy | null> {
    try {
      // Start transaction
      const trx = await this.beginTransaction();

      try {
        // Get the group
        const group = await trx(this.tableName)
          .where({ id: groupId })
          .first();

        if (!group) {
          await this.rollbackTransaction(trx);
          return null;
        }

        // Get all brands for this group
        const brands = await trx('restaurant_brands')
          .where({ group_id: groupId })
          .orderBy('name');

        // For each brand, get outlets
        const brandsWithOutlets = await Promise.all(
          brands.map(async (brand) => {
            const outlets = await trx('restaurant_outlets')
              .where({ brand_id: brand.id })
              .orderBy('name');

            return {
              brand,
              outlets
            };
          })
        );

        // Commit transaction
        await this.commitTransaction(trx);

        return {
          group,
          brands: brandsWithOutlets
        };
      } catch (error) {
        await this.rollbackTransaction(trx);
        throw error;
      }
    } catch (error) {
      logger.error(`Error retrieving restaurant hierarchy: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant hierarchy', error);
    }
  }

  async beginTransaction(): Promise<Knex.Transaction> {
    return await this.db.transaction();
  }

  async commitTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.commit();
  }

  async rollbackTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.rollback();
  }

  private applyFilters(query: Knex.QueryBuilder, filters: Record<string, any>): Knex.QueryBuilder {
    Object.entries(filters).forEach(([key, value]) => {
      if (value !== undefined && value !== null) {
        if (typeof value === 'string' && value.includes('%')) {
          query = query.where(key, 'ilike', value);
        } else {
          query = query.where(key, value);
        }
      }
    });
    return query;
  }
}

/**
 * PostgreSQL implementation of Restaurant Brand Repository
 */
export class PgRestaurantBrandRepository implements IRestaurantBrandRepository {
  private readonly tableName = 'restaurant_brands';
  private readonly db: Knex;

  constructor(dbInstance?: Knex) {
    this.db = dbInstance || db;
  }

  async findAll(page = 1, limit = 10, filters?: Record<string, any>): Promise<RestaurantBrand[]> {
    try {
      const offset = (page - 1) * limit;
      let query = this.db(this.tableName)
        .select('*')
        .offset(offset)
        .limit(limit)
        .orderBy('created_at', 'desc');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const brands = await query;
      return brands as RestaurantBrand[];
    } catch (error) {
      logger.error(`Error retrieving restaurant brands: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant brands', error);
    }
  }

  async findById(id: string): Promise<RestaurantBrand | null> {
    try {
      const brand = await this.db(this.tableName)
        .where({ id })
        .first();

      return brand || null;
    } catch (error) {
      logger.error(`Error retrieving restaurant brand: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant brand', error);
    }
  }

  async findByGroupId(groupId: string): Promise<RestaurantBrand[]> {
    try {
      const brands = await this.db(this.tableName)
        .where({ group_id: groupId })
        .orderBy('name');

      return brands as RestaurantBrand[];
    } catch (error) {
      logger.error(`Error retrieving brands for group: ${error}`);
      throw new DatabaseError('Failed to retrieve brands by group ID', error);
    }
  }

  async create(data: Partial<RestaurantBrand>): Promise<RestaurantBrand> {
    try {
      const [brand] = await this.db(this.tableName)
        .insert({
          ...data,
          created_at: new Date(),
          updated_at: new Date()
        })
        .returning('*');

      return brand as RestaurantBrand;
    } catch (error) {
      logger.error(`Error creating restaurant brand: ${error}`);
      throw new DatabaseError('Failed to create restaurant brand', error);
    }
  }

  async update(id: string, data: Partial<RestaurantBrand>): Promise<RestaurantBrand | null> {
    try {
      const [updated] = await this.db(this.tableName)
        .where({ id })
        .update({
          ...data,
          updated_at: new Date()
        })
        .returning('*');

      return updated || null;
    } catch (error) {
      logger.error(`Error updating restaurant brand: ${error}`);
      throw new DatabaseError('Failed to update restaurant brand', error);
    }
  }

  async delete(id: string): Promise<boolean> {
    try {
      const deleted = await this.db(this.tableName)
        .where({ id })
        .delete();

      return deleted > 0;
    } catch (error) {
      logger.error(`Error deleting restaurant brand: ${error}`);
      throw new DatabaseError('Failed to delete restaurant brand', error);
    }
  }

  async count(filters?: Record<string, any>): Promise<number> {
    try {
      let query = this.db(this.tableName).count('id as count');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const result = await query.first();
      return parseInt(result?.count as string) || 0;
    } catch (error) {
      logger.error(`Error counting restaurant brands: ${error}`);
      throw new DatabaseError('Failed to count restaurant brands', error);
    }
  }

  async findBy(query: Record<string, any>): Promise<RestaurantBrand[]> {
    try {
      const brands = await this.db(this.tableName)
        .where(query)
        .orderBy('created_at', 'desc');

      return brands as RestaurantBrand[];
    } catch (error) {
      logger.error(`Error finding restaurant brands by query: ${error}`);
      throw new DatabaseError('Failed to find restaurant brands', error);
    }
  }

  async beginTransaction(): Promise<Knex.Transaction> {
    return await this.db.transaction();
  }

  async commitTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.commit();
  }

  async rollbackTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.rollback();
  }

  private applyFilters(query: Knex.QueryBuilder, filters: Record<string, any>): Knex.QueryBuilder {
    Object.entries(filters).forEach(([key, value]) => {
      if (value !== undefined && value !== null) {
        if (typeof value === 'string' && value.includes('%')) {
          query = query.where(key, 'ilike', value);
        } else {
          query = query.where(key, value);
        }
      }
    });
    return query;
  }
}

/**
 * PostgreSQL implementation of Restaurant Outlet Repository
 */
export class PgRestaurantOutletRepository implements IRestaurantOutletRepository {
  private readonly tableName = 'restaurant_outlets';
  private readonly db: Knex;

  constructor(dbInstance?: Knex) {
    this.db = dbInstance || db;
  }

  async findAll(page = 1, limit = 10, filters?: Record<string, any>): Promise<RestaurantOutlet[]> {
    try {
      const offset = (page - 1) * limit;
      let query = this.db(this.tableName)
        .select('*')
        .offset(offset)
        .limit(limit)
        .orderBy('created_at', 'desc');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const outlets = await query;
      return outlets as RestaurantOutlet[];
    } catch (error) {
      logger.error(`Error retrieving restaurant outlets: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant outlets', error);
    }
  }

  async findById(id: string): Promise<RestaurantOutlet | null> {
    try {
      const outlet = await this.db(this.tableName)
        .where({ id })
        .first();

      return outlet || null;
    } catch (error) {
      logger.error(`Error retrieving restaurant outlet: ${error}`);
      throw new DatabaseError('Failed to retrieve restaurant outlet', error);
    }
  }

  async findByBrandId(brandId: string): Promise<RestaurantOutlet[]> {
    try {
      const outlets = await this.db(this.tableName)
        .where({ brand_id: brandId })
        .orderBy('name');

      return outlets as RestaurantOutlet[];
    } catch (error) {
      logger.error(`Error retrieving outlets for brand: ${error}`);
      throw new DatabaseError('Failed to retrieve outlets by brand ID', error);
    }
  }

  async findByPosId(posId: string): Promise<RestaurantOutlet | null> {
    try {
      const outlet = await this.db(this.tableName)
        .where({ pos_id: posId })
        .first();

      return outlet || null;
    } catch (error) {
      logger.error(`Error retrieving outlet by POS ID: ${error}`);
      throw new DatabaseError('Failed to retrieve outlet by POS ID', error);
    }
  }

  async create(data: Partial<RestaurantOutlet>): Promise<RestaurantOutlet> {
    try {
      const [outlet] = await this.db(this.tableName)
        .insert({
          ...data,
          created_at: new Date(),
          updated_at: new Date()
        })
        .returning('*');

      return outlet as RestaurantOutlet;
    } catch (error) {
      logger.error(`Error creating restaurant outlet: ${error}`);
      throw new DatabaseError('Failed to create restaurant outlet', error);
    }
  }

  async update(id: string, data: Partial<RestaurantOutlet>): Promise<RestaurantOutlet | null> {
    try {
      const [updated] = await this.db(this.tableName)
        .where({ id })
        .update({
          ...data,
          updated_at: new Date()
        })
        .returning('*');

      return updated || null;
    } catch (error) {
      logger.error(`Error updating restaurant outlet: ${error}`);
      throw new DatabaseError('Failed to update restaurant outlet', error);
    }
  }

  async delete(id: string): Promise<boolean> {
    try {
      const deleted = await this.db(this.tableName)
        .where({ id })
        .delete();

      return deleted > 0;
    } catch (error) {
      logger.error(`Error deleting restaurant outlet: ${error}`);
      throw new DatabaseError('Failed to delete restaurant outlet', error);
    }
  }

  async count(filters?: Record<string, any>): Promise<number> {
    try {
      let query = this.db(this.tableName).count('id as count');

      // Apply filters if provided
      if (filters) {
        query = this.applyFilters(query, filters);
      }

      const result = await query.first();
      return parseInt(result?.count as string) || 0;
    } catch (error) {
      logger.error(`Error counting restaurant outlets: ${error}`);
      throw new DatabaseError('Failed to count restaurant outlets', error);
    }
  }

  async findBy(query: Record<string, any>): Promise<RestaurantOutlet[]> {
    try {
      const outlets = await this.db(this.tableName)
        .where(query)
        .orderBy('created_at', 'desc');

      return outlets as RestaurantOutlet[];
    } catch (error) {
      logger.error(`Error finding restaurant outlets by query: ${error}`);
      throw new DatabaseError('Failed to find restaurant outlets', error);
    }
  }

  async beginTransaction(): Promise<Knex.Transaction> {
    return await this.db.transaction();
  }

  async commitTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.commit();
  }

  async rollbackTransaction(trx: Knex.Transaction): Promise<void> {
    await trx.rollback();
  }

  private applyFilters(query: Knex.QueryBuilder, filters: Record<string, any>): Knex.QueryBuilder {
    Object.entries(filters).forEach(([key, value]) => {
      if (value !== undefined && value !== null) {
        if (typeof value === 'string' && value.includes('%')) {
          query = query.where(key, 'ilike', value);
        } else {
          query = query.where(key, value);
        }
      }
    });
    return query;
  }
}