import express from 'express';
import cors from 'cors';
import { getDb } from './db';
import { initTelegramAuth, getCurrentRates } from './telegram';
import { z } from 'zod';
import * as xlsx from 'xlsx';
import jwt from 'jsonwebtoken';
import crypto from 'crypto';
import dotenv from 'dotenv';
import { initScheduler } from './scheduler';

dotenv.config();

const JWT_SECRET = process.env.JWT_SECRET || 'your-secret-key-change-this-in-production';

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

app.use(cors());
app.use(express.json({ limit: '50mb' }));

// Auth Middleware
const authenticateToken = (req: any, res: any, next: any) => {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];

    if (!token) return res.status(401).json({ error: 'Authentication required' });

    jwt.verify(token, JWT_SECRET, (err: any, user: any) => {
        if (err) return res.status(403).json({ error: 'Invalid or expired token' });
        req.user = user;
        next();
    });
};

// Login Endpoint
app.post('/api/login', async (req, res) => {
    const { username, password, deviceName, rememberMe } = req.body;
    if (!username || !password) {
        return res.status(400).json({ error: 'Username and password are required' });
    }

    const db = await getDb();
    const user = await db.get('SELECT * FROM users WHERE username = ?', [username]);

    if (!user) {
        return res.status(401).json({ error: 'Invalid username or password' });
    }

    const hash = crypto.pbkdf2Sync(password, user.salt, 1000, 64, 'sha512').toString('hex');
    if (hash !== user.password_hash) {
        return res.status(401).json({ error: 'Invalid username or password' });
    }

    const token = jwt.sign({ id: user.id, username: user.username }, JWT_SECRET, { expiresIn: '24h' });
    
    let deviceId = null;
    if (rememberMe && deviceName) {
        deviceId = crypto.randomUUID();
        await db.run(
            'INSERT INTO trusted_devices (user_id, device_id, device_name, last_used) VALUES (?, ?, ?, ?)',
            [user.id, deviceId, deviceName, new Date().toISOString()]
        );
    }

    res.json({ token, username: user.username, deviceId });
});

// Device Login Endpoint
app.post('/api/login-device', async (req, res) => {
    const { username, deviceId } = req.body;
    if (!username || !deviceId) {
        return res.status(400).json({ error: 'Username and device ID are required' });
    }

    const db = await getDb();
    const user = await db.get('SELECT * FROM users WHERE username = ?', [username]);
    if (!user) {
        return res.status(401).json({ error: 'Invalid user' });
    }

    const device = await db.get(
        'SELECT * FROM trusted_devices WHERE user_id = ? AND device_id = ?',
        [user.id, deviceId]
    );

    if (!device) {
        return res.status(401).json({ error: 'Device not recognized or access revoked' });
    }

    // Update last used
    await db.run(
        'UPDATE trusted_devices SET last_used = ? WHERE id = ?',
        [new Date().toISOString(), device.id]
    );

    const token = jwt.sign({ id: user.id, username: user.username }, JWT_SECRET, { expiresIn: '24h' });
    res.json({ token, username: user.username });
});

// Device Management Endpoints
app.get('/api/devices', authenticateToken, async (req: any, res) => {
    const db = await getDb();
    const devices = await db.all(
        'SELECT id, device_name, last_used FROM trusted_devices WHERE user_id = ? ORDER BY last_used DESC',
        [req.user.id]
    );
    res.json(devices);
});

app.delete('/api/devices/:id', authenticateToken, async (req: any, res) => {
    const { id } = req.params;
    const db = await getDb();
    await db.run('DELETE FROM trusted_devices WHERE id = ? AND user_id = ?', [id, req.user.id]);
    res.json({ success: true });
});

app.get('/api/rates', authenticateToken, (req, res) => {
    res.json(getCurrentRates());
});

// Initialize Telegram Client in background
initTelegramAuth().catch(console.error);

// Initialize Backup Scheduler
initScheduler();

// Add Validation Schemas
const transactionSchema = z.object({
    type: z.enum(['income', 'expense', 'transfer']),
    amount: z.number().positive(),
    currency: z.string().min(2),
    wallet_id: z.number().int().positive(),
    to_wallet_id: z.number().int().positive().nullable().optional(),
    category: z.string().optional(),
    note: z.string().optional(),
    date: z.string(),
    country_id: z.string().optional()
});

