import { DuckDBInstance } from '@duckdb/node-api'
import fs from 'fs/promises'
import path from 'path'

// generates XRM artifacts from a duckdb database
// sources can be used to ensure consistency with the actual database schema
// vocabularies and mappings can be used to bootstrap the XRM project, but should be manually edited afterwards

if (process.argv.length <  5) {
  console.error('Usage: node lib/duck2xrm.js <database> [-s <sources>] [-v <vocabularies>] [-m <mappings>]')
  console.error('Example: node lib/duck2xrm.js example.duckdb -s mappings/sources.xrm')
  process.exit(1)
}

const args = process.argv.slice(2)
const database = args[0]
const sIndex = args.indexOf('-s')
const vIndex = args.indexOf('-v')
const mIndex = args.indexOf('-m')
const sources = sIndex !== -1 ? args[sIndex + 1] : undefined
const vocabularies = vIndex !== -1 ? args[vIndex + 1] : undefined
const mappings = mIndex !== -1 ? args[mIndex + 1] : undefined


const database_name = replaceSpecialChars(path.parse(database).name)

async function main() {
    const tables = await getTables()
    if (sources) {
        await writeSources(tables, sources)
    }
    if(vocabularies) {
       await writeVocabularies(tables, vocabularies)
    }
    if(mappings) {
       await writeMappings(tables, mappings)
    }
}

main().catch(err => {
  console.error(err)
  process.exit(1)
})

async function getTables() {
    const db = await DuckDBInstance.create(database)
    const connection = await db.connect()

    // Get all table names
    const tables = {}
    const tableRows = await connection.runAndReadAll('SHOW TABLES')
    const tableNames = tableRows.getRows().map(([name]) => name)

    // Get columns for each table
    for (const table of tableNames) {
        const colRows = await connection.runAndReadAll(`PRAGMA table_info('${table}')`)
        const columns = colRows.getRows().map(row => row[1]) // column name is at index 1
        tables[table] = columns
    }
    return tables
}

async function writeSources(tables, filepath) {
    const sourceTemplate = `
    logical-source {table} {
        source "{table}"

        referenceables
        {columns}
    }\n`
    let sourceContent = "// generated by duck2xrm.js\n"
    sourceContent += `source-group ${database_name} {\n`
    sourceContent += 'type rdb\n'
    for (const table in tables) {
        const logicalSource = sourceTemplate
            .replace(/{table}/g, table)
            .replace('{columns}', tables[table].map(formatColumnName).join('\n\t\t'))
        sourceContent += logicalSource
    }
    sourceContent += "}\n\n"
    await fs.writeFile(filepath, sourceContent)
}

async function writeMappings(tables, filepath) {
    let mappingsContent = "// generated by duck2xrm.js\n\noutput r2rml\n\n"
    for (const table in tables) {
        const [key1, key2] = tables[table]
        mappingsContent += `map ${table} from ${database_name}.${table} {\n`
        mappingsContent += `\t// TODO: use proper key fields in the template\n`
        mappingsContent += `\tsubject template "http://example.com/${table}/{0}/{1}" with ${key1} ${key2};\n\n`
        mappingsContent += `\ttypes ${database_name}.${table}\n\n`
        mappingsContent += `\tproperties\n`
        for (const col of tables[table]) {
            mappingsContent += `\t\t${database_name}.${replaceSpecialChars(col)} from ${col};\n`
        }
        mappingsContent += "\t}\n\n"
    }
    await fs.writeFile(filepath, mappingsContent)
}

async function writeVocabularies(tables, filepath) {
    let vocabContent = "// generated by duck2xrm.js\n\n"
    vocabContent += `vocabulary ${database_name} {\n`
    vocabContent += `\tprefix "${database_name}" "http://example.com/${database_name}#"\n\n`
    vocabContent += "\tclasses\n"
    for (const table in tables) {
        vocabContent += `\t\t${table}\n`
    }
    vocabContent += "\n\tproperties\n"
    const columns = new Set()
    for (const table in tables) {
        tables[table].forEach(col => columns.add(replaceSpecialChars(col)))
    }
    for (const col of columns) {
        vocabContent += `\t\t${col}\n`
    }
    vocabContent += "}\n"
    await fs.writeFile(filepath, vocabContent)
}

function replaceSpecialChars(s) {
  return s.replace(/[\s\-\,\;]/g, '_')
}

function formatColumnName(name) {
  const replaced = replaceSpecialChars(name)
  return replaced === name ? name : `${replaced} "${name}"`
}
