import { Mysql } from '@fermyon/spin-sdk';

const getEnv = (key, def) => {
    try {
        return (typeof process !== 'undefined' && process.env && process.env[key]) || def;
    } catch {
        return def;
    }
};

const DB_URL = `mysql://${getEnv('DB_USER', 'root')}:${getEnv('DB_PASSWORD', 'scrumpass')}@${getEnv('DB_HOST', 'localhost')}:${getEnv('DB_PORT', '3306')}/${getEnv('DB_NAME', 'scrum_manager')}`;

function rowToObject(row, columns) {
    const obj = {};
    columns.forEach((col, index) => {
        obj[col.name] = row[index];
    });
    return obj;
}

class SpinConnection {
    constructor(conn) {
        this.conn = conn;
    }

    async query(sql, params = []) {
        console.log('SpinDB Query:', sql, params);
        try {
            const result = this.conn.query(sql, params);

            const rows = result.rows.map(r => rowToObject(r, result.columns));
            const fields = result.columns.map(c => ({ name: c.name }));

            if (sql.trim().toUpperCase().startsWith('INSERT') || sql.trim().toUpperCase().startsWith('UPDATE') || sql.trim().toUpperCase().startsWith('DELETE')) {
                return [{ affectedRows: result.rowsAffected || 0, insertId: result.lastInsertId || 0 }, fields];
            }

            return [rows, fields];
        } catch (e) {
            console.error('SpinDB Error:', e);
            throw e;
        }
    }

    async beginTransaction() {
        try {
            this.conn.query('START TRANSACTION', []);
        } catch (e) {
            console.warn('Transaction start failed:', e.message);
        }
    }

    async commit() {
        try { this.conn.query('COMMIT', []); } catch (e) { }
    }

    async rollback() {
        try { this.conn.query('ROLLBACK', []); } catch (e) { }
    }

    release() { }
}

export const initDB = async () => {
    console.log('Spin DB adapter ready.');
};

// Lazy initialization to avoid Wizer issues
let poolInstance = null;
function getPool() {
    if (!poolInstance) {
        poolInstance = {
            async getConnection() {
                const conn = Mysql.open(DB_URL);
                return new SpinConnection(conn);
            },
            async query(sql, params) {
                const conn = await this.getConnection();
                const result = await conn.query(sql, params);
                return result;
            },
            escape: (val) => `'${val}'`,
            end: () => { }
        };
    }
    return poolInstance;
}

export default {
    query: (sql, params) => getPool().query(sql, params),
    getConnection: () => getPool().getConnection(),
    end: () => getPool().end(),
    initDB
};