const exchangeSchema = z.object({
    from_amount: z.number().positive(),
    from_currency: z.string().min(2),
    from_wallet_id: z.number().int().positive(),
    to_amount: z.number().positive(),
    to_currency: z.string().min(2),
    to_wallet_id: z.number().int().positive(),
    rate: z.number().positive(),
    note: z.string().optional(),
    date: z.string()
});

const loanSchema = z.object({
    person: z.string().min(1),
    type: z.enum(['borrowed_from_me', 'owed_by_me']),
    amount: z.number().positive(),
    currency: z.string().min(2),
    note: z.string().optional(),
    date: z.string()
});

async function getWalletBalance(db: any, walletId: number): Promise<number> {
    const query1 = `
        SELECT 
            COALESCE(SUM(CASE WHEN type = 'income' AND wallet_id = ? THEN amount ELSE 0 END), 0) -
            COALESCE(SUM(CASE WHEN type = 'expense' AND wallet_id = ? THEN amount ELSE 0 END), 0) -
            COALESCE(SUM(CASE WHEN type = 'transfer' AND wallet_id = ? THEN amount ELSE 0 END), 0) +
            COALESCE(SUM(CASE WHEN type = 'transfer' AND to_wallet_id = ? THEN amount ELSE 0 END), 0) as balance
        FROM transactions
        WHERE wallet_id = ? OR to_wallet_id = ?
    `;
    const result = await db.get(query1, [walletId, walletId, walletId, walletId, walletId, walletId]);

    const query2 = `
        SELECT 
            COALESCE(SUM(CASE WHEN to_wallet_id = ? THEN to_amount ELSE 0 END), 0) -
            COALESCE(SUM(CASE WHEN from_wallet_id = ? THEN from_amount ELSE 0 END), 0) as exchange_balance
        FROM exchanges
        WHERE from_wallet_id = ? OR to_wallet_id = ?
    `;
    const exchangeResult = await db.get(query2, [walletId, walletId, walletId, walletId]);

    return (result?.balance || 0) + (exchangeResult?.exchange_balance || 0);
}

app.get('/api/wallets', authenticateToken, async (req, res) => {
    const db = await getDb();
    const wallets = await db.all('SELECT * FROM wallets');
    res.json(wallets);
});

app.get('/api/transactions', authenticateToken, async (req, res) => {
    const db = await getDb();
    const transactions = await db.all(`
        SELECT id, type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id FROM transactions
        UNION ALL
        SELECT id + 1000000, 'expense' as type, from_amount as amount, from_currency as currency, from_wallet_id as wallet_id, to_wallet_id, 'Exchange' as category, note, date, NULL as country_id FROM exchanges
        UNION ALL
        SELECT id + 2000000, 'income' as type, to_amount as amount, to_currency as currency, to_wallet_id as wallet_id, from_wallet_id as to_wallet_id, 'Exchange' as category, note, date, NULL as country_id FROM exchanges
        ORDER BY date DESC
    `);
    res.json(transactions);
});

app.post('/api/transactions', authenticateToken, async (req, res) => {
    const validatedData = transactionSchema.parse(req.body);
    const { type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id } = validatedData;
    
    const db = await getDb();

    // Balance check for expenses and transfers
    if (type === 'expense' || type === 'transfer') {
        const currentBalance = await getWalletBalance(db, wallet_id);
        if (currentBalance < amount) {
            return res.status(400).json({ 
                error: 'Validation Error', 
                message: "Insufficient balance: your money is less than this receipt" 
            });
        }
    }

    const result = await db.run(
        `INSERT INTO transactions (type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id]
    );
    res.json({ id: result.lastID });
});

app.get('/api/exchanges', authenticateToken, async (req, res) => {
    const db = await getDb();
    const exchanges = await db.all('SELECT * FROM exchanges ORDER BY date DESC');
    res.json(exchanges);
});

app.post('/api/exchanges', authenticateToken, async (req, res) => {
    const validatedData = exchangeSchema.parse(req.body);
    const { from_amount, from_currency, from_wallet_id, to_amount, to_currency, to_wallet_id, rate, note, date } = validatedData;
    
    const db = await getDb();

    // Balance check for the source wallet
    const currentBalance = await getWalletBalance(db, from_wallet_id);
    if (currentBalance < from_amount) {
        return res.status(400).json({ 
            error: 'Validation Error', 
            message: "Insufficient balance: your money is less than this receipt" 
        });
    }

    const result = await db.run(
        `INSERT INTO exchanges (from_amount, from_currency, from_wallet_id, to_amount, to_currency, to_wallet_id, rate, note, date) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [from_amount, from_currency, from_wallet_id, to_amount, to_currency, to_wallet_id, rate, note, date]
    );
    res.json({ id: result.lastID });
});

