import Database from 'better-sqlite3';
import { faker } from '@faker-js/faker';
import fs from 'fs';
import path from 'path';
import PDFDocument from 'pdfkit-table';
import { fileURLToPath } from 'url';

// Handling __dirname in ES modules
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

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

// Connect to SQLite DB
const db = new Database(path.join(__dirname, '..', 'invoices.db'));

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

// Create tables (with updated schema)
db.exec(`
    CREATE TABLE IF NOT EXISTS sellers (
        seller_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        ship_from_address TEXT,
        registered_office_address TEXT,
        gstin TEXT,
        pan TEXT,
        cin TEXT
    );

    CREATE TABLE IF NOT EXISTS customers (
        customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        billing_address TEXT,
        shipping_address TEXT,
        phone TEXT
    );

    CREATE TABLE IF NOT EXISTS invoices (
        invoice_id INTEGER PRIMARY KEY AUTOINCREMENT,
        invoice_number TEXT UNIQUE NOT NULL,
        order_id TEXT,
        seller_id INTEGER,
        customer_id INTEGER,
        order_date DATE,
        invoice_date DATE,
        grand_total REAL,
        FOREIGN KEY (seller_id) REFERENCES sellers(seller_id) ON DELETE RESTRICT,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
    );

    CREATE TABLE IF NOT EXISTS invoice_items (
        item_id INTEGER PRIMARY KEY AUTOINCREMENT,
        invoice_id INTEGER,
        category TEXT,
        title TEXT,
        fsn TEXT,
        hsn_sac TEXT,
        quantity INTEGER,
        gross_amount REAL,
        discount REAL,
        taxable_value REAL,
        cgst_rate REAL,
        cgst_amount REAL,
        sgst_rate REAL,
        sgst_amount REAL,
        total_amount REAL,
        warranty TEXT,
        serial_number TEXT,
        FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE
    );
`);

// Prepared statements for insertion
const insertSeller = db.prepare(`
    INSERT INTO sellers (name, ship_from_address, registered_office_address, gstin, pan, cin)
    VALUES (?, ?, ?, ?, ?, ?)
`);

const insertCustomer = db.prepare(`
    INSERT INTO customers (name, billing_address, shipping_address, phone)
    VALUES (?, ?, ?, ?)
`);

const insertInvoice = db.prepare(`
    INSERT INTO invoices (invoice_number, order_id, seller_id, customer_id, order_date, invoice_date, grand_total)
    VALUES (?, ?, ?, ?, ?, ?, ?)
`);

const insertInvoiceItem = db.prepare(`
    INSERT INTO invoice_items (
        invoice_id, category, title, fsn, hsn_sac, quantity, gross_amount,
        discount, taxable_value, cgst_rate, cgst_amount, sgst_rate, sgst_amount,
        total_amount, warranty, serial_number
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`);

