import { describe, it, before } from "node:test"
import assert from "node:assert"
import path from "node:path"
import fs from "node:fs"
import { PlataModels, PlataSql } from ".."
import { PlataGlobalRuntime } from "../libs/runtime"

describe('PlataSql', () => {
    const originalDatabasePath = path.join(__dirname, 'assets', 'sql', 'example.db')
    const tmpDatabasePath = path.join(__dirname, 'assets', 'sql', 'example.dummy.db')
    const tmpAuxDatabasePath = path.join(__dirname, 'assets', 'sql', 'aux.dummy.db')

    fs.rmSync(tmpDatabasePath, {
        force: true,
        recursive: false,
    })

    fs.rmSync(tmpAuxDatabasePath, {
        force: true,
        recursive: false,
    })

    const plataPromise = (async () => {
        const g = global as any
    
        g.Plata = new PlataGlobalRuntime()
    })()

    const modelSql = new PlataModels.ModelTemplate({
        PK_ID: [ PlataModels.Required(), PlataModels.Int() ],
        VL_PRECO: [ PlataModels.Required(), PlataModels.Int() ],
        DS_OBS: [ PlataModels.Required(), PlataModels.VarChar(Infinity) ],
        TB_JOINTEST2: [{
            PK_ID: [ PlataModels.Required(), PlataModels.Int() ],
            FK_JOIN: [ PlataModels.Required(), PlataModels.Int() ],
        }]
    } as const, {} as const)

    const testFunc: Sql.SqlGetFunc<typeof modelSql> = async (builder, sql) => {
        const result = await sql.select('TABLE', modelSql).build(
            s => builder(s)
        ).toListAsync()

        if (result.errorID !== undefined) {
            return result
        }

        if (result.errors !== undefined) {
            return {
                errorID: '',
                msg: '',
                error: {}
            }
        }

        return result.itens
    }

    const getSqlite3 = () => {
        if (!fs.existsSync(tmpDatabasePath)) {
            fs.copyFileSync(originalDatabasePath, tmpDatabasePath)
        }

        return Plata.MakeDisposable(() => new PlataSql.Driver({
            client: 'sqlite3',
            connection: {
                filename: tmpDatabasePath,
                pool: {
                    min: 0,
                    max: Infinity
                }
            },
            useNullAsDefault: true,
        }, false))
    }

    const sqlLite3Aux = () => {
        if (!fs.existsSync(tmpAuxDatabasePath)) {
            fs.copyFileSync(originalDatabasePath, tmpAuxDatabasePath)
        }

        return Plata.MakeDisposable(() => new PlataSql.Driver({
            client: 'sqlite3',
            connection: {
                filename: tmpAuxDatabasePath
            },
            useNullAsDefault: true,
        }, false))
    }

    before(async () => {
        await plataPromise
        Plata.config.ENV = 'test'
    })

    it('Insert', async () => {
        using sql = getSqlite3()

        const tabela = 'TB_INSERT'

        const antes = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(antes.errorID, undefined)
        assert.strictEqual(antes.errors, undefined)

        const insert = await sql.insert(tabela, modelSql, [{
            VL_PRECO: 2,
            DS_OBS: 'B'
        }])

        assert.strictEqual(insert.errorID, undefined)
        assert.strictEqual(insert.length, 1)

        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)

        // Add TB_JOINTEST2 in insert itens
        const i = insert.map(r => {
            return { 
                ...r,
                TB_JOINTEST2: []
            }
        }) 

        const esperado = [...antes.itens, ...i]

        for (let i = 0; i < depois.itens.length; i++) {
            assert.deepEqual(depois.itens[i], esperado[i])  
        }
    })

    it('autoInsert', async () => {
        using sql = getSqlite3()

        const tabela = 'TB_AUTOINSERT'

        const antes = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(antes.errorID, undefined)
        assert.strictEqual(antes.errors, undefined)

        const insert = await sql.autoInsert(tabela, modelSql, [{}], [ 'PK_ID' ])

        assert.strictEqual(insert.errorID, undefined)
        assert.strictEqual(insert.length, 1)

        {
            const inserted = insert[0]
            
            for (const key in inserted) {
                assert.notStrictEqual(inserted[key], null)
            }
        }

        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)

        // Add TB_JOINTEST2 in insert itens
        const i = insert.map(r => {
            return { 
                ...r,
                TB_JOINTEST2: []
            }
        })

        const esperado = [...antes.itens, ...i]

        for (let i = 0; i < depois.itens.length; i++) {
            assert.deepEqual(depois.itens[i], esperado[i])  
        }
    })

    it('Update', async () => {
        using sql = getSqlite3()

        const tabela = 'TB_UPDATE'

        const antes = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(antes.errorID, undefined)
        assert.strictEqual(antes.errors, undefined)

        const update = await sql.update(tabela, modelSql).build(
            s => s.where('PK_ID', 1), {
                VL_PRECO: 3,
                DS_OBS: 'C'
            }
        )

        assert.strictEqual(update.errorID, undefined)
        assert.strictEqual(update, true)

        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)

        const alterado = depois.itens.filter(d => d.PK_ID === 1)

        assert.strictEqual(alterado.length, 1)

        assert.strictEqual(alterado[0].DS_OBS, 'C')
        assert.strictEqual(alterado[0].VL_PRECO, 3)
    })

    it('Transaction Okay', async () => {
        using sql = getSqlite3()

        const tabela  = 'TB_TRANSACTION_OKAY'

        const antes = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(antes.errorID, undefined)
        assert.strictEqual(antes.errors, undefined)

        const error = await sql.transaction(async trx => {
            const insert = await trx.insert(tabela, modelSql, [{
                DS_OBS: 'B',
                VL_PRECO: 2
            }])

            assert.strictEqual(insert.errorID, undefined)

            return true
        })

        assert.strictEqual(error, true)

        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)

        assert.strictEqual(depois.itens.length, antes.itens.length + 1)
    })

    it('Transaction Multiquery Okay', async () => {
        using sql = getSqlite3()

        const tabela  = 'TB_TRANSACTION_MULTIPLES_QUERY_OK'

        const error = await sql.transaction(async trx => {
            await Promise.all([
                Plata.FastPromise(() => trx.update(tabela, modelSql).build(
                    s => s.where('PK_ID', '=', 1),
                    {
                        DS_OBS: 'F',
                        VL_PRECO: 99
                    }
                )).then(() => assert.strictEqual(trx.conn.isTransaction, true, 'Transação fechada antes da hora')),
                Plata.FastPromise(() => trx.update(tabela, modelSql).build(
                    s => s.where('PK_ID', '=', 2),
                    {
                        DS_OBS: 'F',
                        VL_PRECO: 99
                    }
                )).then(() => assert.strictEqual(trx.conn.isTransaction, true, 'Transação fechada antes da hora')),
                Plata.FastPromise(() => trx.update(tabela, modelSql).build(
                    s => s.where('PK_ID', '=', 3),
                    {
                        DS_OBS: 'F',
                        VL_PRECO: 99
                    }
                )).then(() => assert.strictEqual(trx.conn.isTransaction, true, 'Transação fechada antes da hora')),
                Plata.FastPromise(() => trx.update(tabela, modelSql).build(
                    s => s.where('PK_ID', '=', 4),
                    {
                        DS_OBS: 'F',
                        VL_PRECO: 99
                    }
                )).then(() => assert.strictEqual(trx.conn.isTransaction, true, 'Transação fechada antes da hora')),
                Plata.FastPromise(() => trx.update(tabela, modelSql).build(
                    s => s.where('PK_ID', '=', 5),
                    {
                        DS_OBS: 'F',
                        VL_PRECO: 99
                    }
                )).then(() => assert.strictEqual(trx.conn.isTransaction, true, 'Transação fechada antes da hora')),
            ])

            return true
        })

        assert.strictEqual(sql.conn.isTransaction ?? false, false, 'Transação ainda aberta')
        assert.strictEqual(error, true)
        
        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)
        assert.strictEqual(depois.itens.length, 5)

        for (let i = 0; i < depois.itens.length; i++) {
            assert.deepEqual(depois.itens[i], { 
                PK_ID: i+1,
                DS_OBS: 'F',
                VL_PRECO: 99,
                TB_JOINTEST2: []
            })
        }
    })

    it('Transaction Not Okay', async () => {
        using sql = getSqlite3()

        const tabela  = 'TB_TRANSACTION_NOKAY'

        const antes = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(antes.errorID, undefined)
        assert.strictEqual(antes.errors, undefined)

        const error = await sql.transaction(async trx => {
            const insert = await trx.insert(tabela, modelSql, [{
                DS_OBS: 'B',
                VL_PRECO: 2
            }])

            assert.strictEqual(insert.errorID, undefined)
            
            return Plata.BuildPlataError({
                errorID: 'PTSQLTRX001',
                msg: 'Erro esperado'
            })
        })

        assert.strictEqual(error.errorID, 'PTSQLTRX001')

        const depois = await sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync()

        assert.strictEqual(depois.errorID, undefined)
        assert.strictEqual(depois.errors, undefined)

        assert.strictEqual(depois.itens.length, antes.itens.length)
    })

    it('Join Found', async () => {
        using sql = getSqlite3()

        const table1 = 'TB_JOINTEST1'
        const table2 = 'TB_JOINTEST2'

        const selectJoin = await sql.select(table1, modelSql).build(
            s => s
            .limit(1)
            .includeOne(table2, 'LEFT JOIN', j => j.on(`${table1}.PK_ID`, '=', `${table2}.FK_JOIN`))
        ).toListAsync()

        assert.strictEqual(selectJoin.errorID, undefined)
        assert.strictEqual(selectJoin.errors, undefined)

        assert.deepEqual(selectJoin.itens[0], {
            PK_ID: 1,
            VL_PRECO: 1,
            DS_OBS: 'A',
            TB_JOINTEST2: [{
                PK_ID: 1,
                FK_JOIN: 1
            }] 
        })
    })

    it('Join Not Found', async () => {
        using sql = getSqlite3()

        const table1 = 'TB_JOINTEST1'
        const table2 = 'TB_JOINTEST2'

        const selectJoin = await sql.select(table1, modelSql).build(
            s => s
            .limit(1)
            .includeOne(table2, 'LEFT JOIN', j => j.onVal(`${table2}.PK_ID`, '=', 2))
        ).toListAsync()

        assert.strictEqual(selectJoin.errorID, undefined)
        assert.notStrictEqual(selectJoin.errors, undefined)
    })

    it('MultiBanco', async () => {
        using sql = getSqlite3()
        using aux = sqlLite3Aux()

        const tabela = 'TB_MULTIBANCO'

        const sqlInsertPromise = Plata.FastPromise(() => sql.insert(tabela, modelSql, [
            {
                VL_PRECO: 1,
                DS_OBS: 'A'
            }
        ]))

        const auxInsertPromise = Plata.FastPromise(() => aux.insert(tabela, modelSql, [
            {
                VL_PRECO: 2,
                DS_OBS: 'B'
            }
        ]))

        const [ sqlResult, auxResult ] = await Promise.all([ sqlInsertPromise, auxInsertPromise ])

        assert.strictEqual(sqlResult.errorID, undefined)
        assert.strictEqual(auxResult.errorID, undefined)

        const sqlSelectPromise = Plata.FastPromise(() => sql.select(tabela, modelSql).build(
            s => s
        ).toListAsync())

        const auxSelectPromise = Plata.FastPromise(() => aux.select(tabela, modelSql).build(
            s => s
        ).toListAsync())

        const [ sqlSelect, auxSelect ] = await Promise.all([ sqlSelectPromise , auxSelectPromise ])

        assert.strictEqual(sqlSelect.errorID, undefined)
        assert.strictEqual(sqlSelect.errors, undefined)

        assert.strictEqual(auxSelect.errorID, undefined)
        assert.strictEqual(auxSelect.errors, undefined)

        assert.strictEqual(sqlSelect.itens.length, 1)
        assert.strictEqual(auxSelect.itens.length, 1)

        assert.strictEqual(sqlSelect.itens[0].VL_PRECO, 1)
        assert.strictEqual(sqlSelect.itens[0].DS_OBS, 'A')

        assert.strictEqual(auxSelect.itens[0].VL_PRECO, 2)
        assert.strictEqual(auxSelect.itens[0].DS_OBS, 'B')
    })

    it('Multiquery',async () => {
        using sql = getSqlite3()

        const tabela = 'TB_MULTIQUERY'

        let promises: Promise<any>[] = new Array(50)
        const rows: any[] = new Array(50)

        for (let i = 0; i < rows.length; i++) {
            const row = {
                VL_PRECO: i,
                DS_OBS: `${i}`,
            }
            
            promises[i] = (Plata.FastPromise(() => sql.insert(
                tabela,
                modelSql,
                [row]
            )))
            
            rows[i] = ([{
                PK_ID: i + 1,
                ...row,
            }])
        }

        const resultInsert = await Promise.all(promises)

        assert.deepEqual(resultInsert, rows)

        promises.length = 0

        for (let i = 0; i < rows.length; i++) {
            promises.push(Plata.FastPromise(() => 
                sql.select(tabela,modelSql).build(
                    s => s
                    .where('PK_ID', i + 1)
                ).toListAsync())
            )    
        }

        const resultSelect = await Promise.all(promises)

        assert.deepEqual(resultSelect, rows.flatMap(row => ({
            errors: undefined,
            itens: [{
                ...row[0],
                TB_JOINTEST2: [],
            }]
        })))
    })

    type TbRawSelectTest = {PK_ID: number, TAG: number, VAL: number};
    type TbResultSelectTest = Readonly<{ VAL_MAX: number, VAL_MIN: number | null, TAG: number }>[];
    it('RawMappedSelect', async () => {
        using sql = getSqlite3()

        const tabela = 'TB_RAWSELECT'

        const value = await sql.rawMappedSelect({
            table: tabela,
            query: (s, t: TbRawSelectTest) => s
                .where('TAG', '<>', 3)
                .groupBy('TAG')
            ,
            cols: {
                TAG: [ s => s.select('TAG'), 'number', 'required'],
                VAL_MAX: [s => s.max({ VAL_MAX: 'VAL' }), 'number', 'required'],
                VAL_MIN: [s => s.min({ VAL_MIN: 'VAL' }), 'number'],
            },
        })
        
        if (value.errorID !== undefined) {
            return assert.fail(JSON.stringify(value))
        }

        // Valida a tipagem
        {const assertType: TbResultSelectTest = value}

        assert.deepEqual(value, [
            {VAL_MAX: 6, VAL_MIN: 0, TAG: 1},
            {VAL_MAX: 5, VAL_MIN: 1, TAG: 2},
        ])
    })

    type TbResultSelectTestError = Readonly<{ TEST_NULL: string }>[];
    it('RawMappedSelectError', async () => {
        using sql = getSqlite3()

        const tabela = 'TB_RAWSELECT'

        const value = await sql.rawMappedSelect({
            table: tabela,
            query: (s, t: TbRawSelectTest) => s
                .limit(1)
            ,
            cols: {
                TEST_NULL: [s => s.select(sql.conn.raw('NULL AS TEST_NULL')), 'string', 'required'],
            },
        })

        // Valida a tipagem
        {const assertType: PlataResult<TbResultSelectTestError> = value}
        
        assert.strictEqual(value.errorID, 'PLSQLRWSEL002')
    })

    it('Select with SQL Comments', async () => {
        using sql = getSqlite3()

        let capturedSql = ''
        const listener = (q: any) => { capturedSql = q.sql }

        sql.conn.on('query', listener)

        const result = await sql.select('TB_INSERT', modelSql, {
            comments: {
                user: 'test',
                feature: 'select_test'
            }
        }).build(s => s.limit(1)).toListAsync()

        sql.conn.off('query', listener)

        assert.strictEqual(result.errorID, undefined)
        assert.ok(capturedSql.includes('USER: (TEST)'))
        assert.ok(capturedSql.includes('FEATURE: (SELECT_TEST)'))
    })

    it('Update with SQL Comments', async () => {
        using sql = getSqlite3()

        let capturedSql = ''
        const listener = (q: any) => { capturedSql = q.sql }

        sql.conn.on('query', listener)

        const result = await sql.update('TB_UPDATE', modelSql, {
            comments: {
                operation: 'update_price'
            }
        }).build(
            s => s.where('PK_ID', 1),
            { VL_PRECO: 777, DS_OBS: 'TEST' }
        )

        sql.conn.off('query', listener)

        assert.strictEqual(result, true)
        assert.ok(capturedSql.includes('OPERATION: (UPDATE_PRICE)'))
    })

    it('Raw execution preserves comments', async () => {
        using sql = getSqlite3()

        let capturedSql = ''
        const listener = (q: any) => { capturedSql = q.sql }

        sql.conn.on('query', listener)

        const raw = sql.conn.queryBuilder().from('TB_INSERT').select('*').limit(1).toSQL()

        const result = await sql.runRawWithComment(raw, {
            trace: 'raw_test'
        })

        sql.conn.off('query', listener)

        assert.ok(Array.isArray(result))
        assert.ok(capturedSql.includes('TRACE: (RAW_TEST)'))
    })

    it('Transaction automatically injects TRX comment', async () => {
        using sql = getSqlite3()

        let capturedSql = ''
        const listener = (q: any) => { 
            capturedSql = q.sql 
        }

        await sql.transaction(async trx => {
            trx.trxConn.on('query', listener)

            await trx
                .select('TB_INSERT', modelSql)
                .build(s => s.limit(1))
                .toListAsync()
            ;


            trx.trxConn.off('query', listener)

            return true
        })

        assert.ok(capturedSql.includes('TRX: ('))
    })

    it('sql.now tem que ser um healthcheck', async () => {
        using sql = getSqlite3()

        const result = await sql.now()

        assert.strictEqual(result instanceof Date, true)
    })
})