import { Knex } from 'knex';
import db from '../../../infrastructure/database/connection';
import { IUserRepository } from '../../../domain/interfaces/repositories/user.repository.interface';
import { User, PasswordResetToken } from '../../../domain/models/user.model';
import logger from '../../../config/logger';
import { DatabaseError } from '../../../utils/errors/database.error';

/**
 * PostgreSQL implementation of User Repository
 */
export class PgUserRepository implements IUserRepository {
  private readonly tableName = 'users';
  private readonly resetTokensTable = 'password_reset_tokens';
  private readonly db: Knex;

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

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

      // Parse JSON fields
      return users.map(user => ({
        ...user,
        // roles: JSON.parse(user.roles),
        // permissions: user.permissions ? JSON.parse(user.permissions) : []
      }));
    } catch (error) {
      logger.error(`Error retrieving users: ${error}`);
      throw new DatabaseError('Failed to retrieve users', error);
    }
  }

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

      if (!user) {
        return null;
      }

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

  async findByEmail(email: string): Promise<User | null> {
    try {
      const user = await this.db(this.tableName)
        .where({ email })
        .first();

      if (!user) {
        return null;
      }

      // Parse JSON fields
      return {
        ...user,
        // roles: JSON.parse(user.roles),
        // permissions: user.permissions ? JSON.parse(user.permissions) : []
      };
    } catch (error) {
      logger.error(`Error finding user by email: ${error}`);
      throw new DatabaseError('Failed to find user by email', error);
    }
  }

  async create(data: Partial<User>): Promise<User> {
    try {
      // Ensure roles and permissions are stored as JSON strings
      const userData = {
        ...data,
        roles: JSON.stringify(data.roles || ['staff']),
        permissions: data.permissions ? JSON.stringify(data.permissions) : JSON.stringify([]),
        created_at: new Date(),
        updated_at: new Date()
      };

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

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

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

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

      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 user
      return {
        ...updated,
        // roles: JSON.parse(updated.roles),
        // permissions: updated.permissions ? JSON.parse(updated.permissions) : []
      };
    } catch (error) {
      logger.error(`Error updating user: ${error}`);
      throw new DatabaseError('Failed to update user', error);
    }
  }

  async delete(id: string): Promise<boolean> {
    try {
      // Start transaction to handle related records
      const trx = await this.beginTransaction();

      try {
        // Delete password reset tokens first
        await trx(this.resetTokensTable)
          .where({ user_id: id })
          .delete();

        // Delete user access records (if the foreign key constraint doesn't cascade)
        await trx('user_restaurant_access')
          .where({ user_id: id })
          .delete();

        // Delete the user
        const deleted = await trx(this.tableName)
          .where({ id })
          .delete();

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

        return deleted > 0;
      } catch (error) {
        // Rollback transaction on error
        await this.rollbackTransaction(trx);
        throw error;
      }
    } catch (error) {
      logger.error(`Error deleting user: ${error}`);
      throw new DatabaseError('Failed to delete user', 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 users: ${error}`);
      throw new DatabaseError('Failed to count users', error);
    }
  }

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

      // Parse JSON fields
      return users.map(user => ({
        ...user,
        roles: JSON.parse(user.roles),
        permissions: user.permissions ? JSON.parse(user.permissions) : []
      }));
    } catch (error) {
      logger.error(`Error finding users by query: ${error}`);
      throw new DatabaseError('Failed to find users', error);
    }
  }

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

      if (!updated) {
        return null;
      }

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

  async changePassword(userId: string, hashedPassword: string): Promise<boolean> {
    try {
      const updated = await this.db(this.tableName)
        .where({ id: userId })
        .update({
          password: hashedPassword,
          updated_at: new Date()
        });

      return updated > 0;
    } catch (error) {
      logger.error(`Error changing password: ${error}`);
      throw new DatabaseError('Failed to change password', error);
    }
  }

  async createPasswordResetToken(
    userId: string,
    token: string,
    expiresAt: Date
  ): Promise<PasswordResetToken> {
    try {
      // First, delete any existing tokens for this user
      await this.deleteAllPasswordResetTokensForUser(userId);

      // Create new token
      const [resetToken] = await this.db(this.resetTokensTable)
        .insert({
          user_id: userId,
          token,
          expires_at: expiresAt,
          created_at: new Date()
        })
        .returning('*');

      return resetToken;
    } catch (error) {
      logger.error(`Error creating password reset token: ${error}`);
      throw new DatabaseError('Failed to create password reset token', error);
    }
  }

  async findPasswordResetToken(token: string): Promise<PasswordResetToken | null> {
    try {
      const resetToken = await this.db(this.resetTokensTable)
        .where({ token })
        .first();

      return resetToken || null;
    } catch (error) {
      logger.error(`Error finding password reset token: ${error}`);
      throw new DatabaseError('Failed to find password reset token', error);
    }
  }

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

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

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

      return deleted > 0;
    } catch (error) {
      logger.error(`Error deleting all password reset tokens for user: ${error}`);
      throw new DatabaseError('Failed to delete password reset tokens', error);
    }
  }

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

      if (!updated) {
        return null;
      }

      // Parse JSON fields
      return {
        ...updated,
        roles: JSON.parse(updated.roles),
        permissions: updated.permissions ? JSON.parse(updated.permissions) : []
      };
    } catch (error) {
      logger.error(`Error activating user: ${error}`);
      throw new DatabaseError('Failed to activate user', error);
    }
  }

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

      if (!updated) {
        return null;
      }

      // Parse JSON fields
      return {
        ...updated,
        roles: JSON.parse(updated.roles),
        permissions: updated.permissions ? JSON.parse(updated.permissions) : []
      };
    } catch (error) {
      logger.error(`Error deactivating user: ${error}`);
      throw new DatabaseError('Failed to deactivate user', error);
    }
  }

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

      if (!updated) {
        return null;
      }

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

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

      if (!updated) {
        return null;
      }

      // Parse JSON fields
      return {
        ...updated,
        roles: JSON.parse(updated.roles),
        permissions: updated.permissions ? JSON.parse(updated.permissions) : []
      };
    } catch (error) {
      logger.error(`Error updating user permissions: ${error}`);
      throw new DatabaseError('Failed to update user permissions', 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 === 'email' && typeof value === 'string') {
          query = query.where(key, 'ilike', `%${value}%`);
        } else if (key === 'name' && typeof value === 'string') {
          query = query.where(function() {
            this.where('first_name', 'ilike', `%${value}%`)
              .orWhere('last_name', 'ilike', `%${value}%`);
          });
        } else if (key === 'role' && typeof value === 'string') {
          // Search for role in JSON array
          query = query.whereRaw("roles::jsonb @> ?::jsonb", [JSON.stringify([value])]);
        } else if (key === 'active' && typeof value === 'boolean') {
          query = query.where('active', value);
        } else if (typeof value === 'string' && value.includes('%')) {
          query = query.where(key, 'ilike', value);
        } else {
          query = query.where(key, value);
        }
      }
    });
    return query;
  }
}