import * as bcrypt from 'bcryptjs';
import { ClientProvider } from '#providers/ClientProvider';
import { Client } from '#models/Client';
import { CreateClientInput, UpdateClientInput } from '#contracts/admin/schema';
import { BaseProvider } from '#templates/base/provider';
import { DataTypes, Model, Optional, Sequelize } from 'sequelize';
import sequelize from '#services/database/sequelize/sequelizeService';
import { QueryTypes } from 'sequelize';

export interface ProjectDbConnectionAttributes {
	id: string;
	app_code: string;
	provider: string;
	connection_string_env: string;
	user_table: string;
	user_id_column: string;
	email_column: string;
	json_profile_column: string | null;
	status: string;
	created_at: Date;
	updated_at: Date;
}

type ProjectDbConnectionOptional = 'id' | 'created_at' | 'updated_at' | 'provider' | 'user_table' | 'user_id_column' | 'email_column' | 'json_profile_column' | 'status';
export type ProjectDbConnectionCreationAttributes = Optional<ProjectDbConnectionAttributes, ProjectDbConnectionOptional>;
export class ProjectDbConnection extends Model<ProjectDbConnectionAttributes, ProjectDbConnectionCreationAttributes> {
	declare id: string;
	declare app_code: string;
	declare provider: string;
	declare connection_string_env: string;
	declare user_table: string;
	declare user_id_column: string;
	declare email_column: string;
	declare json_profile_column: string | null;
	declare status: string;
	declare created_at: Date;
	declare updated_at: Date;

	static initModel(sequelize: Sequelize): typeof ProjectDbConnection {
		return ProjectDbConnection.init(
			{
				id: { type: DataTypes.UUID, allowNull: false, defaultValue: DataTypes.UUIDV4, primaryKey: true },
				app_code: { type: DataTypes.TEXT, allowNull: false },
				provider: { type: DataTypes.TEXT, allowNull: false, defaultValue: 'postgresql' },
				connection_string_env: { type: DataTypes.TEXT, allowNull: false },
				user_table: { type: DataTypes.TEXT, allowNull: false, defaultValue: 'users' },
				user_id_column: { type: DataTypes.TEXT, allowNull: false, defaultValue: 'id' },
				email_column: { type: DataTypes.TEXT, allowNull: false, defaultValue: 'email' },
				json_profile_column: { type: DataTypes.TEXT, allowNull: true },
				status: { type: DataTypes.TEXT, allowNull: false, defaultValue: 'active' },
				created_at: { type: DataTypes.DATE, allowNull: false, defaultValue: DataTypes.NOW },
				updated_at: { type: DataTypes.DATE, allowNull: false, defaultValue: DataTypes.NOW },
			},
			{ sequelize, tableName: 'project_db_connections', timestamps: false },
		);
	}
}

export class AdminClientService {
	private readonly clientProvider = new ClientProvider();
	private readonly BCRYPT_ROUNDS = 12;

	async list(opts: {
		page: number;
		pageSize: number;
		status?: string;
		app_code?: string;
		search?: string;
	}) {
		const where: Record<string, unknown> = {};
		if (opts.status) where.status = opts.status;
		if (opts.app_code) where.app_code = opts.app_code;

		if (opts.search) {
			return this.clientProvider.getPaginatedWithSearch({
				where,
				searchFields: ['name', 'app_code', 'client_id'],
				searchQuery: opts.search,
				page: opts.page,
				pageSize: opts.pageSize,
				sortField: 'created_at',
				sortOrder: 'DESC',
			});
		}

		return this.clientProvider.getPaginatedWithSearch({
			where,
			page: opts.page,
			pageSize: opts.pageSize,
			sortField: 'created_at',
			sortOrder: 'DESC',
		});
	}

	async getById(id: string): Promise<Client | null> {
		// Accept either the internal UUID `id` or the public `client_id`.
		const where: Record<string, unknown> = /^[0-9a-f-]{36}$/i.test(id) ? { id } : { client_id: id };
		return this.clientProvider.getOne({ where });
	}

