import type { MaybeRefOrGetter } from 'vue'
import type { BglFormSchemaT, Field } from '../types'
import { ref, toValue } from 'vue'
import { appendScript, downloadFile, getNestedValue } from '../utils'

declare global {
	interface Window {
		XLSX: any
	}
}

interface SheetData {
	headers: string[]
	data: Record<string, any>[]
}

const objKeys = (object: object) => Object.keys(object)
const strToTitle = (str?: string) => str?.split(' ').map(s => s.charAt(0).toUpperCase() + s.slice(1)).join(' ')

function formatData<T>(data: T[], schema?: BglFormSchemaT<T>) {
	return schema
		? data.map((row) => {
			const newRow: any = {}
			schema.forEach((sf) => {
				const { id, transform, label } = sf as unknown as Field<T>
				if (id as any) {
					// Support for dot notation to access nested properties
					const value = getNestedValue(row, id as any)
					const key = (label as any) || (id as any)
					newRow[key] = transform?.(value, row) || value || ''
				}
			})
			return newRow
		})
		: data
}

function autoDetectSchema<T>(data: T[]): BglFormSchemaT<T> {
	const headersSet = new Set<string>()
	data.forEach((row) => {
		Object.keys(row as Record<string, any>).forEach(key => headersSet.add(key))
	})
	const headers = Array.from(headersSet)
	const schema = headers.map(header => ({
		id: header,
		label: header,
	})) as BglFormSchemaT<T>

	return schema
}

export function useExcel() {
	const xlsxLoaded = ref(false)

	async function ensureXLSXLoaded() {
		if (!xlsxLoaded.value) {
			await appendScript('https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js')
			xlsxLoaded.value = true
		}
		return window.XLSX
	}

	async function downloadExcel<T>(data: T[], fileName = 'data', schemaFn?: MaybeRefOrGetter<BglFormSchemaT<T>>) {
		const schema = toValue(schemaFn) || autoDetectSchema(data)
		const XLSX = await ensureXLSXLoaded()

		const formattedData = formatData(data, schema)
		const headers = schema.length > 0
			? schema.map((sf) => {
				const { id, label } = sf as unknown as Field<T>
				return { v: (label as any) || (id as any), t: 's' }
			})
			: []
		const ws = XLSX.utils.json_to_sheet(formattedData)

		if (headers.length > 0) {
			XLSX.utils.sheet_add_aoa(ws, [headers.map(h => h.v)], { origin: 'A1' })
		}

		const wb = XLSX.utils.book_new()
		XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
		fileName = fileName.endsWith('.xlsx') ? fileName : `${fileName}.xlsx`
		XLSX.writeFile(wb, fileName)
	}

	async function downloadCSV(data: Record<string, any>[], fileName = 'data', schema?: BglFormSchemaT<any>) {
		const keys = schema ? (schema.map(sf => (sf as unknown as Field<any>).id).filter(Boolean) as string[]) : [...new Set(data.flatMap(objKeys))]
		const columns = schema
			? schema.map((sf) => {
				const { label, id } = sf as unknown as Field<any>
				return (label as any) || strToTitle(id as any)
			})
			: keys.map(strToTitle)

		const formattedData = formatData(data, schema)
		const csv = [
			columns.join(','),
			...formattedData.map(row => keys.map((key) => {
				const value = row[key as string] !== undefined ? row[key as string] : ''
				return `"${String(value).replace(/"/g, '""')}"`
			}).join(','))
		].join('\n')
		fileName = fileName.endsWith('.csv') ? fileName : `${fileName}.csv`
		const blob = new Blob([csv], { type: 'text/csv;charset=utf-8' })
		downloadFile(blob, fileName)
	}

	async function getWorkbook(file: File) {
		const XLSX = await ensureXLSXLoaded()
		const data = await file.arrayBuffer()
		return XLSX.read(data)
	}

	async function getSheetNames(file: File): Promise<string[]> {
		const workbook = await getWorkbook(file)
		return workbook.SheetNames
	}

	async function readSheetData(file: File, sheetName: string, hasHeaders = true): Promise<SheetData> {
		const XLSX = await ensureXLSXLoaded()
		const workbook = await getWorkbook(file)
		const worksheet = workbook.Sheets[sheetName]

		// Read the raw sheet data as arrays
		const rawSheetData = XLSX.utils.sheet_to_json(worksheet, { header: 1 }) as any[][]

		if (rawSheetData.length === 0) {
			return { headers: [], data: [] }
		}

		// Extract headers based on hasHeaders setting
		let headers: string[] = []
		let startRow = 0

		if (hasHeaders) {
			// Use first row as headers
			headers = rawSheetData[0].map(h => String(h || '').trim())
			startRow = 1
		} else {
			// Generate column letter headers (A, B, C...)
			const firstRow = rawSheetData[0]
			headers = firstRow.map((_, i) => String.fromCharCode(65 + i))
		}

		// Convert the raw data into objects
		const parsedData = []
		for (let i = startRow; i < rawSheetData.length; i++) {
			const row = rawSheetData[i]
			const rowData: Record<string, any> = {}
			for (let j = 0; j < headers.length; j++) {
				if (j < row.length) {
					rowData[headers[j]] = row[j]
				} else {
					rowData[headers[j]] = ''
				}
			}

			parsedData.push(rowData)
		}

		return {
			headers,
			data: parsedData
		}
	}

	async function readExcelFile(file: File): Promise<any[]> {
		const XLSX = await ensureXLSXLoaded()

		return new Promise((resolve, reject) => {
			const reader = new FileReader()

			reader.onload = (e) => {
				try {
					const data = new Uint8Array(e.target?.result as ArrayBuffer)
					const workbook = XLSX.read(data, { type: 'array' })

					const firstSheetName = workbook.SheetNames[0]
					const worksheet = workbook.Sheets[firstSheetName]
					const jsonData = XLSX.utils.sheet_to_json(worksheet)

					resolve(jsonData)
				} catch (error) {
					reject(error)
				}
			}

			reader.onerror = () => {
				reject(new Error('Failed to read file'))
			}

			reader.readAsArrayBuffer(file)
		})
	}

	// Helper function for date detection and conversion
	function isExcelSerialDate(value: any): boolean {
		return (
			typeof value === 'number'
			&& Number.isInteger(value)
			&& value >= 20000
			&& value <= 50000
		)
	}

	function excelSerialDateToJSDate(serial: number): Date {
		const excelEpoch = new Date(Date.UTC(1899, 11, 30)) // Excel counts from Dec 30, 1899
		const msPerDay = 24 * 60 * 60 * 1000
		return new Date(excelEpoch.getTime() + serial * msPerDay)
	}

	function formatDate(date: Date, includeTime = false): string {
		if (!(date instanceof Date) || Number.isNaN(date.getTime())) {
			return ''
		}

		const year = date.getFullYear()
		const month = String(date.getMonth() + 1).padStart(2, '0')
		const day = String(date.getDate()).padStart(2, '0')

		if (!includeTime) {
			return `${year}-${month}-${day}`
		}

		const hours = String(date.getHours()).padStart(2, '0')
		const minutes = String(date.getMinutes()).padStart(2, '0')
		const seconds = String(date.getSeconds()).padStart(2, '0')

		return `${year}-${month}-${day}T${hours}:${minutes}:${seconds}`
	}

	return {
		downloadExcel,
		downloadCSV,
		readExcelFile,
		ensureXLSXLoaded,
		getSheetNames,
		readSheetData,
		getWorkbook,
		// Date helpers
		isExcelSerialDate,
		excelSerialDateToJSDate,
		formatDate
	}
}