const { Pool } = require('pg');
const dotenv = require('dotenv');
const path = require('path');

dotenv.config({ path: path.join(__dirname, '.env') });

const pool = new Pool({
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || 'localhost',
    database: process.env.DB_NAME || 'car',
    password: process.env.DB_PASSWORD || 'ragu@2003',
    port: process.env.DB_PORT || 5432,
});

const alterTable = async () => {
    const client = await pool.connect();
    try {
        console.log('Adding customer, credit card & discount columns to bookings table...');
        await client.query(`
            ALTER TABLE bookings
            ADD COLUMN IF NOT EXISTS customer_name       VARCHAR(100),
            ADD COLUMN IF NOT EXISTS customer_email      VARCHAR(100),
            ADD COLUMN IF NOT EXISTS customer_phone      VARCHAR(30),
            ADD COLUMN IF NOT EXISTS customer_address    TEXT,
            ADD COLUMN IF NOT EXISTS customer_license    VARCHAR(60),
            ADD COLUMN IF NOT EXISTS cc_holder           VARCHAR(100),
            ADD COLUMN IF NOT EXISTS cc_last4            VARCHAR(4),
            ADD COLUMN IF NOT EXISTS cc_expiry           VARCHAR(7),
            ADD COLUMN IF NOT EXISTS cc_type             VARCHAR(20),
            ADD COLUMN IF NOT EXISTS discount_code       VARCHAR(30),
            ADD COLUMN IF NOT EXISTS discount_percent    DECIMAL(5,2) DEFAULT 0,
            ADD COLUMN IF NOT EXISTS discount_amount     DECIMAL(10,2) DEFAULT 0,
            ADD COLUMN IF NOT EXISTS base_price          DECIMAL(10,2),
            ADD COLUMN IF NOT EXISTS notes               TEXT;
        `);

        // Seed sample data for existing bookings
        console.log('Seeding sample customer/card data for existing bookings...');
        const sampleCustomers = [
            { name: 'Mohammed Al-Rashid', email: 'mohammed.rashid@email.com', phone: '+971 50 123 4567', address: 'Al Barsha, Dubai, UAE', license: 'UAE-DXB-2019-447821', cc_holder: 'MOHAMMED AL-RASHID', cc_last4: '4242', cc_expiry: '09/27', cc_type: 'Visa', discount_code: 'FIRST10', discount_percent: 10 },
            { name: 'Sarah Johnson', email: 'sarah.j@gmail.com', phone: '+971 55 987 6543', address: 'JBR, Dubai, UAE', license: 'UAE-SHJ-2021-339102', cc_holder: 'SARAH JOHNSON', cc_last4: '8432', cc_expiry: '03/26', cc_type: 'Mastercard', discount_code: 'WEEKEND15', discount_percent: 15 },
            { name: 'Ahmed Khalil', email: 'ahmed.k@hotmail.com', phone: '+971 52 456 7890', address: 'Deira, Dubai, UAE', license: 'UAE-DXB-2020-112245', cc_holder: 'AHMED KHALIL', cc_last4: '5566', cc_expiry: '11/28', cc_type: 'Amex', discount_code: '', discount_percent: 0 },
            { name: 'Priya Nair', email: 'priya.nair@work.ae', phone: '+971 54 321 0987', address: 'Business Bay, Dubai', license: 'UAE-DXB-2022-778834', cc_holder: 'PRIYA NAIR', cc_last4: '9901', cc_expiry: '06/25', cc_type: 'Visa', discount_code: 'FLASH20', discount_percent: 20 },
        ];

        const bookingsResult = await client.query('SELECT id, total_price FROM bookings ORDER BY created_at DESC');
        for (let i = 0; i < bookingsResult.rows.length; i++) {
            const booking = bookingsResult.rows[i];
            const sample = sampleCustomers[i % sampleCustomers.length];
            const discountAmt = (Number(booking.total_price) * sample.discount_percent / 100).toFixed(2);
            await client.query(`
                UPDATE bookings SET
                    customer_name    = $1,
                    customer_email   = $2,
                    customer_phone   = $3,
                    customer_address = $4,
                    customer_license = $5,
                    cc_holder        = $6,
                    cc_last4         = $7,
                    cc_expiry        = $8,
                    cc_type          = $9,
                    discount_code    = $10,
                    discount_percent = $11,
                    discount_amount  = $12,
                    base_price       = $13
                WHERE id = $14
            `, [sample.name, sample.email, sample.phone, sample.address, sample.license,
            sample.cc_holder, sample.cc_last4, sample.cc_expiry, sample.cc_type,
            sample.discount_code, sample.discount_percent, discountAmt,
            booking.total_price, booking.id]);
        }

        console.log('Done! All columns added and sample data seeded.');
    } catch (err) {
        console.error('Error:', err);
    } finally {
        client.release();
        await pool.end();
    }
};

alterTable();