	async create(input: CreateClientInput): Promise<Client> {
		const passwordHash = input.client_secret ? await bcrypt.hash(input.client_secret, this.BCRYPT_ROUNDS) : null;

		const data: Partial<Client['dataValues']> = {
			client_id: input.client_id,
			client_secret_hash: passwordHash,
			name: input.name,
			app_code: input.app_code,
			redirect_uris: input.redirect_uris,
			post_logout_redirect_uris: input.post_logout_redirect_uris,
			grant_types: input.grant_types,
			response_types: input.response_types,
			scopes: input.scopes,
			token_endpoint_auth_method: input.token_endpoint_auth_method,
			require_pkce: input.require_pkce,
			status: input.status,
		};

		return this.clientProvider.create(data as any);
	}

	async update(id: string, input: UpdateClientInput): Promise<Client> {
		const updateData: Record<string, unknown> = {};

		if (input.name !== undefined) updateData.name = input.name;
		if (input.redirect_uris !== undefined) updateData.redirect_uris = input.redirect_uris;
		if (input.post_logout_redirect_uris !== undefined) updateData.post_logout_redirect_uris = input.post_logout_redirect_uris;
		if (input.scopes !== undefined) updateData.scopes = input.scopes;
		if (input.token_endpoint_auth_method !== undefined) updateData.token_endpoint_auth_method = input.token_endpoint_auth_method;
		if (input.require_pkce !== undefined) updateData.require_pkce = input.require_pkce;
		if (input.status !== undefined) updateData.status = input.status;
		if (input.client_secret !== undefined && input.client_secret !== null) {
			updateData.client_secret_hash = await bcrypt.hash(input.client_secret, this.BCRYPT_ROUNDS);
		}

		const isUuid = /^[0-9a-f-]{36}$/i.test(id);
		const existing = isUuid
			? await this.clientProvider.getById({ id, throwErrorIfNotFound: true })
			: await this.clientProvider.getOne({ where: { client_id: id } });
		if (!existing) throw new Error('Client not found');

		if (Object.keys(updateData).length === 0) {
			return existing;
		}

		const result = await this.clientProvider.updateById(existing.id, updateData);
		if (!result) throw new Error('Client not found');
		return result;
	}

	async delete(id: string): Promise<void> {
		const isUuid = /^[0-9a-f-]{36}$/i.test(id);
		const existing = isUuid
			? await this.clientProvider.getById({ id, throwErrorIfNotFound: false })
			: await this.clientProvider.getOne({ where: { client_id: id } });
		if (!existing) throw new Error('Client not found');
		await this.clientProvider.delete(existing.id, { force: true });
	}

	async regenerateSecret(id: string): Promise<{ client_secret: string }> {
		const client = await this.clientProvider.getById({ id, throwErrorIfNotFound: false });
		if (!client) throw new Error('Client not found');

		const newSecret = crypto.randomUUID().replace(/-/g, '') + crypto.randomUUID().replace(/-/g, '');
		const hash = await bcrypt.hash(newSecret, this.BCRYPT_ROUNDS);

		await this.clientProvider.updateById(id, { client_secret_hash: hash } as any);
		return { client_secret: newSecret };
	}
}

export class AdminDbConnectionService {
	private readonly poolSvc: typeof import('#services/database/multiPoolService').MultiPoolService;

	constructor() {
		this.poolSvc = {} as any; // lazy-loaded
	}

	async list(opts: { page: number; pageSize: number; status?: string }) {
		const where: Record<string, unknown> = {};
		if (opts.status) where.status = opts.status;

		const offset = (opts.page - 1) * opts.pageSize;
		const rows = await sequelize.query<ProjectDbConnectionAttributes>(
			`SELECT * FROM project_db_connections WHERE (:status IS NULL OR status = :status) ORDER BY created_at DESC LIMIT :limit OFFSET :offset`,
			{
				replacements: { status: opts.status || null, limit: opts.pageSize, offset },
				type: QueryTypes.SELECT,
			},
		);

		const countResult = await sequelize.query<{ count: string }>(
			`SELECT COUNT(*) as count FROM project_db_connections WHERE (:status IS NULL OR status = :status)`,
			{ replacements: { status: opts.status || null }, type: QueryTypes.SELECT },
		);

		const count = parseInt(countResult[0]?.count ?? '0', 10);

		return { rows, count, page: opts.page, pageSize: opts.pageSize };
	}

