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 extra car details columns to cars table...');
        await client.query(`
    ALTER TABLE cars
    ADD COLUMN IF NOT EXISTS insurance VARCHAR(255),
    ADD COLUMN IF NOT EXISTS owner VARCHAR(255),
    ADD COLUMN IF NOT EXISTS kilometers INTEGER,
    ADD COLUMN IF NOT EXISTS car_mulkiya_id VARCHAR(100),
    ADD COLUMN IF NOT EXISTS maintenance_company VARCHAR(255),

    ADD COLUMN IF NOT EXISTS start_date_of_insurance DATE,
    ADD COLUMN IF NOT EXISTS end_date_of_insurance DATE,

    ADD COLUMN IF NOT EXISTS start_date_of_mulkiya DATE,
    ADD COLUMN IF NOT EXISTS end_date_of_mulkiya DATE,

    ADD COLUMN IF NOT EXISTS maintenance_start_date DATE,
    ADD COLUMN IF NOT EXISTS maintenance_end_date DATE,

    ADD COLUMN IF NOT EXISTS current_km NUMERIC,
    ADD COLUMN IF NOT EXISTS current_fuel VARCHAR(100),

    ADD COLUMN IF NOT EXISTS last_battery_change DATE,
    ADD COLUMN IF NOT EXISTS last_oil_change_km INTEGER,
    ADD COLUMN IF NOT EXISTS oil_change_interval INTEGER,
    ADD COLUMN IF NOT EXISTS last_brake_change_km INTEGER,
    ADD COLUMN IF NOT EXISTS brake_pad_interval INTEGER,
    
ADD COLUMN IF NOT EXISTS price_per_hour DECIMAL(10, 2),
ADD COLUMN IF NOT EXISTS price_per_week DECIMAL(10, 2),
ADD COLUMN IF NOT EXISTS price_per_month DECIMAL(10, 2),
    ADD COLUMN IF NOT EXISTS rental_options JSONB;
`);
        console.log('Columns ensured successfully!');
    } catch (err) {
        console.error('Error altering table:', err);
    } finally {
        client.release();
        await pool.end();
    }
};

alterTable();
