const sqlite3 = require('sqlite3').verbose();
const { Client, MessageEmbed, Embed, EmbedBuilder } = require('discord.js');

let db = new sqlite3.Database('./stocks.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the stocks database.');
});

// Ensure the initial_price column exists in the stocks table
db.run("ALTER TABLE stocks ADD COLUMN initial_price REAL DEFAULT 100", (err) => {
    if (err && !err.message.includes("duplicate column name")) {
        console.error(err.message);
    }
});

db.run("ALTER TABLE stocks ADD COLUMN last_price REAL DEFAULT 100", (err) => {
    if (err && !err.message.includes("duplicate column name")) {
        console.error(err.message);
    }
});

// Create tables if they don't exist
db.serialize(() => {
    db.run("CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, balance REAL, total_value REAL DEFAULT 1000)");
    db.run("CREATE TABLE IF NOT EXISTS stocks (symbol TEXT PRIMARY KEY, name TEXT, price REAL, initial_price REAL)");
    db.run("CREATE TABLE IF NOT EXISTS portfolios (user_id TEXT, stock_symbol TEXT, shares INTEGER, PRIMARY KEY(user_id, stock_symbol))");
});

// Load started users from the database
let startedUsers = new Set();
db.all("SELECT id FROM users", [], (err, rows) => {
    if (err) {
        console.error(err.message);
    } else {
        rows.forEach((row) => {
            startedUsers.add(row.id);
        });
    }
});

let staff = [
    "1151266968163336282",
    "390836821723971594"
];

let createdStocks = []; // Array to store created stocks

function stocks(message, prefix) {
    if (!message.content.startsWith(prefix) || message.author.bot) return;

    const args = message.content.slice(prefix.length).trim().split(/ +/);
    const command = args.shift().toLowerCase();

    if (command === 'start') {
        startCommand(message);
    } else {
        if (!startedUsers.has(message.author.id)) {
            message.reply({ embeds: [createEmbed('Account Not Created', 'Please start your account using `kbs start` command.')] });
            return;
        }

        switch (command) {
            case 'create':
                if (args.length < 2) {
                    return message.reply({ embeds: [createEmbed('Error', 'Please provide a stock name and symbol. Example: `kbs create Azami AZA`')] });
                }
                createCommand(message, args);
                break;
            case 'increase':
                if (args.length < 2) {
                    return message.reply({ embeds: [createEmbed('Error', 'Please provide a stock symbol and the percentage to increase. Example: `kbs increase AZA 2.5`')] });
                }
                increaseCommand(message, args);
                break; 
            case 'decrease':
                if (args.length < 2) {
                    return message.reply({ embeds: [createEmbed('Error', 'Please provide a stock symbol and the percentage to decrease. Example: `kbs decrease SHB 2.5`')] });
                }
                decreaseCommand(message, args);
                break;
            case 'buy':
                if (args.length < 2) {
                    return message.reply({ embeds: [createEmbed('Error', 'Please provide a stock symbol and the number of shares you want to buy. Example: `kbs buy <stock symbol> 20`')] });
                }
                investCommand(message, args);
                break;
            case 'investments':
                investmentsCommand(message);
                break;
            case 'stocks':
                stocksCommand(message);
                break;
            case 'dbclear':
                dbClearCommand(message);
                break;
            case 'help':
                if (args.length > 0 && args[0] === 'staff') {
                    sendStaffHelp(message);
                } else {
                    sendUserHelp(message);
                }
                break;
            default:
                break;
        }
    }
}

function createEmbed(title, description) {
    if (!description || description.trim() === "") {
        description = "Kagurabachi Stock Market";
    }
    return new EmbedBuilder()
        .setTitle(title)
        .setDescription(description)
        .setColor(0x00AE86);
}

const commandDescriptionsNormalUsers = {
    'start': 'Start your account with a starting balance of 1000 money.',
    'buy <symbol> <shares>': 'Buy a specified number of shares of a stock.',
    'investments': 'View all your investments in stocks.',
    'stocks': 'View all available stocks and their current prices.',
};

function sendUserHelp(message) {
    const embed = new EmbedBuilder()
        .setColor(0x00AE86)
        .setTitle('Kagurabachi Stocks Help')
        .setDescription('List of available commands for normal users:')
        .addFields(
            Object.entries(commandDescriptionsNormalUsers).map(([command, description]) => {
                return { name: command, value: description };
            })
        );
    
    message.channel.send({ embeds: [embed] });
}


