import { Knex } from 'knex';
import db from '../../../infrastructure/database/connection';
import { IAccessRepository } from '../../../domain/interfaces/repositories/access.repository.interface';
import { UserRestaurantAccess, UserAccessSummary } from '../../../domain/models/access.model';
import logger from '../../../config/logger';
import { DatabaseError } from '../../../utils/errors/database.error';

/**
 * PostgreSQL implementation of Access Repository
 */
export class PgAccessRepository implements IAccessRepository {
  private readonly tableName = 'user_restaurant_access';
  private readonly db: Knex;

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

  async findAll(page = 1, limit = 10, filters?: Record<string, any>): Promise<UserRestaurantAccess[]> {
    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 accessEntries = await query;

      // Parse JSON fields
      return accessEntries.map(entry => ({
        ...entry,
        // permissions: JSON.parse(entry.permissions)
      }));
    } catch (error) {
      logger.error(`Error retrieving access entries: ${error}`);
      throw new DatabaseError('Failed to retrieve access entries', error);
    }
  }

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

      if (!access) {
        return null;
      }

      // Parse JSON fields
      return {
        ...access,
        // permissions: JSON.parse(access.permissions)
      };
    } catch (error) {
      logger.error(`Error retrieving access entry: ${error}`);
      throw new DatabaseError('Failed to retrieve access entry', error);
    }
  }

  async create(data: Partial<UserRestaurantAccess>): Promise<UserRestaurantAccess> {
    try {
      // Ensure permissions is stored as JSON string
      const accessData = {
        ...data,
        permissions: JSON.stringify(data.permissions || []),
        created_at: new Date(),
        updated_at: new Date()
      };

      const [access] = await this.db(this.tableName)
        .insert(accessData)
        .returning('*');

      // Parse JSON fields for the returned access
      return {
        ...access,
        // permissions: JSON.parse(access.permissions)
      };
    } catch (error) {
      logger.error(`Error creating access entry: ${error}`);
      throw new DatabaseError('Failed to create access entry', error);
    }
  }

  async update(id: string, data: Partial<UserRestaurantAccess>): Promise<UserRestaurantAccess | null> {
    try {
      // Prepare update data, handling JSON fields
      const updateData: Record<string, any> = {
        ...data,
        updated_at: new Date()
      };

      if (data.permissions) {
        updateData.permissions = JSON.stringify(data.permissions);
      }

      const [updated] = await this.db(this.tableName)
        .where({ id })
        .update(updateData)
        .returning('*');

      if (!updated) {
        return null;
      }

      // Parse JSON fields for the returned access
      return {
        ...updated,
        // permissions: JSON.parse(updated.permissions)
      };
    } catch (error) {
      logger.error(`Error updating access entry: ${error}`);
      throw new DatabaseError('Failed to update access entry', 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 access entry: ${error}`);
      throw new DatabaseError('Failed to delete access entry', 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 access entries: ${error}`);
      throw new DatabaseError('Failed to count access entries', error);
    }
  }

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

      // Parse JSON fields
      return accessEntries.map(entry => ({
        ...entry,
        // permissions: JSON.parse(entry.permissions)
      }));
    } catch (error) {
      logger.error(`Error finding access entries by query: ${error}`);
      throw new DatabaseError('Failed to find access entries', error);
    }
  }

  async findByUserId(userId: string): Promise<UserRestaurantAccess[]> {
    try {
      const accessEntries = await this.db(this.tableName)
        .where({ user_id: userId })
        .orderBy('created_at', 'desc');

      // Parse JSON fields
      return accessEntries.map(entry => ({
        ...entry,
        // permissions: JSON.parse(entry.permissions)
      }));
    } catch (error) {
      logger.error(`Error finding access entries by user ID: ${error}`);
      throw new DatabaseError('Failed to find access entries by user ID', error);
    }
  }

  async findByRestaurantId(
    restaurantId: string,
    level?: 'group' | 'brand' | 'outlet'
  ): Promise<UserRestaurantAccess[]> {
    try {
      let query = this.db(this.tableName)
        .where({ restaurant_id: restaurantId });

      if (level) {
        query = query.where({ restaurant_level: level });
      }

      const accessEntries = await query.orderBy('created_at', 'desc');

      // Parse JSON fields
      return accessEntries.map(entry => ({
        ...entry,
        // permissions: JSON.parse(entry.permissions)
      }));
    } catch (error) {
      logger.error(`Error finding access entries by restaurant ID: ${error}`);
      throw new DatabaseError('Failed to find access entries by restaurant ID', error);
    }
  }

  async findSpecificAccess(
    userId: string,
    restaurantId: string,
    level: 'group' | 'brand' | 'outlet'
  ): Promise<UserRestaurantAccess | null> {
    try {
      const access = await this.db(this.tableName)
        .where({
          user_id: userId,
          restaurant_id: restaurantId,
          restaurant_level: level
        })
        .first();

      if (!access) {
        return null;
      }

      // Parse JSON fields
      return {
        ...access,
        // permissions: JSON.parse(access.permissions)
      };
    } catch (error) {
      logger.error(`Error finding specific access entry: ${error}`);
      throw new DatabaseError('Failed to find specific access entry', error);
    }
  }

  async hasAccess(
    userId: string,
    restaurantId: string,
    level: 'group' | 'brand' | 'outlet'
  ): Promise<boolean> {
    try {
      // For outlet level access, we need to check brand and group access too
      // For brand level access, we need to check group access too
      let query = this.db(this.tableName).where({ user_id: userId });

      if (level === 'outlet') {
        // Get the outlet's brand and group IDs
        const outlet = await this.db('restaurant_outlets')
          .where({ id: restaurantId })
          .select('brand_id')
          .first();

        if (!outlet) {
          return false;
        }

        const brand = await this.db('restaurant_brands')
          .where({ id: outlet.brand_id })
          .select('group_id')
          .first();

        if (!brand) {
          return false;
        }

        // Check access at all levels
        query = query.where(function() {
          this.where({ restaurant_id: restaurantId, restaurant_level: 'outlet' })
              .orWhere({ restaurant_id: outlet.brand_id, restaurant_level: 'brand' })
              .orWhere({ restaurant_id: brand.group_id, restaurant_level: 'group' });
        });
      } else if (level === 'brand') {
        // Get the brand's group ID
        const brand = await this.db('restaurant_brands')
          .where({ id: restaurantId })
          .select('group_id')
          .first();

        if (!brand) {
          return false;
        }

        // Check access at brand and group level
        query = query.where(function() {
          this.where({ restaurant_id: restaurantId, restaurant_level: 'brand' })
              .orWhere({ restaurant_id: brand.group_id, restaurant_level: 'group' });
        });
      } else {
        // Group level - direct check
        query = query.where({
          restaurant_id: restaurantId,
          restaurant_level: 'group'
        });
      }

      const count = await query.count('id as count').first();
      return (parseInt(count?.count as string) || 0) > 0;
    } catch (error) {
      logger.error(`Error checking user access: ${error}`);
      throw new DatabaseError('Failed to check user access', error);
    }
  }

  async hasPermission(
    userId: string,
    restaurantId: string,
    permission: string
  ): Promise<boolean> {
    try {
      // First check if user has access at any level
      const accessEntries = await this.db(this.tableName)
        .where({ user_id: userId })
        .select('*');

      if (!accessEntries.length) {
        return false;
      }

      // Check outlet level
      const outletAccess = accessEntries.find(
        entry => entry.restaurant_id === restaurantId && entry.restaurant_level === 'outlet'
      );

      if (outletAccess) {
        // const permissions = JSON.parse(outletAccess.permissions);
        const permissions = outletAccess.permissions;
        if (permissions.includes(permission)) {
          return true;
        }
      }

      // If no outlet access or permission not found, check brand level
      const outlet = await this.db('restaurant_outlets')
        .where({ id: restaurantId })
        .select('brand_id')
        .first();

      if (outlet) {
        const brandAccess = accessEntries.find(
          entry => entry.restaurant_id === outlet.brand_id && entry.restaurant_level === 'brand'
        );

        if (brandAccess) {
          // const permissions = JSON.parse(brandAccess.permissions);
          const permissions = outletAccess.permissions;
          if (permissions.includes(permission)) {
            return true;
          }
        }

        // If no brand access or permission not found, check group level
        const brand = await this.db('restaurant_brands')
          .where({ id: outlet.brand_id })
          .select('group_id')
          .first();

        if (brand) {
          const groupAccess = accessEntries.find(
            entry => entry.restaurant_id === brand.group_id && entry.restaurant_level === 'group'
          );

          if (groupAccess) {
            // const permissions = JSON.parse(groupAccess.permissions);
            const permissions = outletAccess.permissions;
            if (permissions.includes(permission)) {
              return true;
            }
          }
        }
      }

      return false;
    } catch (error) {
      logger.error(`Error checking user permission: ${error}`);
      throw new DatabaseError('Failed to check user permission', error);
    }
  }

  async updatePermissions(
    accessId: string,
    permissions: string[]
  ): Promise<UserRestaurantAccess | null> {
    try {
      const [updated] = await this.db(this.tableName)
        .where({ id: accessId })
        .update({
          permissions: JSON.stringify(permissions),
          updated_at: new Date()
        })
        .returning('*');

      if (!updated) {
        return null;
      }

      // Parse JSON fields
      return {
        ...updated,
        // permissions: JSON.parse(updated.permissions)
      };
    } catch (error) {
      logger.error(`Error updating permissions: ${error}`);
      throw new DatabaseError('Failed to update permissions', error);
    }
  }

  async addPermissions(
    accessId: string,
    permissions: string[]
  ): Promise<UserRestaurantAccess | null> {
    try {
      // Get current access entry
      const access = await this.findById(accessId);

      if (!access) {
        return null;
      }

      // Merge permissions (removing duplicates)
      const updatedPermissions = [...new Set([...access.permissions, ...permissions])];

      // Update permissions
      return this.updatePermissions(accessId, updatedPermissions);
    } catch (error) {
      logger.error(`Error adding permissions: ${error}`);
      throw new DatabaseError('Failed to add permissions', error);
    }
  }

  async removePermissions(
    accessId: string,
    permissions: string[]
  ): Promise<UserRestaurantAccess | null> {
    try {
      // Get current access entry
      const access = await this.findById(accessId);

      if (!access) {
        return null;
      }

      // Filter out permissions to remove
      const updatedPermissions = access.permissions.filter(
        permission => !permissions.includes(permission)
      );

      // Update permissions
      return this.updatePermissions(accessId, updatedPermissions);
    } catch (error) {
      logger.error(`Error removing permissions: ${error}`);
      throw new DatabaseError('Failed to remove permissions', error);
    }
  }

  async getUserAccessSummary(userId: string): Promise<UserAccessSummary | null> {
    try {
      // Get user info
      const user = await this.db('users')
        .where({ id: userId })
        .select('id', 'email', 'first_name', 'last_name')
        .first();

      if (!user) {
        return null;
      }

      // Get all access entries for this user
      const accessEntries = await this.findByUserId(userId);

      // Categorize access entries by level
      const groupAccessEntries = accessEntries.filter(entry => entry.restaurant_level === 'group');
      const brandAccessEntries = accessEntries.filter(entry => entry.restaurant_level === 'brand');
      const outletAccessEntries = accessEntries.filter(entry => entry.restaurant_level === 'outlet');

      // Get group details
      const groupIds = groupAccessEntries.map(entry => entry.restaurant_id);
      const groups = await this.db('restaurant_groups')
        .whereIn('id', groupIds)
        .select('id', 'name');

      // Get brand details
      const brandIds = brandAccessEntries.map(entry => entry.restaurant_id);
      const brands = await this.db('restaurant_brands')
        .whereIn('id', brandIds)
        .select('id', 'name', 'group_id');

      // Get outlet details
      const outletIds = outletAccessEntries.map(entry => entry.restaurant_id);
      const outlets = await this.db('restaurant_outlets')
        .whereIn('id', outletIds)
        .select('id', 'name', 'brand_id');

      // Build access summary
      const summary: UserAccessSummary = {
        user_id: user.id,
        email: user.email,
        first_name: user.first_name,
        last_name: user.last_name,
        access: {
          groups: groups.map(group => {
            const accessEntry = groupAccessEntries.find(entry => entry.restaurant_id === group.id);
            return {
              id: group.id,
              name: group.name,
              permissions: accessEntry?.permissions || []
            };
          }),
          brands: brands.map(brand => {
            const accessEntry = brandAccessEntries.find(entry => entry.restaurant_id === brand.id);
            return {
              id: brand.id,
              name: brand.name,
              group_id: brand.group_id,
              permissions: accessEntry?.permissions || []
            };
          }),
          outlets: outlets.map(outlet => {
            const accessEntry = outletAccessEntries.find(entry => entry.restaurant_id === outlet.id);
            return {
              id: outlet.id,
              name: outlet.name,
              brand_id: outlet.brand_id,
              permissions: accessEntry?.permissions || []
            };
          })
        }
      };

      return summary;
    } catch (error) {
      logger.error(`Error getting user access summary: ${error}`);
      throw new DatabaseError('Failed to get user access summary', error);
    }
  }

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

      return deleted > 0;
    } catch (error) {
      logger.error(`Error revoking all access for user: ${error}`);
      throw new DatabaseError('Failed to revoke all access for user', error);
    }
  }

  async bulkAssignAccess(
    restaurantId: string,
    level: 'group' | 'brand' | 'outlet',
    userIds: string[],
    permissions: string[]
  ): Promise<UserRestaurantAccess[]> {
    try {
      // Start transaction
      const trx = await this.beginTransaction();

      try {
        const results: UserRestaurantAccess[] = [];

        // Process each user
        for (const userId of userIds) {
          // Check if access already exists
          const existingAccess = await trx(this.tableName)
            .where({
              user_id: userId,
              restaurant_id: restaurantId,
              restaurant_level: level
            })
            .first();

          if (existingAccess) {
            // Update existing access
            const updatedPermissions = [...new Set([
              ...JSON.parse(existingAccess.permissions),
              ...permissions
            ])];

            const [updated] = await trx(this.tableName)
              .where({ id: existingAccess.id })
              .update({
                permissions: JSON.stringify(updatedPermissions),
                updated_at: new Date()
              })
              .returning('*');

            results.push({
              ...updated,
              permissions: JSON.parse(updated.permissions)
            });
          } else {
            // Create new access
            const [created] = await trx(this.tableName)
              .insert({
                user_id: userId,
                restaurant_id: restaurantId,
                restaurant_level: level,
                permissions: JSON.stringify(permissions),
                created_at: new Date(),
                updated_at: new Date()
              })
              .returning('*');

            results.push({
              ...created,
              permissions: JSON.parse(created.permissions)
            });
          }
        }

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

        return results;
      } catch (error) {
        // Rollback transaction on error
        await this.rollbackTransaction(trx);
        throw error;
      }
    } catch (error) {
      logger.error(`Error bulk assigning access: ${error}`);
      throw new DatabaseError('Failed to bulk assign access', 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 (key === 'user_id' || key === 'restaurant_id') {
          query = query.where(key, value);
        } else if (key === 'restaurant_level' && ['group', 'brand', 'outlet'].includes(value)) {
          query = query.where(key, value);
        } else if (key === 'permission' && typeof value === 'string') {
          // Search for permission in JSON array
          query = query.whereRaw("permissions::jsonb ? ?", [value]);
        } else {
          query = query.where(key, value);
        }
      }
    });
    return query;
  }
}