app.get('/api/loans', authenticateToken, async (req, res) => {
    const db = await getDb();
    const loans = await db.all('SELECT * FROM loans ORDER BY date DESC');
    res.json(loans);
});

app.post('/api/loans', authenticateToken, async (req, res) => {
    const validatedData = loanSchema.parse(req.body);
    const { person, type, amount, currency, note, date } = validatedData;
    const db = await getDb();
    const result = await db.run(
        `INSERT INTO loans (person, type, amount, currency, note, date) 
    VALUES (?, ?, ?, ?, ?, ?)`,
        [person, type, amount, currency, note, date]
    );
    res.json({ id: result.lastID });
});

app.post('/api/loans/:id/pay', authenticateToken, async (req, res) => {
    const { id } = req.params;
    const paymentSchema = z.object({ amount: z.number().positive() });
    const validatedData = paymentSchema.parse(req.body);
    const db = await getDb();
    
    await db.run(
        `UPDATE loans SET paid = paid + ? WHERE id = ?`,
        [validatedData.amount, id]
    );
    res.json({ success: true });
});

app.put('/api/loans/:id', authenticateToken, async (req, res) => {
    const { id } = req.params;
    const validatedData = loanSchema.parse(req.body);
    const { person, type, amount, currency, note, date } = validatedData;
    const db = await getDb();
    
    await db.run(
        `UPDATE loans SET person = ?, type = ?, amount = ?, currency = ?, note = ?, date = ? WHERE id = ?`,
        [person, type, amount, currency, note, date, id]
    );
    res.json({ success: true });
});

app.delete('/api/loans/:id', authenticateToken, async (req, res) => {
    const { id } = req.params;
    const db = await getDb();
    await db.run('DELETE FROM loans WHERE id = ?', [id]);
    res.json({ success: true });
});

app.get('/api/export', authenticateToken, async (req, res) => {
    const db = await getDb();
    const transactions = await db.all('SELECT * FROM transactions');
    const wallets = await db.all('SELECT * FROM wallets');
    const exchanges = await db.all('SELECT * FROM exchanges');
    const loans = await db.all('SELECT * FROM loans');

    const wb = xlsx.utils.book_new();

    xlsx.utils.book_append_sheet(wb, xlsx.utils.json_to_sheet(transactions), 'Transactions');
    xlsx.utils.book_append_sheet(wb, xlsx.utils.json_to_sheet(wallets), 'Wallets');
    xlsx.utils.book_append_sheet(wb, xlsx.utils.json_to_sheet(exchanges), 'Exchanges');
    xlsx.utils.book_append_sheet(wb, xlsx.utils.json_to_sheet(loans), 'Loans');

    const buf = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });

    res.set('Content-Disposition', 'attachment; filename="backup.xlsx"');
    res.type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buf);
});

