import pool from '../config/database';
import logger from '../utils/logger';
import { User, CreateUserData, UpdateUserData } from '../models/user.model';

class UserService {
  /**
   * Find user by Authentik sub (subject identifier)
   */
  async findByAuthentikSub(sub: string): Promise<User | null> {
    try {
      const query = `
        SELECT id, email, authentik_sub, name, preferred_username, given_name,
               family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
               is_active, last_login_at, created_at, updated_at, preferences, authentik_groups
        FROM users
        WHERE authentik_sub = $1
      `;

      const result = await pool.query(query, [sub]);

      if (result.rows.length === 0) {
        logger.debug('User not found by Authentik sub', { sub });
        return null;
      }

      logger.debug('User found by Authentik sub', { sub, userId: result.rows[0].id });
      return result.rows[0] as User;
    } catch (error) {
      logger.error('Error finding user by Authentik sub', { error, sub });
      throw new Error('Database query failed');
    }
  }

  /**
   * Find user by email
   */
  async findByEmail(email: string): Promise<User | null> {
    try {
      const query = `
        SELECT id, email, authentik_sub, name, preferred_username, given_name,
               family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
               is_active, last_login_at, created_at, updated_at, preferences, authentik_groups
        FROM users
        WHERE email = $1
      `;

      const result = await pool.query(query, [email]);

      if (result.rows.length === 0) {
        logger.debug('User not found by email', { email });
        return null;
      }

      logger.debug('User found by email', { email, userId: result.rows[0].id });
      return result.rows[0] as User;
    } catch (error) {
      logger.error('Error finding user by email', { error, email });
      throw new Error('Database query failed');
    }
  }

  /**
   * Find user by ID
   */
  async findById(id: string): Promise<User | null> {
    try {
      const query = `
        SELECT id, email, authentik_sub, name, preferred_username, given_name,
               family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
               is_active, last_login_at, created_at, updated_at, preferences, authentik_groups
        FROM users
        WHERE id = $1
      `;

      const result = await pool.query(query, [id]);

      if (result.rows.length === 0) {
        logger.debug('User not found by ID', { id });
        return null;
      }

      logger.debug('User found by ID', { id });
      return result.rows[0] as User;
    } catch (error) {
      logger.error('Error finding user by ID', { error, id });
      throw new Error('Database query failed');
    }
  }

  /**
   * Create a new user
   */
  async createUser(userData: CreateUserData): Promise<User> {
    try {
      const query = `
        INSERT INTO users (
          email,
          authentik_sub,
          name,
          preferred_username,
          given_name,
          family_name,
          profile_picture_url,
          is_active,
          authentik_groups
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, true, $8)
        RETURNING id, email, authentik_sub, name, preferred_username, given_name,
                  family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
                  is_active, last_login_at, created_at, updated_at, preferences, authentik_groups
      `;

      const values = [
        userData.email,
        userData.authentik_sub,
        userData.name || null,
        userData.preferred_username || null,
        userData.given_name || null,
        userData.family_name || null,
        userData.profile_picture_url || null,
        userData.authentik_groups ?? [],
      ];

      const result = await pool.query(query, values);

      const user = result.rows[0] as User;
      logger.info('User created successfully', {
        userId: user.id,
        email: user.email,
      });

      return user;
    } catch (error) {
      logger.error('Error creating user', { error, email: userData.email });
      throw new Error('Failed to create user');
    }
  }

  /**
   * Update user information
   */
  async updateUser(id: string, data: UpdateUserData): Promise<User> {
    try {
      const updateFields: string[] = [];
      const values: any[] = [];
      let paramCount = 1;

      if (data.name !== undefined) {
        updateFields.push(`name = $${paramCount++}`);
        values.push(data.name);
      }
      if (data.preferred_username !== undefined) {
        updateFields.push(`preferred_username = $${paramCount++}`);
        values.push(data.preferred_username);
      }
      if (data.given_name !== undefined) {
        updateFields.push(`given_name = $${paramCount++}`);
        values.push(data.given_name);
      }
      if (data.family_name !== undefined) {
        updateFields.push(`family_name = $${paramCount++}`);
        values.push(data.family_name);
      }
      if (data.profile_picture_url !== undefined) {
        updateFields.push(`profile_picture_url = $${paramCount++}`);
        values.push(data.profile_picture_url);
      }
      if (data.is_active !== undefined) {
        updateFields.push(`is_active = $${paramCount++}`);
        values.push(data.is_active);
      }
      if (data.preferences !== undefined) {
        updateFields.push(`preferences = $${paramCount++}`);
        values.push(JSON.stringify(data.preferences));
      }

      if (updateFields.length === 0) {
        throw new Error('No fields to update');
      }

      updateFields.push(`updated_at = CURRENT_TIMESTAMP`);
      values.push(id);

      const query = `
        UPDATE users
        SET ${updateFields.join(', ')}
        WHERE id = $${paramCount}
        RETURNING id, email, authentik_sub, name, preferred_username, given_name,
                  family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
                  is_active, last_login_at, created_at, updated_at, preferences, authentik_groups
      `;

      const result = await pool.query(query, values);

      if (result.rows.length === 0) {
        throw new Error('User not found');
      }

      const user = result.rows[0] as User;
      logger.info('User updated successfully', { userId: user.id });

      return user;
    } catch (error) {
      logger.error('Error updating user', { error, userId: id });
      throw new Error('Failed to update user');
    }
  }

