import { db } from '../db.ts';

/**
 * Types for notification_services table
 */
export interface NotificationService {
	id: string;
	name: string;
	service_type: string;
	enabled: number;
	config: string; // JSON string
	enabled_types: string; // JSON array string
	created_at: string;
	updated_at: string;
}

export interface NotificationServiceConfig {
	[key: string]: unknown;
}

export interface CreateNotificationServiceInput {
	id: string; // UUID generated by caller
	name: string;
	serviceType: string;
	enabled?: boolean;
	config: NotificationServiceConfig;
	enabledTypes: string[];
}

export interface UpdateNotificationServiceInput {
	name?: string;
	enabled?: boolean;
	config?: NotificationServiceConfig;
	enabledTypes?: string[];
}

/**
 * All queries for notification_services table
 */
export const notificationServicesQueries = {
	/**
	 * Get all notification services
	 */
	getAll(): NotificationService[] {
		return db.query<NotificationService>('SELECT * FROM notification_services ORDER BY created_at DESC');
	},

	/**
	 * Get all enabled notification services
	 */
	getAllEnabled(): NotificationService[] {
		return db.query<NotificationService>(
			'SELECT * FROM notification_services WHERE enabled = 1 ORDER BY created_at DESC'
		);
	},

	/**
	 * Get notification service by ID
	 */
	getById(id: string): NotificationService | undefined {
		return db.queryFirst<NotificationService>(
			'SELECT * FROM notification_services WHERE id = ?',
			id
		);
	},

	/**
	 * Get notification services by type
	 */
	getByType(serviceType: string): NotificationService[] {
		return db.query<NotificationService>(
			'SELECT * FROM notification_services WHERE service_type = ? ORDER BY created_at DESC',
			serviceType
		);
	},

	/**
	 * Create a new notification service
	 */
	create(input: CreateNotificationServiceInput): boolean {
		const affected = db.execute(
			`INSERT INTO notification_services (
				id, name, service_type, enabled, config, enabled_types
			) VALUES (?, ?, ?, ?, ?, ?)`,
			input.id,
			input.name,
			input.serviceType,
			input.enabled ? 1 : 0,
			JSON.stringify(input.config),
			JSON.stringify(input.enabledTypes)
		);

		return affected > 0;
	},

	/**
	 * Update a notification service
	 */
	update(id: string, input: UpdateNotificationServiceInput): boolean {
		const updates: string[] = [];
		const params: (string | number)[] = [];

		if (input.name !== undefined) {
			updates.push('name = ?');
			params.push(input.name);
		}
		if (input.enabled !== undefined) {
			updates.push('enabled = ?');
			params.push(input.enabled ? 1 : 0);
		}
		if (input.config !== undefined) {
			updates.push('config = ?');
			params.push(JSON.stringify(input.config));
		}
		if (input.enabledTypes !== undefined) {
			updates.push('enabled_types = ?');
			params.push(JSON.stringify(input.enabledTypes));
		}

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

		// Add updated_at
		updates.push('updated_at = CURRENT_TIMESTAMP');
		params.push(id); // id for WHERE clause

		const affected = db.execute(
			`UPDATE notification_services SET ${updates.join(', ')} WHERE id = ?`,
			...params
		);

		return affected > 0;
	},

	/**
	 * Delete a notification service
	 */
	delete(id: string): boolean {
		const affected = db.execute(
			'DELETE FROM notification_services WHERE id = ?',
			id
		);

		return affected > 0;
	},

	/**
	 * Check if a service name already exists (case-insensitive)
	 */
	existsByName(name: string, excludeId?: string): boolean {
		if (excludeId) {
			const result = db.queryFirst<{ count: number }>(
				'SELECT COUNT(*) as count FROM notification_services WHERE LOWER(name) = LOWER(?) AND id != ?',
				name,
				excludeId
			);
			return (result?.count ?? 0) > 0;
		}

		const result = db.queryFirst<{ count: number }>(
			'SELECT COUNT(*) as count FROM notification_services WHERE LOWER(name) = LOWER(?)',
			name
		);
		return (result?.count ?? 0) > 0;
	}
};