// Main function
async function generateData(amount: number) {
    console.log(`Generating ${amount} dummy invoices...`);

    // 1. Pre-generate pools of sellers, customers, and products
    const sellersPool: any[] = [];
    const customersPool: any[] = [];
    const productsPool: any[] = [];

    // Generate Sellers
    const numSellers = Math.max(2, Math.min(5, Math.ceil(amount / 2)));
    for (let i = 0; i < numSellers; i++) {
        const info = insertSeller.run(
            faker.company.name(),
            faker.location.streetAddress(),
            faker.location.streetAddress(),
            faker.finance.accountNumber(15),
            faker.finance.accountNumber(10),
            faker.finance.accountNumber(21)
        );

        // Retrieve the inserted seller
        const seller = db.prepare('SELECT * FROM sellers WHERE seller_id = ?').get(info.lastInsertRowid);
        sellersPool.push(seller);
    }

    // Generate Customers
    const numCustomers = Math.max(3, Math.min(10, amount));
    for (let i = 0; i < numCustomers; i++) {
        const info = insertCustomer.run(
            faker.person.fullName(),
            faker.location.streetAddress(),
            faker.location.streetAddress(),
            faker.phone.number()
        );

        // Retrieve the inserted customer
        const customer = db.prepare('SELECT * FROM customers WHERE customer_id = ?').get(info.lastInsertRowid);
        customersPool.push(customer);
    }

    // Generate Products
    const numProducts = 20;
    for (let i = 0; i < numProducts; i++) {
        productsPool.push({
            category: faker.commerce.department(),
            title: faker.commerce.productName(),
            fsn: faker.string.alphanumeric(10).toUpperCase(),
            hsn_sac: faker.string.numeric(6),
            base_price: parseFloat(faker.commerce.price({ min: 100, max: 5000 })),
            has_warranty: faker.datatype.boolean()
        });
    }

    // Generate Invoices
    for (let i = 0; i < amount; i++) {
        const seller = faker.helpers.arrayElement(sellersPool);
        const customer = faker.helpers.arrayElement(customersPool);

        const invoice_number = `INV-${faker.string.alphanumeric(8).toUpperCase()}`;
        const order_id = `ORD-${faker.string.numeric(6)}`;
        const order_date = faker.date.past({ years: 5 }).toISOString().split('T')[0];
        const invoice_date = order_date; // Keeping it simple

        let grand_total = 0;
        const numItems = faker.number.int({ min: 1, max: 20 });
        const selectedProducts = faker.helpers.arrayElements(productsPool, numItems);
        const generatedItems: any[] = [];

        for (const product of selectedProducts) {
            const quantity = faker.number.int({ min: 1, max: 10 });
            const gross_amount = product.base_price * quantity;

            // Randomly apply a discount
            let discount = 0;
            if (faker.datatype.boolean()) {
                // 5%, 10% or 15% discount
                const discountPercent = faker.helpers.arrayElement([0.05, 0.10, 0.15]);
                discount = gross_amount * discountPercent;
            }

            const taxable_value = gross_amount - discount;
            const cgst_rate = 9;
            const sgst_rate = 9;
            const cgst_amount = (taxable_value * cgst_rate) / 100;
            const sgst_amount = (taxable_value * sgst_rate) / 100;
            const total_amount = taxable_value + cgst_amount + sgst_amount;

            const warranty = product.has_warranty ? `${faker.number.int({ min: 1, max: 3 })} Year(s)` : null;
            const serial_number = faker.string.alphanumeric(12).toUpperCase();

            generatedItems.push({
                ...product,
                quantity,
                gross_amount,
                discount,
                taxable_value,
                cgst_rate,
                cgst_amount,
                sgst_rate,
                sgst_amount,
                total_amount,
                warranty,
                serial_number
            });

            grand_total += total_amount;
        }

        // Insert Invoice
        const invInfo = insertInvoice.run(
            invoice_number, order_id, seller.seller_id, customer.customer_id, order_date, invoice_date, grand_total
        );

        const invoiceData = {
            invoice_id: invInfo.lastInsertRowid,
            invoice_number,
            order_id,
            order_date,
            invoice_date,
            grand_total
        };

        // Insert Items
        for (const item of generatedItems) {
            insertInvoiceItem.run(
                invoiceData.invoice_id, item.category, item.title, item.fsn, item.hsn_sac,
                item.quantity, item.gross_amount, item.discount, item.taxable_value,
                item.cgst_rate, item.cgst_amount, item.sgst_rate, item.sgst_amount,
                item.total_amount, item.warranty, item.serial_number
            );
        }

        // Randomly generate a footer note for this invoice
        const footerNotes = [
            "Thank you for your business!",
            "Goods once sold cannot be returned. Terms & Conditions apply.",
            "Please make the payment within 15 days of the invoice date.",
            "Warranty void if product seal is broken or tampered with."
        ];
        const footerNote = faker.datatype.boolean() ? faker.helpers.arrayElement(footerNotes) : null;

        // Generate PDF
        await generateInvoicePDF(invoiceData, seller, customer, generatedItems, footerNote);
    }

    console.log(`Successfully generated ${amount} invoices!`);
}

