import { DELETED_SITE_RECORD_CID } from '@wispplace/constants'
import type { SQL } from 'bun'

/**
 * Read-only queries used by primary helpers and by the explicitly eventual
 * presentation API. This module has no database-pool imports, so it cannot
 * accidentally create a second client or use the wrong endpoint.
 */

type SiteDomain = {
	type: 'wisp' | 'custom'
	domain: string
	verified?: boolean
	id?: string
}

type SitePresentationRow = {
	did: string
	rkey: string
	display_name: string | null
	created_at: number | string | null
	updated_at: number | string | null
	domains: SiteDomain[]
}

type SitesWithDomainsQueryRow = Omit<SitePresentationRow, 'domains'> & {
	domain_type: 'wisp' | 'custom' | null
	domain: string | null
	domain_id: string | null
	domain_verified: boolean | null
}

type WebhookEventLogRow = {
	rkey: string
	url: string
	event_kind: string
	event_did: string
	event_collection: string
	event_rkey: string
	cid: string | null
	status: string
	delivered_at: string
}

/**
 * Builds reusable read queries for both primary helpers and the narrower
 * eventual-read facade. Only db.ts decides which methods are safe to expose
 * through the replica-capable facade.
 */
export const createPresentationReadQueries = (sql: SQL) => {
	const getDomainByDid = async (did: string): Promise<string | null> => {
		const rows = await sql`SELECT domain FROM domains WHERE did = ${did} ORDER BY created_at ASC LIMIT 1`
		return rows[0]?.domain ?? null
	}

	const getAllWispDomains = async (did: string): Promise<Array<{ domain: string; rkey: string | null }>> => {
		const rows = await sql`SELECT domain, rkey FROM domains WHERE did = ${did} ORDER BY created_at ASC`
		return rows
	}

	const countWispDomains = async (did: string): Promise<number> => {
		const rows = await sql`SELECT COUNT(*) as count FROM domains WHERE did = ${did}`
		return Number(rows[0]?.count ?? 0)
	}

	const isDomainRegistered = async (domain: string) => {
		const domainLower = domain.toLowerCase().trim()

		// Preserve the current wisp-domain precedence if inconsistent data exists.
		const wispDomain = await sql`
			SELECT did, domain, rkey FROM domains WHERE domain = ${domainLower}
		`

		if (wispDomain.length > 0) {
			return {
				registered: true as const,
				type: 'wisp' as const,
				domain: wispDomain[0].domain,
				did: wispDomain[0].did,
				rkey: wispDomain[0].rkey,
			}
		}

		const customDomain = await sql`
			SELECT id, domain, did, rkey, verified FROM custom_domains WHERE domain = ${domainLower}
		`

		if (customDomain.length > 0) {
			return {
				registered: true as const,
				type: 'custom' as const,
				domain: customDomain[0].domain,
				did: customDomain[0].did,
				rkey: customDomain[0].rkey,
				verified: customDomain[0].verified,
			}
		}

		return { registered: false as const }
	}

	const getCustomDomainsByDid = async (did: string) => {
		const rows = await sql`SELECT * FROM custom_domains WHERE did = ${did} ORDER BY created_at DESC`
		return rows
	}

	const getCustomDomainInfo = async (domain: string) => {
		const rows = await sql`SELECT * FROM custom_domains WHERE domain = ${domain.toLowerCase()}`
		return rows[0] ?? null
	}

	const getSitesByDid = async (did: string) => {
		const rows = await sql`
			SELECT
				did,
				rkey,
				rkey AS display_name,
				cached_at AS created_at,
				updated_at
			FROM site_cache
			WHERE did = ${did} AND record_cid <> ${DELETED_SITE_RECORD_CID}
			ORDER BY cached_at DESC
		`
		return rows
	}

	/**
	 * One bulk query for a site's list view and all of its domain badges. The
	 * lateral UNION avoids both the former 1 + 2N request pattern and a
	 * wisp-domain × custom-domain join explosion.
	 */
	const getSitesWithDomainsByDid = async (did: string): Promise<SitePresentationRow[]> => {
		const rows = await sql<SitesWithDomainsQueryRow[]>`
			SELECT
				s.did,
				s.rkey,
				s.rkey AS display_name,
				s.cached_at AS created_at,
				s.updated_at,
				site_domain.domain_type,
				site_domain.domain,
				site_domain.domain_id,
				site_domain.domain_verified
			FROM site_cache s
			LEFT JOIN LATERAL (
				SELECT 'wisp'::TEXT AS domain_type, d.domain, NULL::TEXT AS domain_id, NULL::BOOLEAN AS domain_verified
				FROM domains d
				WHERE d.did = s.did AND d.rkey = s.rkey
				UNION ALL
				SELECT 'custom'::TEXT AS domain_type, cd.domain, cd.id AS domain_id, cd.verified AS domain_verified
				FROM custom_domains cd
				WHERE cd.did = s.did AND cd.rkey = s.rkey
			) site_domain ON true
			WHERE s.did = ${did} AND s.record_cid <> ${DELETED_SITE_RECORD_CID}
			ORDER BY s.cached_at DESC, site_domain.domain_type ASC NULLS LAST, site_domain.domain ASC NULLS LAST
		`

		const sites = new Map<string, SitePresentationRow>()
		const domainKeys = new Map<string, Set<string>>()
		for (const row of rows) {
			const key = `${row.did}:${row.rkey}`
			let site = sites.get(key)
			if (!site) {
				site = {
					did: row.did,
					rkey: row.rkey,
					display_name: row.display_name,
					created_at: row.created_at,
					updated_at: row.updated_at,
					domains: [],
				}
				sites.set(key, site)
				domainKeys.set(key, new Set())
			}

			if (!row.domain_type || !row.domain) continue
			const seen = domainKeys.get(key)!
			const domainKey = `${row.domain_type}:${row.domain_id ?? row.domain}`
			if (seen.has(domainKey)) continue
			seen.add(domainKey)

			if (row.domain_type === 'wisp') {
				site.domains.push({ type: 'wisp', domain: row.domain })
			} else {
				site.domains.push({
					type: 'custom',
					domain: row.domain,
					verified: row.domain_verified ?? false,
					id: row.domain_id ?? undefined,
				})
			}
		}

		return [...sites.values()]
	}

	const getDomainsBySite = async (did: string, rkey: string): Promise<SiteDomain[]> => {
		const [wispDomain, customDomains] = await Promise.all([
			sql`
				SELECT domain, rkey FROM domains
				WHERE did = ${did} AND rkey = ${rkey}
			`,
			sql`
				SELECT id, domain, verified FROM custom_domains
				WHERE did = ${did} AND rkey = ${rkey}
				ORDER BY created_at DESC
			`,
		])
		const domains: SiteDomain[] = []

		if (wispDomain.length > 0) {
			domains.push({
				type: 'wisp',
				domain: wispDomain[0].domain,
			})
		}

		for (const customDomain of customDomains) {
			domains.push({
				type: 'custom',
				domain: customDomain.domain,
				verified: customDomain.verified,
				id: customDomain.id,
			})
		}

		return domains
	}

	const getDomainCountBySite = async (did: string, rkey: string) => {
		const [wispCount, customCount] = await Promise.all([
			sql`
				SELECT COUNT(*) as count FROM domains
				WHERE did = ${did} AND rkey = ${rkey}
			`,
			sql`
				SELECT COUNT(*) as count FROM custom_domains
				WHERE did = ${did} AND rkey = ${rkey}
			`,
		])

		return {
			wisp: Number(wispCount[0]?.count || 0),
			custom: Number(customCount[0]?.count || 0),
			total: Number(wispCount[0]?.count || 0) + Number(customCount[0]?.count || 0),
		}
	}

	const isSupporter = async (did: string): Promise<boolean> => {
		const rows = await sql`SELECT 1 FROM supporter WHERE did = ${did} LIMIT 1`
		return rows.length > 0
	}

	const getAllSupporters = async () => {
		const rows = await sql`SELECT * FROM supporter ORDER BY created_at ASC`
		return rows
	}

	const getUserStatus = async (did: string) => {
		const [sites, domain] = await Promise.all([getSitesByDid(did), getDomainByDid(did)])
		return { sites, domain }
	}

	const getDomainsForDid = async (did: string) => {
		const [wispDomains, customDomains] = await Promise.all([getAllWispDomains(did), getCustomDomainsByDid(did)])
		return { wispDomains, customDomains }
	}

	const getDomainStatus = async (domain: string) => {
		const registration = await isDomainRegistered(domain)
		const customDomain =
			registration.registered && registration.type === 'custom' ? await getCustomDomainInfo(domain) : null
		return { registration, customDomain }
	}

	const getAdminDatabaseReport = async () => {
		const [siteCount, wispSubdomains, customDomainCount, siteSettingsCount, recentSites, recentDomains] =
			await Promise.all([
				sql<
					Array<{ count: string | number }>
				>`SELECT COUNT(*) as count FROM site_cache WHERE record_cid <> ${DELETED_SITE_RECORD_CID}`,
				sql<Array<{ count: string | number }>>`SELECT COUNT(*) as count FROM domains WHERE domain LIKE '%.wisp.place'`,
				sql<Array<{ count: string | number }>>`SELECT COUNT(*) as count FROM custom_domains WHERE verified = true`,
				sql<Array<{ count: string | number }>>`SELECT COUNT(*) as count FROM site_settings_cache`,
				sql`
				SELECT
					s.did,
					s.rkey,
					s.rkey as display_name,
					s.cached_at as created_at,
					d.domain as subdomain,
					cd.domain as custom_domain
				FROM site_cache s
				LEFT JOIN domains d ON s.did = d.did AND s.rkey = d.rkey AND d.domain LIKE '%.wisp.place'
				LEFT JOIN custom_domains cd ON s.did = cd.did AND s.rkey = cd.rkey AND cd.verified = true
				WHERE s.record_cid <> ${DELETED_SITE_RECORD_CID}
				ORDER BY s.cached_at DESC
				LIMIT 10
			`,
				sql`SELECT domain, did, rkey, verified, created_at FROM custom_domains ORDER BY created_at DESC LIMIT 10`,
			])

		return {
			stats: {
				totalSites: siteCount[0]?.count ?? 0,
				totalWispSubdomains: wispSubdomains[0]?.count ?? 0,
				totalCustomDomains: customDomainCount[0]?.count ?? 0,
				totalSiteCache: siteCount[0]?.count ?? 0,
				totalSiteSettingsCache: siteSettingsCount[0]?.count ?? 0,
			},
			recentSites,
			recentDomains,
		}
	}

	const getAdminSites = async (limit: number, offset: number) => {
		const [sites, customDomains] = await Promise.all([
			sql`
				SELECT
					s.did,
					s.rkey,
					s.rkey as display_name,
					s.cached_at as created_at,
					d.domain as subdomain,
					cd.domain as custom_domain
				FROM site_cache s
				LEFT JOIN domains d ON s.did = d.did AND s.rkey = d.rkey AND d.domain LIKE '%.wisp.place'
				LEFT JOIN custom_domains cd ON s.did = cd.did AND s.rkey = cd.rkey AND cd.verified = true
				WHERE s.record_cid <> ${DELETED_SITE_RECORD_CID}
				ORDER BY s.cached_at DESC
				LIMIT ${limit} OFFSET ${offset}
			`,
			sql`
				SELECT
					domain,
					did,
					rkey,
					verified,
					created_at
				FROM custom_domains
				ORDER BY created_at DESC
				LIMIT ${limit} OFFSET ${offset}
			`,
		])

		return { sites, customDomains }
	}

	const getWebhookEventHistory = async (ownerDid: string): Promise<WebhookEventLogRow[]> => {
		return await sql<WebhookEventLogRow[]>`
			SELECT rkey, url, event_kind, event_did, event_collection, event_rkey, cid, status, delivered_at
			FROM webhook_event_logs
			WHERE owner_did = ${ownerDid}
			ORDER BY delivered_at DESC
			LIMIT 100
		`
	}

	return {
		getDomainByDid,
		getAllWispDomains,
		countWispDomains,
		isDomainRegistered,
		getCustomDomainsByDid,
		getCustomDomainInfo,
		getSitesByDid,
		getSitesWithDomainsByDid,
		getDomainsBySite,
		getDomainCountBySite,
		isSupporter,
		getAllSupporters,
		getUserStatus,
		getDomainsForDid,
		getDomainStatus,
		getAdminDatabaseReport,
		getAdminSites,
		getWebhookEventHistory,
	}
}