export const updateProduct = async (req: Request, res: Response) => {
    const { id } = req.params;
    const productData: UpdateProductInput = req.body;
    const productId: number = parseInt(id, 10);

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }

    try {
        await query('BEGIN');

        // Adjust product price if necessary
        let adjustedProductPrice: number = productData.price;
        if (adjustedProductPrice < 1000) {
            adjustedProductPrice = parseFloat((adjustedProductPrice * 1000).toFixed(2));
        }

        // Update product details
        const result = await query(
            `
            UPDATE products 
            SET 
                name = $1, 
                description = $2, 
                brand_id = $3, 
                has_color = $4, 
                has_size = $5, 
                product_type = $6, 
                price = $7, 
                qty = $8, 
                updated_at = CURRENT_TIMESTAMP 
            WHERE id = $9 
            RETURNING *
            `,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                productData.has_color,
                productData.has_size,
                productData.product_type,
                adjustedProductPrice,
                productData.qty,
                productId
            ]
        );

        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }

        // Delete existing category associations
        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);

        // Determine if main images are provided in the update
        const hasMainImagesInUpdate = Array.isArray(productData.images) && productData.images.length > 0;

        // Handle Main Images
        let insertedMainImages: Image[];

        if (hasMainImagesInUpdate) {
            // Delete existing main images
            await query('DELETE FROM images WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL', [productId]);

            // Insert new main images
            const imagesInsertPromises = productData.images.map((image: ImageInput) =>
                query(
                    `INSERT INTO images (product_id, url, color_id, size_id)
                     VALUES ($1, $2, $3, $4)
                     RETURNING id, url, color_id, size_id`,
                    [productId, image.url || image, image.color_id || null, image.size_id || null]
                )
            );

            const insertedImagesResults = await Promise.all(imagesInsertPromises);
            insertedMainImages = insertedImagesResults.map((imgResult: any) => ({
                id: imgResult.rows[0].id,
                url: imgResult.rows[0].url,
                color_id: imgResult.rows[0].color_id,
                size_id: imgResult.rows[0].size_id
            }));
        } else {
            // Fetch existing main images
            const existingMainImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );

            insertedMainImages = existingMainImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }

        // Insert new category associations
        if (Array.isArray(productData.category_ids) && productData.category_ids.length > 0) {
            const categoryInsertPromises = productData.category_ids.map((categoryId: number) =>
                query(
                    `INSERT INTO product_categories (product_id, category_id) 
                     VALUES ($1, $2)`,
                    [productId, categoryId]
                )
            );
            await Promise.all(categoryInsertPromises);
        }

        // Insert or Update Colors and Sizes
        let insertedColors: number[] = [];
        let insertedSizes: number[] = [];

        if (productData.has_color && Array.isArray(productData.colors)) {
            for (const colorInput of productData.colors) {
                // Adjust color price if necessary
                let adjustedColorPrice: number = colorInput.price;
                if (adjustedColorPrice < 1000) {
                    adjustedColorPrice = parseFloat((adjustedColorPrice * 1000).toFixed(2));
                }

                // Insert or Update color
                let colorId: number;
                if (colorInput.id) {
                    // Update existing color
                    await query(
                        `
                        UPDATE colors 
                        SET 
                            name = $1, 
                            price = $2, 
                            qty = $3, 
                            sold = $4, 
                            has_size = $5 
                        WHERE id = $6
                        `,
                        [
                            colorInput.name,
                            adjustedColorPrice,
                            colorInput.qty,
                            colorInput.sold || 0,
                            colorInput.has_size,
                            colorInput.id
                        ]
                    );
                    colorId = colorInput.id;
                } else {
                    // Insert new color
                    const colorResult = await query(
                        `
                        INSERT INTO colors 
                            (product_id, name, price, qty, sold, has_size)
                        VALUES 
                            ($1, $2, $3, $4, 0, $5)
                        RETURNING id
                        `,
                        [productId, colorInput.name, adjustedColorPrice, colorInput.qty, colorInput.has_size]
                    );
                    colorId = colorResult.rows[0].id;
                    insertedColors.push(colorId);
                }

                // Handle Color Images
                const hasColorImagesInUpdate = Array.isArray(colorInput.images) && colorInput.images.length > 0;

                let colorImages: string[] = [];

                if (hasColorImagesInUpdate) {
                    // Delete existing images for this color
                    await query('DELETE FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL', [productId, colorId]);

                    // Insert new images
                    const colorImagesInsertPromises = colorInput.images.map((image: ImageInput) =>
                        query(
                            `INSERT INTO images (product_id, color_id, url, size_id)
                             VALUES ($1, $2, $3, $4)
                             RETURNING id, url, color_id, size_id`,
                            [productId, colorId, image.url || image, image.size_id || null]
                        )
                    );
                    const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                    colorImages = insertedColorImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                } else {
                    // Fetch existing color images
                    const existingColorImagesResult = await query(
                        `SELECT url FROM images 
                         WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                        [productId, colorId]
                    );
                    colorImages = existingColorImagesResult.rows.map((row: any) => row.url);
                }

                // Handle Sizes within Color
                let colorSizes: Size[] = [];

                if (colorInput.has_size && Array.isArray(colorInput.sizes)) {
                    for (const sizeInput of colorInput.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice: number = sizeInput.price;
                        if (adjustedSizePrice < 1000) {
                            adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                        }

                        // Insert or Update size
                        let sizeId: number;
                        if (sizeInput.id) {
                            // Update existing size
                            await query(
                                `
                                UPDATE sizes 
                                SET 
                                    name = $1, 
                                    price = $2, 
                                    qty = $3, 
                                    sold = $4, 
                                    color_id = $5 
                                WHERE id = $6
                                `,
                                [
                                    sizeInput.name,
                                    adjustedSizePrice,
                                    sizeInput.qty,
                                    sizeInput.sold || 0,
                                    colorId,
                                    sizeInput.id
                                ]
                            );
                            sizeId = sizeInput.id;
                        } else {
                            // Insert new size
                            const sizeResult = await query(
                                `
                                INSERT INTO sizes 
                                    (product_id, color_id, name, price, qty, sold)
                                VALUES 
                                    ($1, $2, $3, $4, $5, 0)
                                RETURNING id
                                `,
                                [productId, colorId, sizeInput.name, adjustedSizePrice, sizeInput.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                            insertedSizes.push(sizeId);
                        }

                        // Handle Size Images
                        const hasSizeImagesInUpdate = Array.isArray(sizeInput.images) && sizeInput.images.length > 0;

                        let sizeImages: string[] = [];

                        if (hasSizeImagesInUpdate) {
                            // Delete existing images for this size
                            await query('DELETE FROM images WHERE product_id = $1 AND size_id = $2', [productId, sizeId]);

                            // Insert new images
                            const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                                query(
                                    `INSERT INTO images (product_id, size_id, url)
                                     VALUES ($1, $2, $3)
                                     RETURNING id, url, color_id, size_id`,
                                    [productId, sizeId, image.url || image]
                                )
                            );
                            const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                            sizeImages = insertedSizeImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                        } else {
                            // Fetch existing size images
                            const existingSizeImagesResult = await query(
                                `SELECT url FROM images 
                                 WHERE product_id = $1 AND size_id = $2`,
                                [productId, sizeId]
                            );
                            sizeImages = existingSizeImagesResult.rows.map((row: any) => row.url);
                        }

                        // Append to colorSizes
                        colorSizes.push({
                            id: sizeId,
                            name: sizeInput.name,
                            price: adjustedSizePrice,
                            qty: sizeInput.qty,
                            sold: sizeInput.sold || 0,
                            color_id: colorId,
                            images: sizeImages
                        });
                    }
                }
            }
        }


        // Fetch and populate categories with details
        const categories = await fetchCategoryDetails(productData.category_ids || []);

        // Handle Images for updatedProduct
        // Since we've already handled main images, colors, and sizes, we need to construct the updatedProduct accordingly

        // Fetch existing color images and size images if not updated
        // To do this, we need to fetch all existing images and assign them appropriately

        // Fetch all colors
        const colorsResult = await query(
            `SELECT id, name, price, qty, sold, has_size FROM colors WHERE product_id = $1`,
            [productId]
        );

        // Build colors with images and sizes
        const colors: Color[] = await Promise.all(
            colorsResult.rows.map(async (colorRow: any) => {
                // Fetch images for this color
                const colorImagesResult = await query(
                    `SELECT url FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                    [productId, colorRow.id]
                );
                const colorImages = colorImagesResult.rows.map((row: any) => row.url);

                // Fetch sizes for this color
                const sizesResult = await query(
                    `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id = $2`,
                    [productId, colorRow.id]
                );

                const sizes: Size[] = await Promise.all(
                    sizesResult.rows.map(async (sizeRow: any) => {
                        // Fetch images for this size
                        const sizeImagesResult = await query(
                            `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                            [productId, sizeRow.id]
                        );
                        const sizeImages = sizeImagesResult.rows.map((row: any) => row.url);

                        return {
                            id: sizeRow.id,
                            name: sizeRow.name,
                            price: sizeRow.price,
                            qty: sizeRow.qty,
                            sold: sizeRow.sold,
                            color_id: colorRow.id,
                            images: sizeImages
                        };
                    })
                );

                return {
                    id: colorRow.id,
                    name: colorRow.name,
                    price: colorRow.price,
                    qty: colorRow.qty,
                    sold: colorRow.sold,
                    has_size: colorRow.has_size,
                    images: colorImages,
                    sizes: sizes
                };
            })
        );

        // Fetch standalone sizes if the product does not have colors but has sizes
        let standaloneSizes: Size[] = [];
        if (!productData.has_color && productData.has_size) {
            const sizesResult = await query(
                `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id IS NULL`,
                [productId]
            );

            standaloneSizes = sizesResult.rows.map((sizeRow: any) => ({
                id: sizeRow.id,
                name: sizeRow.name,
                price: sizeRow.price,
                qty: sizeRow.qty,
                sold: sizeRow.sold,
                color_id: null,
                images: [] // Fetch images if necessary
            }));

            // Optionally, fetch images for standalone sizes
            standaloneSizes = await Promise.all(
                standaloneSizes.map(async (size: Size) => {
                    const sizeImagesResult = await query(
                        `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                        [productId, size.id]
                    );
                    size.images = sizeImagesResult.rows.map((row: any) => row.url);
                    return size;
                })
            );
        }

        // Fetch existing main images if not updated
        if (!hasMainImagesInUpdate) {
            const existingMainImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );

            insertedMainImages = existingMainImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }

        // Construct the updated product object as it should appear in the cache
        const updatedProduct: Product = {
            id: productId,
            name: productData.name,
            description: productData.description,
            brand_id: productData.brand_id,
            has_color: productData.has_color,
            has_size: productData.has_size,
            product_type: productData.product_type,
            price: adjustedProductPrice,
            qty: productData.qty,
            sold: result.rows[0].sold, // Retain existing sold count
            created_at: result.rows[0].created_at.toISOString(),
            updated_at: new Date().toISOString(),
            categories: categories,
            colors: colors,
            sizes: standaloneSizes,
            images: insertedMainImages
        };

        // Update the cache
        updateProductInCache(updatedProduct);

        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: updatedProduct });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
  detail: 'Key (product_id, category_id)=(25, 153) already exists.',
