import sequelize from '#services/database/sequelize/sequelizeService';
import { MultiPoolService } from '#services/database/multiPoolService';
import { QueryTypes } from 'sequelize';

export class ProjectUserReaderService {
	async findUserByEmail(appCode: string, email: string): Promise<Record<string, unknown> | null> {
		const pool = MultiPoolService.getPool(appCode);
		const rows = await pool.query(
			`SELECT * FROM users WHERE lower(email) = lower($1) LIMIT 1`,
			{ bind: [email], type: QueryTypes.SELECT, raw: true },
		);
		return (rows as Record<string, unknown>[])[0] ?? null;
	}

	async findUserById(appCode: string, externalUserId: string): Promise<Record<string, unknown> | null> {
		const pool = MultiPoolService.getPool(appCode);
		const rows = await pool.query(
			`SELECT * FROM users WHERE id = $1 LIMIT 1`,
			{ bind: [externalUserId], type: QueryTypes.SELECT, raw: true },
		);
		return (rows as Record<string, unknown>[])[0] ?? null;
	}
}

export class ProjectUserMappingService {
	private readonly reader = new ProjectUserReaderService();

	async listMappingsForUser(ssoUserId: string): Promise<Array<{ client_id: string; external_user_id: string; external_email: string; created_at: Date }>> {
		const rows = await sequelize.query<{ client_id: string; external_user_id: string; external_email: string; created_at: Date }>(
			`SELECT client_id, external_user_id, external_email, created_at
       FROM user_app_mappings
       WHERE sso_user_id = $1
       ORDER BY created_at DESC`,
			{ bind: [ssoUserId], type: QueryTypes.SELECT },
		);
		return rows;
	}

	async getOrCreateMapping(ssoUserId: string, clientId: string, externalUserId: string, externalEmail: string): Promise<void> {
		const existing = await sequelize.query<{ id: string }>(
			`SELECT id FROM user_app_mappings WHERE client_id = $1 AND external_user_id = $2 LIMIT 1`,
			{ bind: [clientId, externalUserId], type: QueryTypes.SELECT },
		);
		if (existing.length > 0) return;

		await sequelize.query(
			`INSERT INTO user_app_mappings (id, sso_user_id, client_id, external_user_id, external_email)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (client_id, external_user_id) DO NOTHING`,
			{ bind: [crypto.randomUUID(), ssoUserId, clientId, externalUserId, externalEmail], type: QueryTypes.INSERT },
		);
	}

	async linkAccount(ssoUserId: string, appCode: string, email: string): Promise<{ external_user_id: string; already_linked: boolean }> {
		const extUser = await this.reader.findUserByEmail(appCode, email);
		if (!extUser) {
			throw new Error(`No user found with email ${email} in ${appCode}`);
		}

		const existing = await sequelize.query<{ id: string }>(
			`SELECT id FROM user_app_mappings WHERE sso_user_id = $1 AND client_id = (
        SELECT id FROM clients WHERE app_code = $2 LIMIT 1
      ) LIMIT 1`,
			{ bind: [ssoUserId, appCode], type: QueryTypes.SELECT },
		);

		const alreadyLinked = existing.length > 0;
		const externalUserId = String(extUser.id ?? extUser.user_id ?? extUser.userId ?? '');

		await this.getOrCreateMapping(
			ssoUserId,
			String(extUser.client_id ?? ''),
			externalUserId,
			email,
		);

		return { external_user_id: externalUserId, already_linked: alreadyLinked };
	}
}
