/**
 * ConversionDB – SQLite wrapper for conversion history
 * Used in the Electron main process only.
 * Refactored to use async 'sqlite3' to avoid native compilation issues.
 */
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class ConversionDB {
    constructor(dbPath) {
        this.dbPath = dbPath;
    }

    async init() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbPath, (err) => {
                if (err) return reject(err);

                this.db.run('PRAGMA journal_mode = WAL');

                this.db.serialize(() => {
                    this.db.run(`
                        CREATE TABLE IF NOT EXISTS konvertierungen (
                            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
                            zeitstempel         DATETIME DEFAULT (datetime('now', 'localtime')),
                            dateiname           TEXT NOT NULL,
                            ausgangsdateiname   TEXT,
                            quellformat         TEXT,
                            zielformat          TEXT,
                            konvertierungsmodus TEXT,
                            kunden_id           TEXT,
                            eingang_daten       TEXT,
                            ausgang_daten       TEXT,
                            status              TEXT DEFAULT 'ERFOLGREICH',
                            fehlermeldung       TEXT,
                            quelle              TEXT DEFAULT 'WATCHER'
                        );
                    `);

                    // Table for storing the latest configuration
                    this.db.run(`
                        CREATE TABLE IF NOT EXISTS konfigurationen (
                            id            INTEGER PRIMARY KEY AUTOINCREMENT,
                            zeitstempel   DATETIME DEFAULT (datetime('now', 'localtime')),
                            config_daten  TEXT NOT NULL
                        );
                    `);

                    // Index for common queries
                    this.db.run(`CREATE INDEX IF NOT EXISTS idx_zeitstempel ON konvertierungen(zeitstempel DESC);`);
                    this.db.run(`CREATE INDEX IF NOT EXISTS idx_status ON konvertierungen(status);`, (err) => {
                        if (err) reject(err);
                        else resolve();
                    });
                });
            });
        });
    }

    /**
     * Store the latest configuration (overwrites any existing).
     * @param {string} configData Serialized JSON string
     */
    async insertConfig(configData) {
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                // Keep only one configuration record by dropping the old one
                this.db.run('DELETE FROM konfigurationen');

                const sql = `INSERT INTO konfigurationen (config_daten) VALUES (?)`;
                this.db.run(sql, [configData || ''], function (err) {
                    if (err) reject(err);
                    else resolve({ id: this.lastID });
                });
            });
        });
    }

    /**
     * Retrieve the latest stored configuration.
     * @returns {Promise<string|null>}
     */
    async getConfig() {
        return new Promise((resolve, reject) => {
            this.db.get('SELECT config_daten FROM konfigurationen ORDER BY id DESC LIMIT 1', [], (err, row) => {
                if (err) reject(err);
                else resolve(row ? row.config_daten : null);
            });
        });
    }

    /**
     * Insert a new conversion record.
     * @param {Object} record
     * @returns {Promise<Object>} { id }
     */
    async insert(record) {
        return new Promise((resolve, reject) => {
            const sql = `
                INSERT INTO konvertierungen 
                    (dateiname, ausgangsdateiname, quellformat, zielformat, 
                     konvertierungsmodus, kunden_id, eingang_daten, ausgang_daten, 
                     status, fehlermeldung, quelle)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;

            const params = [
                record.dateiname || '',
                record.ausgangsdateiname || null,
                record.quellformat || null,
                record.zielformat || null,
                record.konvertierungsmodus || null,
                record.kunden_id || null,
                record.eingang_daten || null,
                record.ausgang_daten || null,
                record.status || 'ERFOLGREICH',
                record.fehlermeldung || null,
                record.quelle || 'WATCHER'
            ];

            this.db.run(sql, params, function (err) {
                if (err) reject(err);
                else resolve({ id: this.lastID });
            });
        });
    }

    /**
     * Get paginated list of conversions (without full data blobs).
     */
    async getAll(limit = 50, offset = 0, statusFilter = 'ALL', search = '', dateFilter = 'ALL') {
        return new Promise((resolve, reject) => {
            let where = '';
            const conditions = [];
            const params = [];

            const cleanStatus = (statusFilter || 'ALL').toString().trim().toUpperCase();
            const cleanDate = (dateFilter || 'ALL').toString().trim().toUpperCase();

            if (cleanStatus !== 'ALL') {
                conditions.push('status = ?');
                params.push(cleanStatus);
            }
            if (cleanDate === 'TODAY') {
                conditions.push("date(zeitstempel) = date('now', 'localtime')");
            }
            if (search) {
                conditions.push('(dateiname LIKE ? OR ausgangsdateiname LIKE ? OR kunden_id LIKE ?)');
                params.push(`%${search}%`, `%${search}%`, `%${search}%`);
            }
            if (conditions.length > 0) {
                where = 'WHERE ' + conditions.join(' AND ');
            }

            const countSql = `SELECT COUNT(*) as total FROM konvertierungen ${where}`;

            this.db.get(countSql, params, (err, countRow) => {
                if (err) return reject(err);

                const dataSql = `
                    SELECT id, zeitstempel, dateiname, ausgangsdateiname, 
                           quellformat, zielformat, konvertierungsmodus, 
                           kunden_id, status, fehlermeldung, quelle
                    FROM konvertierungen 
                    ${where}
                    ORDER BY zeitstempel DESC
                    LIMIT ? OFFSET ?
                `;

                this.db.all(dataSql, [...params, limit, offset], (err, rows) => {
                    if (err) reject(err);
                    else resolve({ rows, total: countRow.total });
                });
            });
        });
    }

    /**
     * Get a single record by ID (including full data).
     */
    async getById(id) {
        return new Promise((resolve, reject) => {
            this.db.get('SELECT * FROM konvertierungen WHERE id = ?', [id], (err, row) => {
                if (err) reject(err);
                else resolve(row || null);
            });
        });
    }

    /**
     * Delete a record by ID.
     */
    async deleteById(id) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM konvertierungen WHERE id = ?', [id], function (err) {
                if (err) reject(err);
                else resolve(this.changes > 0);
            });
        });
    }

    /**
     * Get aggregated statistics.
     */
    async getStats() {
        return new Promise((resolve, reject) => {
            let total = 0, success = 0, error = 0, today = 0;

            this.db.serialize(() => {
                this.db.get('SELECT COUNT(*) as c FROM konvertierungen', (err, row) => {
                    if (!err && row) total = row.c;
                });
                this.db.get("SELECT COUNT(*) as c FROM konvertierungen WHERE status = 'ERFOLGREICH'", (err, row) => {
                    if (!err && row) success = row.c;
                });
                this.db.get("SELECT COUNT(*) as c FROM konvertierungen WHERE status = 'FEHLERHAFT'", (err, row) => {
                    if (!err && row) error = row.c;
                });
                this.db.get("SELECT COUNT(*) as c FROM konvertierungen WHERE date(zeitstempel) = date('now', 'localtime')", (err, row) => {
                    if (err) return reject(err);
                    today = row.c;
                    resolve({ total, success, error, today });
                });
            });
        });
    }

    /**
     * Close the database connection.
     */
    close() {
        if (this.db) {
            this.db.close();
        }
    }
}

module.exports = ConversionDB;