const commandDescriptionsStaff = {
    'create <name> <symbol>': 'Create a new stock with the specified name and symbol.',
    'increase <symbol> <percentage>': 'Increase the price of a stock by a specified percentage.',
    'decrease <symbol> <percentage>': 'Decrease the price of a stock by a specified percentage.',
    'dbclear': 'Clear all data from the database. (only available to zeto)'
};

function sendStaffHelp(message) {
    // Check if the user has any of the staff roles
    const member = message.guild.members.cache.get(message.author.id);

    const embed = new EmbedBuilder()
        .setColor(0x00AE86)
        .setTitle('Kagurabachi Stocks Help (Staff)')
        .setDescription('List of available commands for staff:')
        .addFields(
            Object.entries(commandDescriptionsStaff).map(([command, description]) => {
                return { name: command, value: description };
            })
        );
    
    message.channel.send({ embeds: [embed] });
}

function startCommand(message) {
    if (startedUsers.has(message.author.id)) {
        return message.reply({ embeds: [createEmbed('Account Exists', 'You have already started your account.')] });
    }

    db.run(`INSERT OR IGNORE INTO users (id, balance) VALUES (?, ?)`, [message.author.id, 1000], function(err) {
        if (err) {
            return console.error(err.message);
        }
        startedUsers.add(message.author.id);
        message.reply({ embeds: [createEmbed('Welcome to Kagurabachi Stocks', 'You have been given a starting balance of 1000 money.')] });
    });
}

function createCommand(message, args) {
    const name = args[0];
    const symbol = args[1].toUpperCase();
    db.run(`INSERT INTO stocks (symbol, name, price, initial_price) VALUES (?, ?, 100, 100)`, [symbol, name], function(err) {
        if (err) {
            return console.error(err.message);
        }
        createdStocks.push({ symbol, name }); // Add the created stock to the array
        message.reply({ embeds: [createEmbed('Stock Created', `Stock ${name} (${symbol}) created with a starting price of 100.`)] });
    });
}

function increaseCommand(message, args) {
    const symbol = args[0].toUpperCase();
    const percentage = parseFloat(args[1]);
    
    if (isNaN(percentage)) {
        return message.reply({ embeds: [createEmbed('Error', 'Please provide a valid percentage.')] });
    }

    db.get(`SELECT price FROM stocks WHERE symbol = ?`, [symbol], (err, row) => {
        if (err) {
            return console.error(err.message);
        }
        if (!row) {
            return message.reply({ embeds: [createEmbed('Unknown Stock', 'Stock not found.')] });
        }
        const newPrice = row.price * (1 + (percentage / 100));
        db.run(`UPDATE stocks SET last_price = price, price = ? WHERE symbol = ?`, [newPrice, symbol], function(err) {
            if (err) {
                return console.error(err.message);
            }
            message.reply({ embeds: [createEmbed('Stock Price Increased', `Stock ${symbol} increased by ${percentage}%. New price is ${newPrice.toFixed(2)}.`)] });
        });
    });
}

function investCommand(message, args) {
    const symbol = args[0].toUpperCase();
    const shares = parseInt(args[1]);
    
    if (isNaN(shares) || shares <= 0) {
        return message.reply({ embeds: [createEmbed('Invalid Shares', 'Please provide a valid number of shares.')] });
    }

    db.get(`SELECT price FROM stocks WHERE symbol = ?`, [symbol], (err, row) => {
        if (err) {
            return console.error(err.message);
        }
        if (!row) {
            return message.reply({ embeds: [createEmbed('Unknown Stock', 'Stock not found.')] });
        }
        const totalCost = row.price * shares;
        db.get(`SELECT balance FROM users WHERE id = ?`, [message.author.id], (err, userRow) => {
            if (err) {
                return console.error(err.message);
            }
            if (!userRow) {
                return message.reply({ embeds: [createEmbed('Error', 'User not found.')] });
            }
            if (userRow.balance < totalCost) {
                const neededMoney = totalCost - userRow.balance;
                const embed = createEmbed('Insufficient Balance', `You need ${neededMoney.toFixed(2)} more money to buy ${shares} shares of ${symbol}.`)
                    .addFields({ name: 'Your Balance', value: `Balance: ${userRow.balance.toFixed(2)}` });
                return message.reply({ embeds: [embed] });
            }
            db.run(`UPDATE users SET balance = balance - ? WHERE id = ?`, [totalCost, message.author.id], function(err) {
                if (err) {
                    return console.error(err.message);
                }
                db.run(`INSERT INTO portfolios (user_id, stock_symbol, shares) VALUES (?, ?, ?) ON CONFLICT(user_id, stock_symbol) DO UPDATE SET shares = shares + ?`, [message.author.id, symbol, shares, shares], function(err) {
                    if (err) {
                        return console.error(err.message);
                    }
                    message.reply({ embeds: [createEmbed('Invested', `You bought ${shares} shares of ${symbol} for ${totalCost.toFixed(2)} money.`)] });
                });
            });
        });
    });
}