  /**
   * Update last login timestamp
   */
  async updateLastLogin(id: string): Promise<void> {
    try {
      const query = `
        UPDATE users
        SET last_login_at = CURRENT_TIMESTAMP
        WHERE id = $1
      `;

      await pool.query(query, [id]);
      logger.debug('Updated last login timestamp', { userId: id });
    } catch (error) {
      logger.error('Error updating last login', { error, userId: id });
      // Don't throw - this is not critical
    }
  }

  /**
   * Update refresh token
   */
  async updateRefreshToken(
    id: string,
    refreshToken: string | null,
    expiresAt: Date | null
  ): Promise<void> {
    try {
      const query = `
        UPDATE users
        SET refresh_token = $1,
            refresh_token_expires_at = $2
        WHERE id = $3
      `;

      await pool.query(query, [refreshToken, expiresAt, id]);
      logger.debug('Updated refresh token', { userId: id });
    } catch (error) {
      logger.error('Error updating refresh token', { error, userId: id });
      throw new Error('Failed to update refresh token');
    }
  }

  /**
   * Check if user is active
   */
  async isUserActive(id: string): Promise<boolean> {
    try {
      const query = `
        SELECT is_active
        FROM users
        WHERE id = $1
      `;

      const result = await pool.query(query, [id]);

      if (result.rows.length === 0) {
        return false;
      }

      return result.rows[0].is_active;
    } catch (error) {
      logger.error('Error checking user active status', { error, userId: id });
      return false;
    }
  }

  async updateNextcloudCredentials(userId: string, loginName: string, appPassword: string): Promise<void> {
    try {
      await pool.query(
        `UPDATE users SET nextcloud_login_name = $2, nextcloud_app_password = $3 WHERE id = $1`,
        [userId, loginName, appPassword],
      );
      logger.debug('Updated Nextcloud credentials', { userId });
    } catch (error) {
      logger.error('Error updating Nextcloud credentials', { error, userId });
      throw new Error('Failed to update Nextcloud credentials');
    }
  }

  async getNextcloudCredentials(userId: string): Promise<{ loginName: string; appPassword: string } | null> {
    try {
      const result = await pool.query(
        `SELECT nextcloud_login_name, nextcloud_app_password FROM users WHERE id = $1`,
        [userId],
      );

      if (result.rows.length === 0) {
        return null;
      }

      const row = result.rows[0];
      if (!row.nextcloud_login_name || !row.nextcloud_app_password) {
        return null;
      }

      return { loginName: row.nextcloud_login_name, appPassword: row.nextcloud_app_password };
    } catch (error) {
      logger.error('Error getting Nextcloud credentials', { error, userId });
      throw new Error('Failed to get Nextcloud credentials');
    }
  }

  async clearNextcloudCredentials(userId: string): Promise<void> {
    try {
      await pool.query(
        `UPDATE users SET nextcloud_login_name = NULL, nextcloud_app_password = NULL WHERE id = $1`,
        [userId],
      );
      logger.debug('Cleared Nextcloud credentials', { userId });
    } catch (error) {
      logger.error('Error clearing Nextcloud credentials', { error, userId });
      throw new Error('Failed to clear Nextcloud credentials');
    }
  }