import { Pool } from 'pg';
import bcrypt from 'bcrypt';
import dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
    host: 'ep-young-math-a2o7mpxf-pooler.eu-central-1.aws.neon.tech',
    database: 'ecommerce',
    user: 'ecommerce_owner',
    password: 'np8CZAUYKP3f',
    ssl: {
        rejectUnauthorized: false
    }
});

async function createTables(client: any): Promise<void> {
    try {
        await client.query('BEGIN');

        // 1. Create users table
        await client.query(`
            CREATE TABLE IF NOT EXISTS users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    dob DATE,
    phone_number VARCHAR(20),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP);
        `);



        // 3. Create categories table
        await client.query(`
            CREATE TABLE IF NOT EXISTS categories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) UNIQUE NOT NULL,
                is_brand BOOLEAN NOT NULL
            );
        `);

        // 4. Create subcategories table
        await client.query(`
            CREATE TABLE IF NOT EXISTS subcategories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                category_id INTEGER NOT NULL,
                FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE,
                UNIQUE(name, category_id)
            );
        `);

        // 5. Create products table with foreign key to brands
        await client.query(`
            CREATE TABLE IF NOT EXISTS products (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT NOT NULL,
                brand_id INTEGER NOT NULL,
                has_color BOOLEAN NOT NULL DEFAULT false,
                has_size BOOLEAN NOT NULL DEFAULT false,
                product_type VARCHAR(10) NOT NULL,
                price NUMERIC(10, 2) CHECK (price >= 0),
                qty INTEGER CHECK (qty >= 0),
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (brand_id) REFERENCES subcategories (id) ON DELETE RESTRICT
                -- Changed from ON DELETE SET NULL to ON DELETE RESTRICT to maintain NOT NULL
            );
        `);

        // 6. Create colors table with foreign key to products
        await client.query(`
            CREATE TABLE IF NOT EXISTS colors (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                name VARCHAR(255) NOT NULL,
                has_size BOOLEAN NOT NULL DEFAULT false,
                price NUMERIC(10,2) CHECK (price >= 0),
                qty INTEGER NOT NULL CHECK (qty >= 0),
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                UNIQUE(name, product_id)
            );
        `);

        // 7. Create sizes table with foreign keys to products and colors
        await client.query(`
            CREATE TABLE IF NOT EXISTS sizes (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                color_id INTEGER,
                name VARCHAR(255) NOT NULL,
                price NUMERIC(10,2) CHECK (price >= 0),
                qty INTEGER CHECK (qty >= 0),
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (color_id) REFERENCES colors (id) ON DELETE SET NULL,
                UNIQUE(name, color_id, product_id)
            );
        `);

        // 8. Create images table with foreign keys to products, colors, and sizes
        await client.query(`
            CREATE TABLE IF NOT EXISTS images (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                color_id INTEGER,
                size_id INTEGER,
                url VARCHAR(255));
        `);

        // 9. Create coupons table
        await client.query(`
            CREATE TABLE IF NOT EXISTS coupons (
                id SERIAL PRIMARY KEY,
                code VARCHAR(255) UNIQUE NOT NULL,
                name VARCHAR(255) NOT NULL,
                description VARCHAR(255),
                discount_amount NUMERIC(10,2),
                discount_percentage NUMERIC(5,2) CHECK (discount_percentage >= 0 AND discount_percentage <= 100),
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                usage_limit INTEGER NOT NULL CHECK (usage_limit >= 0),
                min_purchase_amount NUMERIC(10,2) NOT NULL CHECK (min_purchase_amount >= 0),
                max_discount_amount NUMERIC(10,2) CHECK (max_discount_amount >= 0),
                is_active BOOLEAN DEFAULT true,
                coupon_image VARCHAR(255),
                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
                updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        `);

        // 10. Create address table
        await client.query(`
            CREATE TABLE IF NOT EXISTS address (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL,
                full_name VARCHAR(255) NOT NULL,
                governorate VARCHAR(255) NOT NULL,
                city VARCHAR(255) NOT NULL,
                address TEXT NOT NULL,
                closest_point TEXT,
                phone_number VARCHAR(80) NOT NULL,
                type VARCHAR(20),
                FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
            );
        `);

        // 11. Create product_categories table with composite primary key
        await client.query(`
            CREATE TABLE IF NOT EXISTS product_categories (
                product_id INTEGER NOT NULL,
                category_id INTEGER NOT NULL,
                PRIMARY KEY (product_id, category_id),
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (category_id) REFERENCES subcategories (id) ON DELETE CASCADE
            );
        `);

        // 12. Create carts table with foreign keys to users and coupons
        await client.query(`
            CREATE TABLE IF NOT EXISTS carts (
                id SERIAL PRIMARY KEY,
                user_id INTEGER,
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                notes VARCHAR(255),
                full_name VARCHAR(255) NOT NULL,
                governorate VARCHAR(50) NOT NULL,
                city VARCHAR(100) NOT NULL,
                address TEXT NOT NULL,
                closest_point TEXT,
                phone_number VARCHAR(20) NOT NULL,
                type VARCHAR(20),
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                coupon_id INTEGER,
                updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE SET NULL,
                FOREIGN KEY (coupon_id) REFERENCES coupons (id) ON DELETE SET NULL
            );
        `);

        // 13. Create cart_items table with foreign keys to carts, products, sizes, and colors
        await client.query(`
            CREATE TABLE IF NOT EXISTS cart_items (
                id SERIAL PRIMARY KEY,
                cart_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                size_id INTEGER,
                color_id INTEGER,
                quantity INTEGER NOT NULL CHECK (quantity > 0),
                FOREIGN KEY (cart_id) REFERENCES carts (id) ON DELETE CASCADE
            );
        `);
        await client.query(`
        CREATE TABLE "counter" (
    id SERIAL PRIMARY KEY
);
       `);
        await client.query(`ALTER SEQUENCE "counter_id_seq" RESTART WITH 3500`);
        await client.query(`INSERT INTO counter(id) VALUES(3500)`);

        const tables = ['users', 'categories', 'subcategories', 'products', 'colors', 'sizes', 'images', 'coupons', 'address', 'carts', 'cart_items'];
        for (const table of tables) {
            await client.query(`
                DO $$
                BEGIN
                    IF EXISTS (SELECT 1 FROM information_schema.sequences WHERE sequence_name = '${table}_id_seq') THEN
                        ALTER SEQUENCE ${table}_id_seq RESTART WITH 5000;
                    END IF;
                END $$;
            `);
        }

        // Update counter table
        await client.query(`DROP TABLE IF EXISTS Counter`);
        await client.query(`
            CREATE TABLE Counter (
                id SERIAL PRIMARY KEY
            );
        `);
        await client.query(`ALTER SEQUENCE Counter_id_seq RESTART WITH 5000`);
        await client.query(`INSERT INTO Counter(id) VALUES(5000)`);

        // Create color_images table
        await client.query(`
            CREATE TABLE IF NOT EXISTS color_images (
                name VARCHAR(255) PRIMARY KEY,
                url TEXT NOT NULL
            );
        `);

        // Foreign Key Indexes
        await client.query(`CREATE INDEX IF NOT EXISTS idx_products_brand_id ON products (brand_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_colors_product_id ON colors (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_product_id ON sizes (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_color_id ON sizes (color_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_product_categories_category_id ON product_categories (category_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_user_id ON carts (user_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_coupon_id ON carts (coupon_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_cart_items_cart_id ON cart_items (cart_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_address_user_id ON address (user_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_coupons_code ON coupons (code);`);

        // Commonly Searched Columns Indexes
        await client.query(`CREATE INDEX IF NOT EXISTS idx_products_name ON products (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_categories_name ON categories (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_subcategories_name ON subcategories (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_colors_name ON colors (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_name ON sizes (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_status ON carts (status);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_coupons_name ON coupons (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_address_city ON address (city);`);

        // 15. Create Triggers for updated_at Columns
        // Function to update updated_at column
        await client.query(`
            CREATE OR REPLACE FUNCTION update_updated_at_column()
            RETURNS TRIGGER AS $$
               BEGIN
                   NEW.updated_at = NOW();
                   RETURN NEW;
               END;
               $$ LANGUAGE plpgsql;
        `);

        // Trigger for products table
        await client.query(`
            CREATE TRIGGER trg_update_products_updated_at
            BEFORE UPDATE ON products
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Trigger for carts table
        await client.query(`
            CREATE TRIGGER trg_update_carts_updated_at
            BEFORE UPDATE ON carts
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Trigger for coupons table
        await client.query(`
            CREATE TRIGGER trg_update_coupons_updated_at
            BEFORE UPDATE ON coupons
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Commit Transaction
        await client.query('COMMIT');
        console.log('All tables created successfully with enhanced constraints and indexes.');
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Error creating tables:', error);
        throw error;
    }
}
async function createDatabase(): Promise<void> {
    let client;
    try {
        client = await pool.connect();
        console.log('Connected to the database successfully');

        await createTables(client);

        // Check if superadmin already exists
        const superadminCheck = await client.query(
            "SELECT * FROM staff WHERE email = 'admin@example.com'"
        );

        if (superadminCheck.rows.length === 0) {
            // Insert sample superadmin
            const hashedPassword = await bcrypt.hash('admin123', 10);
            await client.query(`
                INSERT INTO staff (name, email, password, rank)
                VALUES ('Super Admin', 'admin@example.com', $1, 'superadmin');
            `, [hashedPassword]);

            console.log('Sample superadmin inserted successfully');
        } else {
            console.log('Superadmin already exists');
        }
        const subcategories = [
            'Shampoos', 'Conditioners', 'Dry Shampoos',
            'Hair Oils', 'Masks', 'Hair Mist',
            'Hair Brush', 'Hair Styling', 'Hair Treatments', 'Message Tools'
        ];

        for (const subcategory of subcategories) {
            await client.query(
                `INSERT INTO subcategories (name, category_id) VALUES ($1, $2) 
                ON CONFLICT DO NOTHING`,
                [subcategory, 3]
            );
        }
        await client.query(`
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            original_price DECIMAL(10, 2) NOT NULL,
            sale_price DECIMAL(10, 2),
            description TEXT,
            images TEXT[],
            color VARCHAR(50),
            color_image TEXT,
            size VARCHAR(50),
            qty INTEGER NOT NULL,
            category_id INTEGER REFERENCES categories(id),
            brand_id INTEGER REFERENCES categories(id)
        );
    `);
        console.log('Hair Care subcategories inserted successfully');

    } catch (err) {
        console.error('Error creating database:', err);
    } finally {
        if (client) {
            client.release();
        }
        await pool.end();
    }
}



createTables(pool).catch(console.error);