	async getById(id: string): Promise<ProjectDbConnectionAttributes | null> {
		const rows = await sequelize.query<ProjectDbConnectionAttributes>(
			`SELECT * FROM project_db_connections WHERE id = :id LIMIT 1`,
			{ replacements: { id }, type: QueryTypes.SELECT },
		);
		return rows[0] ?? null;
	}

	async create(input: {
		app_code: string;
		provider?: string;
		connection_string_env: string;
		user_table?: string;
		user_id_column?: string;
		email_column?: string;
		json_profile_column?: string;
		status?: string;
	}): Promise<ProjectDbConnectionAttributes> {
		const id = crypto.randomUUID();
		await sequelize.query(
			`INSERT INTO project_db_connections (id, app_code, provider, connection_string_env, user_table, user_id_column, email_column, json_profile_column, status)
       VALUES (:id, :app_code, :provider, :connection_string_env, :user_table, :user_id_column, :email_column, :json_profile_column, :status)`,
			{
				replacements: {
					id,
					app_code: input.app_code,
					provider: input.provider ?? 'postgresql',
					connection_string_env: input.connection_string_env,
					user_table: input.user_table ?? 'users',
					user_id_column: input.user_id_column ?? 'id',
					email_column: input.email_column ?? 'email',
					json_profile_column: input.json_profile_column ?? null,
					status: input.status ?? 'active',
				},
				type: QueryTypes.INSERT,
			},
		);

		const result = await this.getById(id);
		if (!result) throw new Error('Failed to create project DB connection');
		return result;
	}

	async update(id: string, input: Partial<{
		connection_string_env: string;
		user_table: string;
		user_id_column: string;
		email_column: string;
		json_profile_column: string;
		status: string;
	}>): Promise<ProjectDbConnectionAttributes> {
		const fields: string[] = [];
		const replacements: Record<string, unknown> = { id };

		if (input.connection_string_env !== undefined) { fields.push('connection_string_env = :connection_string_env'); replacements.connection_string_env = input.connection_string_env; }
		if (input.user_table !== undefined) { fields.push('user_table = :user_table'); replacements.user_table = input.user_table; }
		if (input.user_id_column !== undefined) { fields.push('user_id_column = :user_id_column'); replacements.user_id_column = input.user_id_column; }
		if (input.email_column !== undefined) { fields.push('email_column = :email_column'); replacements.email_column = input.email_column; }
		if (input.json_profile_column !== undefined) { fields.push('json_profile_column = :json_profile_column'); replacements.json_profile_column = input.json_profile_column; }
		if (input.status !== undefined) { fields.push('status = :status'); replacements.status = input.status; }

		if (fields.length === 0) {
			const result = await this.getById(id);
			if (!result) throw new Error('Not found');
			return result;
		}

		fields.push('updated_at = NOW()');
		await sequelize.query(
			`UPDATE project_db_connections SET ${fields.join(', ')} WHERE id = :id`,
			{ replacements, type: QueryTypes.UPDATE },
		);

		const result = await this.getById(id);
		if (!result) throw new Error('Not found');
		return result;
	}

	async delete(id: string): Promise<void> {
		await sequelize.query(`DELETE FROM project_db_connections WHERE id = :id`, { replacements: { id }, type: QueryTypes.DELETE });
	}

	async testConnection(connectionString: string): Promise<{ success: boolean; latencyMs: number; error?: string }> {
		const { Sequelize } = require('sequelize') as typeof import('sequelize');
		let testPool: any;

		try {
			testPool = new Sequelize(connectionString, { logging: false, pool: { max: 1, min: 0, acquire: 5000, idle: 1000 } });
			const start = Date.now();
			await testPool.authenticate();
			const latencyMs = Date.now() - start;
			return { success: true, latencyMs };
		} catch (err: any) {
			return { success: false, latencyMs: 0, error: err.message };
		} finally {
			if (testPool) {
				await testPool.close().catch(() => {});
			}
		}
	}
}