  /**
   * Purge all data associated with a user, keeping the users record itself.
   * Financial/audit records (buchhaltung, bestellung_historie, audit_log) are preserved.
   */
  async purgeUserData(userId: string, requestingUserId: string): Promise<Record<string, number>> {
    if (userId === requestingUserId) {
      throw new Error('Cannot purge your own data');
    }

    const user = await this.findById(userId);
    if (!user) {
      throw new Error('User not found');
    }

    const client = await pool.connect();
    try {
      await client.query('BEGIN');

      const results: Record<string, number> = {};

      // Helper to delete and track row counts
      const purge = async (table: string, column = 'user_id') => {
        const r = await client.query(`DELETE FROM ${table} WHERE ${column} = $1`, [userId]);
        results[table] = r.rowCount ?? 0;
      };

      // User-owned data tables (DELETE rows)
      await purge('notifications');
      // Keep mitglieder_profile row but NULL FDISK-synced fields
      await client.query(
        `UPDATE mitglieder_profile SET dienstgrad = NULL, dienstgrad_seit = NULL, eintrittsdatum = NULL, austrittsdatum = NULL, geburtsdatum = NULL, fuehrerscheinklassen = '{}' WHERE user_id = $1`,
        [userId]
      );
      results['mitglieder_profile_fdisk_cleared'] = 1;

      await purge('dienstgrad_verlauf');
      await purge('atemschutz_traeger');
      await purge('ausbildung');
      await purge('untersuchungen');
      await purge('fahrgenehmigungen');
      await purge('befoerderungen');
      await purge('einsatz_personal');
      await purge('uebung_teilnahmen');
      await purge('veranstaltung_teilnahmen');
      await purge('veranstaltung_ical_tokens');
      await purge('fahrzeug_ical_tokens');
      // Delete child positions before parent anfragen (FK constraint)
      const posResult = await client.query(
        'DELETE FROM ausruestung_anfrage_positionen WHERE anfrage_id IN (SELECT id FROM ausruestung_anfragen WHERE anfrager_id = $1)',
        [userId]
      );
      results['ausruestung_anfrage_positionen'] = posResult.rowCount ?? 0;
      await purge('ausruestung_anfragen', 'anfrager_id');
      await purge('persoenliche_ausruestung');

      // Clear user preferences (widget layout, etc.)
      await client.query(
        `UPDATE users SET preferences = NULL, nextcloud_login_name = NULL, nextcloud_app_password = NULL WHERE id = $1`,
        [userId]
      );
      results['preferences_cleared'] = 1;

      await client.query('COMMIT');

      logger.info('User data purged', { userId, by: requestingUserId, results });

      return results;
    } catch (error) {
      await client.query('ROLLBACK');
      logger.error('Error purging user data', { error, userId, requestingUserId });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * Sync Authentik groups for a user
   */
  async updateGroups(id: string, groups: string[]): Promise<void> {
    try {
      await pool.query(
        `UPDATE users SET authentik_groups = $1 WHERE id = $2`,
        [groups, id]
      );
      logger.debug('Updated authentik_groups', { userId: id });
    } catch (error) {
      logger.error('Error updating authentik_groups', { error, userId: id });
      throw new Error('Failed to update user groups');
    }
  }

  async findFdiskUserByName(givenName: string, familyName: string): Promise<User | null> {
    try {
      const result = await pool.query<User>(
        `SELECT id, email, authentik_sub, name, preferred_username, given_name,
                family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
                is_active, last_login_at, created_at, updated_at, preferences,
                authentik_groups, sync_source
         FROM users
         WHERE sync_source = 'fdisk'
           AND last_login_at IS NULL
           AND LOWER(given_name) = LOWER($1)
           AND LOWER(family_name) = LOWER($2)`,
        [givenName, familyName]
      );
      if (result.rows.length === 1) return result.rows[0];
      if (result.rows.length > 1) {
        logger.warn('Ambiguous FDISK name match on login — skipping merge', { givenName, familyName, count: result.rows.length });
      }
      return null;
    } catch (error) {
      logger.error('Error finding FDISK user by name', { error });
      return null;
    }
  }

  async claimFdiskUser(userId: string, authentikSub: string, email: string): Promise<User | null> {
    try {
      const result = await pool.query<User>(
        `UPDATE users SET
           authentik_sub = $2,
           email = $3
         WHERE id = $1 AND sync_source = 'fdisk' AND last_login_at IS NULL
         RETURNING id, email, authentik_sub, name, preferred_username, given_name,
                   family_name, profile_picture_url, refresh_token, refresh_token_expires_at,
                   is_active, last_login_at, created_at, updated_at, preferences,
                   authentik_groups, sync_source`,
        [userId, authentikSub, email]
      );
      if (result.rows.length === 0) return null;
      logger.info('FDISK user claimed by Authentik login', { userId, authentikSub });
      return result.rows[0];
    } catch (error) {
      logger.error('Error claiming FDISK user', { error, userId });
      return null;
    }
  }
}

export default new UserService();