async function generateInvoicePDF(invoice: any, seller: any, customer: any, items: any[], footerNote: string | null) {
    return new Promise<void>((resolve) => {
        const doc = new PDFDocument({ margin: 30, size: 'A4' });
        const pdfPath = path.join(invoicesDir, `${invoice.invoice_number}.pdf`);
        const writeStream = fs.createWriteStream(pdfPath);

        doc.pipe(writeStream);

        // Add content to PDF
        doc.fontSize(20).text('TAX INVOICE', { align: 'center' });
        doc.moveDown();

        doc.fontSize(10);

        // --- Header Info ---
        doc.text(`Invoice Number: ${invoice.invoice_number}`);
        doc.text(`Invoice Date: ${invoice.invoice_date}`);
        doc.text(`Order ID: ${invoice.order_id}`);
        doc.text(`Order Date: ${invoice.order_date}`);
        doc.moveDown();

        // --- Seller & Customer ---
        // Using two columns for layout
        const currentY = doc.y;

        // Seller (Left)
        doc.text('SOLD BY:', 30, currentY, { underline: true });
        doc.text(seller.name);
        doc.text(`Ship From: ${seller.ship_from_address}`, { width: 250 });
        doc.text(`Regd Office: ${seller.registered_office_address}`, { width: 250 });
        doc.text(`GSTIN: ${seller.gstin}`);
        doc.text(`PAN: ${seller.pan}`);
        doc.text(`CIN: ${seller.cin}`);

        // Customer (Right)
        doc.text('BILL TO / SHIP TO:', 300, currentY, { underline: true });
        doc.text(customer.name, 300, doc.y);
        doc.text(`Billing: ${customer.billing_address}`, { width: 250 });
        doc.text(`Shipping: ${customer.shipping_address}`, { width: 250 });
        doc.text(`Phone: ${customer.phone}`);

        doc.moveDown(2);

        // --- Line Items Table ---
        // Restore x margin for table
        doc.x = 30;

        const table = {
            title: "Line Items",
            headers: ["Description", "Qty", "Gross", "Dis.", "Taxable", "CGST", "SGST", "Total"],
            rows: items.map(item => {
                // Combine Title, Warranty, and Serial Number for the description column
                let desc = item.title;
                if (item.serial_number) desc += `\nS/N: ${item.serial_number}`;
                if (item.warranty) desc += `\nWarranty: ${item.warranty}`;

                return [
                    desc,
                    item.quantity.toString(),
                    item.gross_amount.toFixed(2),
                    item.discount.toFixed(2),
                    item.taxable_value.toFixed(2),
                    `${item.cgst_amount.toFixed(2)} (${item.cgst_rate}%)`,
                    `${item.sgst_amount.toFixed(2)} (${item.sgst_rate}%)`,
                    item.total_amount.toFixed(2)
                ];
            })
        };

        doc.table(table, {
            width: 535,
            prepareHeader: () => doc.font("Helvetica-Bold").fontSize(9),
            prepareRow: () => doc.font("Helvetica").fontSize(8)
        });

        doc.moveDown();
        doc.font("Helvetica-Bold").fontSize(12).text(`Grand Total: Rs. ${invoice.grand_total.toFixed(2)}`, { align: 'right' });

        // --- Footer Note ---
        if (footerNote) {
            doc.moveDown(2);
            doc.font("Helvetica-Oblique").fontSize(9).text(footerNote, { align: 'center' });
        }

        doc.end();
        writeStream.on('finish', () => resolve());
    });
}

// Entry point
const amountArg = process.argv[2];
const amount = parseInt(amountArg, 10);

if (isNaN(amount) || amount <= 0) {
    console.error('Usage: tsx create_dummy_invoices.ts <amount>');
    console.error('Please provide a valid positive number for the amount.');
    process.exit(1);
}

generateData(amount).catch(err => {
    console.error('Error generating data:', err);
});