function decreaseCommand(message, args) {
    const symbol = args[0].toUpperCase();
    const percentage = parseFloat(args[1]);
    
    if (isNaN(percentage)) {
        return message.reply({ embeds: [createEmbed('Error', 'Please provide a valid percentage.')] });
    }

    db.get(`SELECT price FROM stocks WHERE symbol = ?`, [symbol], (err, row) => {
        if (err) {
            return console.error(err.message);
        }
        if (!row) {
            return message.reply({ embeds: [createEmbed('Unknown Stock', 'Stock not found.')] });
        }
        const newPrice = row.price * (1 - (percentage / 100));
        db.run(`UPDATE stocks SET last_price = price, price = ? WHERE symbol = ?`, [newPrice, symbol], function(err) {
            if (err) {
                return console.error(err.message);
            }
            message.reply({ embeds: [createEmbed('Stock Price Decreased', `Stock ${symbol} decreased by ${percentage}%. New price is ${newPrice.toFixed(2)}.`)] });
        });
    });
}

function investmentsCommand(message) {
    db.get(`SELECT balance FROM users WHERE id = ?`, [message.author.id], (err, userRow) => {
        if (err) {
            return console.error(err.message);
        }
        
        if (!userRow) {
            return message.reply({ embeds: [createEmbed('Investments', 'User not found or has no balance yet.')] });
        }

        db.all(`SELECT p.stock_symbol, p.shares, s.price FROM portfolios p JOIN stocks s ON p.stock_symbol = s.symbol WHERE p.user_id = ?`, [message.author.id], (err, rows) => {
            if (err) {
                return console.error(err.message);
            }
            if (rows.length === 0) {
                const embed = createEmbed(`${message.author.username}'s Investments`, 'Your investment portfolio is empty.')
                    .addFields({ name: 'Bank Balance', value: `Balance: ${userRow.balance.toFixed(2)}` });
                return message.channel.send({ embeds: [embed] });
            }

            let totalValue = userRow.balance;
            const embed = createEmbed(`${message.author.username}'s Investments`, '');

            rows.forEach(row => {
                const value = row.price * row.shares;
                totalValue += value;
                embed.addFields({ name: row.stock_symbol, value: `Shares: ${row.shares}\nValue: ${value.toFixed(2)}` });
            });

            embed.addFields({ name: 'Total Portfolio Value', value: totalValue.toFixed(2) });
            embed.addFields({ name: 'Bank Balance', value: `Balance: ${userRow.balance.toFixed(2)}` });

            message.channel.send({ embeds: [embed] });
        });
    });
}

function stocksCommand(message) {
    db.all(`SELECT symbol, name, price, last_price FROM stocks`, [], (err, rows) => {
        if (err) {
            return console.error(err.message);
        }
        if (rows.length === 0) {
            return message.reply({ embeds: [createEmbed('Stocks', 'There are no stocks available.')] });
        }

        const embed = createEmbed('Kagurabachi Stocks', '');

        rows.forEach(row => {
            const change = row.price - row.last_price;
            const changePercent = (change / row.last_price) * 100;
            const changeText = change > 0 ? `🔺 ${change.toFixed(2)} (${changePercent.toFixed(2)}%)` : `🔻 ${change.toFixed(2)} (${changePercent.toFixed(2)}%)`;
            embed.addFields({ name: `${row.name} (${row.symbol})`, value: `Price: ${row.price.toFixed(2)}\nChange: ${changeText}` });
        });

        message.channel.send({ embeds: [embed] });
    });
}

function dbClearCommand(message) {
    // Check if the user is the specific user with ID 390836821723971594
    if (!staff.includes(message.author.id)) {
        return message.reply({ embeds: [createEmbed('Error', 'You do not have permission to use this command.')] });
    }
    
    // Proceed with database clearing
    db.serialize(() => {
        db.run(`DELETE FROM users`, function(err) {
            if (err) {
                return console.error(err.message);
            }
            startedUsers.clear(); // Clear the started users set after clearing the table
        });
        db.run(`DELETE FROM stocks`, function(err) {
            if (err) {
                return console.error(err.message);
            }
            createdStocks = []; // Clear the created stocks array after clearing the table
        });
        db.run(`DELETE FROM portfolios`, function(err) {
            if (err) {
                return console.error(err.message);
            }
        });
        message.reply({ embeds: [createEmbed('Database Clear', 'The database has been cleared.')] });
    });
}

module.exports = {
    stocks
};