const express = require('express');
const cors = require('cors');
const sqlite3 = require('sqlite3').verbose();
const bodyParser = require('body-parser');
const path = require('path');

const app = express();
const PORT = process.env.PORT || 3000;

// Enable CORS and JSON parsing
app.use(cors());
app.use(bodyParser.json({ limit: '50mb' })); // Increased limit for potential image data

// Serve static files (HTML, JS, CSS) from the current directory
app.use(express.static(__dirname));

// Initialize SQLite database
const db = new sqlite3.Database('./database.sqlite', (err) => {
    if (err) {
        console.error('Error opening database', err.message);
    } else {
        console.log('Connected to the SQLite database.');
        initializeTables();
    }
});

// The list of stores (tables) we need, corresponding to the original IndexedDB stores
const STORES = [
    'users',
    'warehouses',
    'items',
    'projects',
    'transactions',
    'notifications',
    'contacts',
    'units',
    'accounts',
    'journal_entries',
    'activity_logs',
    'subcontracts',
    'subcontract_abstracts',
    'client_abstracts'
];

function initializeTables() {
    STORES.forEach(store => {
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS ${store} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                data TEXT NOT NULL
            )
        `;
        db.run(createTableQuery, (err) => {
            if (err) console.error(`Error creating table ${store}:`, err.message);
            else if (store === 'users' || store === 'warehouses') {
                seedDefaultData(store);
            }
        });
    });
}

function seedDefaultData(store) {
    db.get(`SELECT COUNT(*) as count FROM ${store}`, [], (err, row) => {
        if (!err && row && row.count === 0) {
            if (store === 'users') {
                const adminUser = { username: 'admin', password: '123', role: 'مدير عام', warehouse_id: null };
                db.run(`INSERT INTO users (data) VALUES (?)`, [JSON.stringify(adminUser)]);
                console.log('Seeded default admin user');
            } else if (store === 'warehouses') {
                const defaultWh = { name: 'المخزن الرئيسي', location: 'المقر الرئيسي' };
                db.run(`INSERT INTO warehouses (data) VALUES (?)`, [JSON.stringify(defaultWh)]);
                console.log('Seeded default warehouse');
            }
        }
    });
}

// Generics CRUD APIs

// GET all records for a specific store
app.get('/api/:store', (req, res) => {
    const store = req.params.store;
    if (!STORES.includes(store)) return res.status(400).json({ error: 'Invalid store' });

    db.all(`SELECT id, data FROM ${store}`, [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        
        // Parse the JSON data back into objects
        const results = rows.map(row => {
            try {
                const parsed = JSON.parse(row.data);
                parsed.id = row.id; // Ensure the ID matches the database primary key
                return parsed;
            } catch (e) {
                return null;
            }
        }).filter(item => item !== null);

        res.json(results);
    });
});

// GET a specific record by ID
app.get('/api/:store/:id', (req, res) => {
    const store = req.params.store;
    const id = req.params.id;
    if (!STORES.includes(store)) return res.status(400).json({ error: 'Invalid store' });

    db.get(`SELECT data FROM ${store} WHERE id = ?`, [id], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: 'Not found' });
        
        const parsed = JSON.parse(row.data);
        parsed.id = id;
        res.json(parsed);
    });
});

// POST (Add) a new record
app.post('/api/:store', (req, res) => {
    const store = req.params.store;
    if (!STORES.includes(store)) return res.status(400).json({ error: 'Invalid store' });

    const data = req.body;
    // We don't store the id inside the JSON if it's auto-incrementing in DB,
    // but the frontend might pass an id. If we use DB's id, we ignore the frontend's.
    
    // SQLite uses AUTOINCREMENT for id, so we just insert the data.
    const jsonStr = JSON.stringify(data);
    
    db.run(`INSERT INTO ${store} (data) VALUES (?)`, [jsonStr], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        
        const newId = this.lastID;
        // Also update the JSON to contain the correct ID and save it back, 
        // to ensure data consistency if the object itself stores its ID.
        data.id = newId;
        const updatedJsonStr = JSON.stringify(data);
        db.run(`UPDATE ${store} SET data = ? WHERE id = ?`, [updatedJsonStr, newId]);

        res.status(201).json({ id: newId, message: 'Created successfully' });
    });
});

// PUT (Update) an existing record
app.put('/api/:store/:id', (req, res) => {
    const store = req.params.store;
    const id = parseInt(req.params.id, 10);
    if (!STORES.includes(store)) return res.status(400).json({ error: 'Invalid store' });

    const data = req.body;
    data.id = id; // Enforce the ID matches the route
    const jsonStr = JSON.stringify(data);

    db.run(`UPDATE ${store} SET data = ? WHERE id = ?`, [jsonStr, id], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        if (this.changes === 0) return res.status(404).json({ error: 'Not found' });
        
        res.json({ success: true, message: 'Updated successfully' });
    });
});

// DELETE a record
app.delete('/api/:store/:id', (req, res) => {
    const store = req.params.store;
    const id = req.params.id;
    if (!STORES.includes(store)) return res.status(400).json({ error: 'Invalid store' });

    db.run(`DELETE FROM ${store} WHERE id = ?`, [id], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        if (this.changes === 0) return res.status(404).json({ error: 'Not found' });
        
        res.json({ success: true, message: 'Deleted successfully' });
    });
});
// Backup system
const fs = require('fs');
app.post('/api/backup', (req, res) => {
    try {
        const backupDir = path.join(__dirname, 'backups');
        if (!fs.existsSync(backupDir)) {
            fs.mkdirSync(backupDir);
        }

        const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
        const currentBackupPath = path.join(backupDir, `version_${timestamp}`);
        fs.mkdirSync(currentBackupPath);

        // Files to backup
        const filesToBackup = ['database.sqlite', 'index.html', 'app_final.js', 'server.js', 'ai_module.js', 'style.css'];
        
        let successCount = 0;
        filesToBackup.forEach(file => {
            const src = path.join(__dirname, file);
            const dest = path.join(currentBackupPath, file);
            if (fs.existsSync(src)) {
                fs.copyFileSync(src, dest);
                successCount++;
            }
        });

        res.json({ success: true, message: `تم أخذ نسخة احتياطية بنجاح (${successCount} ملفات) في مجلد backups`, path: `backups/version_${timestamp}` });
    } catch (err) {
        res.status(500).json({ error: 'فشل النسخ الاحتياطي: ' + err.message });
    }
});

// Start the server
app.listen(PORT, '0.0.0.0', () => {
    console.log(`Server is running on port ${PORT}`);
    console.log(`Access it on your local network using your machine's IP address (e.g., http://192.168.x.x:${PORT})`);
});
