const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

// Ensure instance directory exists
const instanceDir = path.join(__dirname, 'instance');
if (!fs.existsSync(instanceDir)) {
    fs.mkdirSync(instanceDir, { recursive: true });
}

const DATABASE_PATH = path.join(instanceDir, 'database.db');
const db = new Database(DATABASE_PATH);

// Enable foreign keys
db.pragma('foreign_keys = ON');

function initDb() {
    // Create OTPs table
    db.exec(`
        CREATE TABLE IF NOT EXISTS otps (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            identifier TEXT,
            otp TEXT,
            expires_at DATETIME
        )
    `);

    // Create users table
    db.exec(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE,
            client_name TEXT,
            phone TEXT,
            address TEXT,
            company TEXT,
            user_type TEXT DEFAULT 'client'
        )
    `);

    // Create products table
    db.exec(`
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT NOT NULL,
            category TEXT NOT NULL,
            product_options TEXT,
            product_rate TEXT,
            stock_status TEXT NOT NULL,
            image_filename TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `);

    // Create admins table
    db.exec(`
        CREATE TABLE IF NOT EXISTS admins (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `);

    // Create orders table
    db.exec(`
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT,
            expected_date TEXT,
            quantity TEXT,
            comments TEXT,
            user_email TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status TEXT DEFAULT 'inquiry received',
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            payment_status TEXT DEFAULT 'unpaid'
        )
    `);

    // Create messages table
    db.exec(`
        CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER,
            user_email TEXT,
            subject TEXT,
            body TEXT,
            attachment_name TEXT,
            order_name TEXT,
            order_quantity TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_read INTEGER DEFAULT 0,
            FOREIGN KEY (order_id) REFERENCES orders(id)
        )
    `);

    // Insert main admin if not exists
    const mainAdminEmail = process.env.ADMIN_EMAIL;
    if (mainAdminEmail) {
        const stmt = db.prepare('INSERT OR IGNORE INTO admins (email) VALUES (?)');
        stmt.run(mainAdminEmail.toLowerCase());
    }

    console.log('Database initialized successfully');
}

// Helper functions
function cleanOptions(optionsStr) {
    if (!optionsStr) return null;
    let options;
    try {
        options = typeof optionsStr === 'string' ? JSON.parse(optionsStr) : optionsStr;
    } catch (e) {
        // If it's a string that failed to parse but contains JSON markers, it's likely corrupted
        if (typeof optionsStr === 'string' && (optionsStr.includes('{') || optionsStr.includes(':'))) {
            return null;
        }
        return optionsStr;
    }

    if (!options || typeof options !== 'object') return optionsStr;

    const cleaned = {};
    for (const [key, value] of Object.entries(options)) {
        // SCORCHED EARTH: Remove any key that contains markers of being a JSON object or escaped JSON
        // Also skip keys that are unusually long (keys should be short names like 'size', 'finish')
        const isSuspicious = /[{}[\]:"]/.test(key) || key.length > 50;

        if (isSuspicious) {
            console.log(`ULTIMATE DATABASE CLEANING: Discarding parasitic key: ${key.substring(0, 30)}...`);
            continue;
        }
        cleaned[key] = value;
    }

    return JSON.stringify(cleaned);
}

// Users
function addClient(email, clientName, phone, address, company) {
    try {
        const stmt = db.prepare(`
            INSERT INTO users (email, client_name, phone, address, company, user_type) 
            VALUES (?, ?, ?, ?, ?, ?)
        `);
        stmt.run(email.toLowerCase(), clientName, phone, address, company, 'client');
        return true;
    } catch (err) {
        if (err.code === 'SQLITE_CONSTRAINT_UNIQUE') {
            return false; // Email already exists
        }
        console.error('Database error:', err);
        return false;
    }
}

function getAllClients() {
    const stmt = db.prepare(`
        SELECT id, email, client_name, phone, address, company 
        FROM users WHERE user_type = 'client'
    `);
    return stmt.all();
}

function getClientById(clientId) {
    const stmt = db.prepare(`
        SELECT id, email, client_name, phone, address, company 
        FROM users WHERE id = ? AND user_type = 'client'
    `);
    return stmt.get(clientId);
}

function updateClient(clientId, email, clientName, phone, address, company) {
    try {
        const stmt = db.prepare(`
            UPDATE users SET email = ?, client_name = ?, phone = ?, address = ?, company = ? 
            WHERE id = ? AND user_type = 'client'
        `);
        stmt.run(email.toLowerCase(), clientName, phone, address, company, clientId);
        return true;
    } catch (err) {
        if (err.code === 'SQLITE_CONSTRAINT_UNIQUE') {
            return false;
        }
        return false;
    }
}

function deleteClient(clientId) {
    const stmt = db.prepare("DELETE FROM users WHERE id = ? AND user_type = 'client'");
    stmt.run(clientId);
}

function isAuthorizedClient(email) {
    const stmt = db.prepare("SELECT email FROM users WHERE email = ? AND user_type = 'client'");
    const result = stmt.get(email.toLowerCase());
    return result !== undefined;
}

// Admins
function isAdminInDb(email) {
    const stmt = db.prepare('SELECT email FROM admins WHERE email = ?');
    const result = stmt.get(email.toLowerCase());
    return result !== undefined;
}

function getAllAdmins() {
    const stmt = db.prepare('SELECT id, email, created_at FROM admins ORDER BY email');
    return stmt.all();
}

function addAdmin(email) {
    try {
        const stmt = db.prepare('INSERT INTO admins (email) VALUES (?)');
        stmt.run(email.toLowerCase());

        // If they were a client, update their user_type
        const updateStmt = db.prepare("UPDATE users SET user_type = 'admin' WHERE email = ?");
        updateStmt.run(email.toLowerCase());

        return true;
    } catch (err) {
        return false;
    }
}

function removeAdmin(adminId) {
    const mainAdminEmail = process.env.ADMIN_EMAIL;

    // Get admin email first
    const getStmt = db.prepare('SELECT email FROM admins WHERE id = ?');
    const admin = getStmt.get(adminId);

    if (!admin) {
        return { success: false, message: 'Admin not found' };
    }

    if (admin.email.toLowerCase() === mainAdminEmail.toLowerCase()) {
        return { success: false, message: 'Cannot remove main admin' };
    }

    // Remove from admins table
    const deleteStmt = db.prepare('DELETE FROM admins WHERE id = ?');
    deleteStmt.run(adminId);

    // Check if user exists
    const userStmt = db.prepare('SELECT id FROM users WHERE email = ?');
    const user = userStmt.get(admin.email);

    if (user) {
        const updateStmt = db.prepare("UPDATE users SET user_type = 'client' WHERE email = ?");
        updateStmt.run(admin.email);
    } else {
        const insertStmt = db.prepare(`
            INSERT INTO users (email, client_name, phone, address, company, user_type) 
            VALUES (?, ?, '', '', '', 'client')
        `);
        insertStmt.run(admin.email, admin.email.split('@')[0]);
    }

    return { success: true, message: 'Admin removed and converted to client' };
}

// Products
function getAllProducts() {
    const stmt = db.prepare('SELECT * FROM products ORDER BY category, product_name');
    const products = stmt.all();
    return products.map(p => {
        if (p.product_options) {
            p.product_options = cleanOptions(p.product_options);
        }
        return p;
    });
}

function getProductById(productId) {
    const stmt = db.prepare('SELECT * FROM products WHERE id = ?');
    const product = stmt.get(productId);
    if (product && product.product_options) {
        product.product_options = cleanOptions(product.product_options);
    }
    return product;
}

function getProductsByCategory(category) {
    const stmt = db.prepare('SELECT * FROM products WHERE category = ? ORDER BY product_name');
    const products = stmt.all(category);
    return products.map(p => {
        if (p.product_options) {
            p.product_options = cleanOptions(p.product_options);
        }
        return p;
    });
}

function addProduct(productName, category, productOptions, productRate, stockStatus, imageFilename) {
    const cleanedOptions = cleanOptions(productOptions);
    const stmt = db.prepare(`
        INSERT INTO products (product_name, category, product_options, product_rate, stock_status, image_filename)
        VALUES (?, ?, ?, ?, ?, ?)
    `);
    return stmt.run(productName, category, cleanedOptions, productRate, stockStatus, imageFilename);
}

function updateProduct(productId, productName, category, productOptions, productRate, stockStatus, imageFilename = null) {
    const cleanedOptions = cleanOptions(productOptions);
    if (imageFilename) {
        const stmt = db.prepare(`
            UPDATE products 
            SET product_name = ?, category = ?, product_options = ?, 
                product_rate = ?, stock_status = ?, image_filename = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `);
        return stmt.run(productName, category, cleanedOptions, productRate, stockStatus, imageFilename, productId);
    } else {
        const stmt = db.prepare(`
            UPDATE products 
            SET product_name = ?, category = ?, product_options = ?, 
                product_rate = ?, stock_status = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `);
        return stmt.run(productName, category, cleanedOptions, productRate, stockStatus, productId);
    }
}

function deleteProduct(productId) {
    const stmt = db.prepare('DELETE FROM products WHERE id = ?');
    return stmt.run(productId);
}

function getProductNameById(productId) {
    const stmt = db.prepare('SELECT product_name FROM products WHERE id = ?');
    const result = stmt.get(productId);
    return result ? result.product_name : null;
}

// Orders
function createOrder(productName, expectedDate, quantity, comments, userEmail) {
    const stmt = db.prepare(`
        INSERT INTO orders (product_name, expected_date, quantity, comments, user_email, status, last_updated)
        VALUES (?, ?, ?, ?, ?, 'inquiry received', datetime('now', '+4 hours'))
    `);
    return stmt.run(productName, expectedDate, quantity, comments, userEmail);
}

function getOrdersByUser(userEmail) {
    const stmt = db.prepare('SELECT * FROM orders WHERE user_email = ? ORDER BY created_at DESC');
    return stmt.all(userEmail);
}

function getAllOrders() {
    const stmt = db.prepare('SELECT * FROM orders ORDER BY datetime(last_updated) DESC');
    return stmt.all();
}

function getOrderById(orderId) {
    const stmt = db.prepare('SELECT * FROM orders WHERE id = ?');
    return stmt.get(orderId);
}

function updateOrderStatus(orderId, status) {
    const stmt = db.prepare(`
        UPDATE orders SET status = ?, last_updated = datetime('now', '+4 hours') WHERE id = ?
    `);
    return stmt.run(status, orderId);
}

function deleteOrder(orderId) {
    const stmt = db.prepare('DELETE FROM orders WHERE id = ?');
    return stmt.run(orderId);
}

// Messages
function createMessage(orderId, userEmail, subject, body, attachmentName, orderName, orderQuantity) {
    const stmt = db.prepare(`
        INSERT INTO messages (order_id, user_email, subject, body, attachment_name, order_name, order_quantity, created_at, is_read)
        VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), 0)
    `);
    return stmt.run(orderId, userEmail, subject, body, attachmentName, orderName, orderQuantity);
}

function getMessagesByUser(userEmail) {
    const stmt = db.prepare(`
        SELECT m.id, m.order_id, m.subject, m.body, m.attachment_name, m.order_name, m.order_quantity, 
               m.created_at, m.is_read, o.status AS order_status
        FROM messages m
        LEFT JOIN orders o ON m.order_id = o.id
        WHERE m.user_email = ?
        ORDER BY m.created_at DESC
    `);
    return stmt.all(userEmail);
}

function markMessagesAsRead(userEmail) {
    const stmt = db.prepare(`
        UPDATE messages SET is_read = 1 
        WHERE user_email = ? AND (is_read = 0 OR is_read IS NULL)
    `);
    return stmt.run(userEmail);
}

function getUnreadMessageCount(userEmail) {
    const stmt = db.prepare(`
        SELECT COUNT(*) as count FROM messages 
        WHERE user_email = ? AND (is_read = 0 OR is_read IS NULL)
    `);
    const result = stmt.get(userEmail);
    return result ? result.count : 0;
}

function getMessageById(messageId) {
    const stmt = db.prepare('SELECT * FROM messages WHERE id = ?');
    return stmt.get(messageId);
}

module.exports = {
    db,
    initDb,
    // Users
    addClient,
    getAllClients,
    getClientById,
    updateClient,
    deleteClient,
    isAuthorizedClient,
    // Admins
    isAdminInDb,
    getAllAdmins,
    addAdmin,
    removeAdmin,
    // Products
    getAllProducts,
    getProductById,
    getProductsByCategory,
    addProduct,
    updateProduct,
    deleteProduct,
    getProductNameById,
    // Orders
    createOrder,
    getOrdersByUser,
    getAllOrders,
    getOrderById,
    updateOrderStatus,
    deleteOrder,
    // Messages
    createMessage,
    getMessagesByUser,
    markMessagesAsRead,
    getUnreadMessageCount,
    getMessageById
};