app.post('/api/import', authenticateToken, async (req, res) => {
    try {
        const { file, replace } = req.body;
        if (!file) {
            return res.status(400).json({ error: 'No file provided' });
        }

        const buffer = Buffer.from(file, 'base64');
        const workbook = xlsx.read(buffer, { type: 'buffer' });
        const db = await getDb();

        if (replace) {
            await db.exec('DELETE FROM transactions; DELETE FROM exchanges; DELETE FROM loans;');
        }

        // Import Transactions
        const transactionsSheet = workbook.Sheets['Transactions'];
        if (transactionsSheet) {
            const transactions: any[] = xlsx.utils.sheet_to_json(transactionsSheet);
            for (const t of transactions) {
                // Ensure field names match database schema
                await db.run(
                    `INSERT INTO transactions (type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                    [t.type, t.amount, t.currency, t.wallet_id, t.to_wallet_id, t.category, t.note, t.date, t.country_id]
                );
            }
        }

        // Import Exchanges
        const exchangesSheet = workbook.Sheets['Exchanges'];
        if (exchangesSheet) {
            const exchanges: any[] = xlsx.utils.sheet_to_json(exchangesSheet);
            for (const e of exchanges) {
                await db.run(
                    `INSERT INTO exchanges (from_amount, from_currency, from_wallet_id, to_amount, to_currency, to_wallet_id, rate, note, date) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                    [e.from_amount, e.from_currency, e.from_wallet_id, e.to_amount, e.to_currency, e.to_wallet_id, e.rate, e.note, e.date]
                );
            }
        }

        // Import Loans
        const loansSheet = workbook.Sheets['Loans'];
        if (loansSheet) {
            const loans: any[] = xlsx.utils.sheet_to_json(loansSheet);
            for (const l of loans) {
                await db.run(
                    `INSERT INTO loans (person, type, amount, currency, paid, date, note) 
                    VALUES (?, ?, ?, ?, ?, ?, ?)`,
                    [l.person, l.type, l.amount, l.currency, l.paid || 0, l.date, l.note]
                );
            }
        }

        res.json({ success: true, message: 'Data imported successfully' });
    } catch (error: any) {
        console.error('Import error:', error);
        res.status(500).json({ error: 'Failed to import data', details: error.message });
    }
});

app.delete('/api/data', authenticateToken, async (req, res) => {
    try {
        const db = await getDb();
        await db.exec('DELETE FROM transactions; DELETE FROM exchanges; DELETE FROM loans;');
        res.json({ success: true, message: 'All data cleared successfully' });
    } catch (error: any) {
        console.error('Clear data error:', error);
        res.status(500).json({ error: 'Failed to clear data', details: error.message });
    }
});

app.get('/api/analytics/dashboard', authenticateToken, async (req, res) => {
    const db = await getDb();
    
    // Using SQLite to aggregate the balances
    // Since currency conversion changes, we return the raw grouped data and calculate total dynamically
    
    const walletBalancesRaw = await db.all(`
        SELECT 
            w.id, w.name, w.type, w.currency,
            COALESCE(SUM(CASE WHEN t.type = 'income' THEN t.amount ELSE 0 END), 0) -
            COALESCE(SUM(CASE WHEN t.type = 'expense' THEN t.amount ELSE 0 END), 0) -
            COALESCE(SUM(CASE WHEN t.type = 'transfer' AND t.wallet_id = w.id THEN t.amount ELSE 0 END), 0) +
            COALESCE(SUM(CASE WHEN t.type = 'transfer' AND t.to_wallet_id = w.id THEN t.amount ELSE 0 END), 0) as balance
        FROM wallets w
        LEFT JOIN transactions t ON t.wallet_id = w.id OR t.to_wallet_id = w.id
        GROUP BY w.id
    `);

    // Fetch exchanges to merge into currency pools later on the frontend
    const exchangesRaw = await db.all('SELECT from_amount, from_currency, to_amount, to_currency FROM exchanges');

    // Fetch loans for net worth calculation
    const loansRaw = await db.all('SELECT type, amount, paid, currency FROM loans');
    
    // We also need transactions for the recent activity feed
    const recentActivity = await db.all(`
        SELECT id, type, amount, currency, wallet_id, to_wallet_id, category, note, date, country_id FROM transactions
        UNION ALL
        SELECT id + 1000000, 'expense' as type, from_amount as amount, from_currency as currency, from_wallet_id as wallet_id, to_wallet_id, 'Exchange' as category, note, date, NULL as country_id FROM exchanges
        UNION ALL
        SELECT id + 2000000, 'income' as type, to_amount as amount, to_currency as currency, to_wallet_id as wallet_id, from_wallet_id as to_wallet_id, 'Exchange' as category, note, date, NULL as country_id FROM exchanges
        ORDER BY date DESC LIMIT 5
    `);

    res.json({
        walletBalancesRaw,
        exchangesRaw,
        loansRaw,
        recentActivity
    });
});

app.listen(PORT, () => {
    console.log(`Backend running on http://localhost:${PORT}`);
});

// Global Error Handler
app.use((err: any, req: express.Request, res: express.Response, next: express.NextFunction) => {
    if (err instanceof z.ZodError) {
        res.status(400).json({ error: 'Validation Error', details: err.issues });
    } else {
        console.error('Unhandled Error:', err);
        res.status(500).json({ error: err.message || 'Internal Server Error' });
    }
});