I'm signing to 
const token = jwt.sign(
            {
                userId: user.user_id,
                name: user.name,
                active: true,
                email: user.email,
                role: 'user',
                phone_number: user.phone_number,
                dob: user.dob
            },
            process.env.JWT_SECRET!,
            { expiresIn: '24h' }
        )
userId so update the cartController.
First, update createCart so it gets the userId and pass it to the database if not it should pass null.
Second, create getUserCarts which should return all the orders for requested userId...
cartController.ts:
// src/controllers/cartController.ts

import { Request, Response, NextFunction } from 'express';
import * as cartModel from '../models/cartModel';
import { query } from "../config/database";
import { deleteCartById, getAllCartsWithItems, updateCartStatus } from "../models/cartModel";
import {
    decreaseProductQtyInCache,
    increaseColorSoldInCache,
    increaseProductSoldInCache,
    increaseSizeSoldInCache
} from "../cacheHelper";
import {cartCache, flushAllCarts, flushCartCache} from "../cache";

/**
 * Creates a new cart and updates product quantities accordingly.
 */
async function updateProductQuantity(productId: number, sizeId: number | null, colorId: number | null, quantity: number): Promise<void> {
    let updateQuery = '';
    const params: any[] = [quantity, productId, null]; // Initialize with null

    if (sizeId) {
        updateQuery = 'UPDATE sizes SET qty = qty - $1 WHERE id = $2 AND product_id = $3';
        params[2] = sizeId;
    } else if (colorId) {
        updateQuery = 'UPDATE colors SET qty = qty - $1 WHERE id = $2 AND product_id = $3';
        params[2] = colorId;
    } else {
        updateQuery = 'UPDATE products SET qty = qty - $1 WHERE id = $2';
        params.splice(2, 1); // Remove the third parameter as it's not needed
    }

    if (updateQuery) {
        await query(updateQuery, params);

        // Decrease the quantity in the cache
        decreaseProductQtyInCache(productId, sizeId, colorId, quantity);
    }
}

/**
 * Increases the 'sold' count based on product attributes.
 * @param productId - ID of the product
 * @param sizeId - ID of the size (optional)
 * @param colorId - ID of the color (optional)
 * @param quantity - Quantity to increase
 */
async function increaseSold(productId: number, sizeId: number | null, colorId: number | null, quantity: number): Promise<void> {
    let updateQuery = '';
    let params: any[] = [quantity, productId];

    if (sizeId) {
        updateQuery = 'UPDATE sizes SET sold = sold + $1 WHERE id = $2 AND product_id = $3';
        params.push(sizeId);
    } else if (colorId) {
        updateQuery = 'UPDATE colors SET sold = sold + $1 WHERE id = $2 AND product_id = $3';
        params.push(colorId);
    }
    let productQuery = 'UPDATE products SET sold = sold + $1 WHERE id = $2';
    let productParams = [quantity, productId];

    if (updateQuery) {
        await query(updateQuery, params);
        await query(productQuery, productParams);

        // Update the sold count in the cache
        if (sizeId) {
            increaseSizeSoldInCache(productId, sizeId, quantity);
        } else if (colorId) {
            increaseColorSoldInCache(productId, colorId, quantity);
        }
        increaseProductSoldInCache(productId, quantity);
    }
}
/**
 * Creates a new cart and updates product quantities and sold counts accordingly.
 */
export const createCart = async (req: Request, res: Response) => {
    try {
        const { coupon_id, ...cartData } = req.body;

        await query('BEGIN');

        const cartResult = await query(
            'INSERT INTO carts (user_id, status, notes, full_name, governorate, city, address, closest_point, phone_number, type) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING *',
            [cartData.user_id, 'Pending', cartData.notes, cartData.full_name, cartData.governorate, cartData.city, cartData.address, cartData.closest_point, cartData.phone_number, cartData.type]
        );
        const cart = cartResult.rows[0];

        if (coupon_id) {
            const couponResult = await query('SELECT * FROM coupons WHERE id = $1', [coupon_id]);
            const coupon = couponResult.rows[0];

            if (coupon && coupon.is_active) {
                if (coupon.usage_limit !== 0) {
                    await query('UPDATE coupons SET usage_limit = usage_limit - 1 WHERE id = $1', [coupon_id]);
                    const updatedCouponResult = await query('SELECT usage_limit FROM coupons WHERE id = $1', [coupon_id]);
                    if (updatedCouponResult.rows[0].usage_limit === 0) {
                        await query('UPDATE coupons SET is_active = false WHERE id = $1', [coupon_id]);
                    }
                }
                await query('UPDATE carts SET coupon_id = $1 WHERE id = $2', [coupon_id, cart.id]);
            } else {
                await query('ROLLBACK');
                return res.status(400).json({ message: 'Invalid or inactive coupon' });
            }
        }

        for (const item of cartData.items) {
            await query(
                'INSERT INTO cart_items (cart_id, product_id, size_id, color_id, quantity) VALUES ($1, $2, $3, $4, $5)',
                [cart.id, item.product_id, item.size_id, item.color_id, item.quantity]
            );

            // Decrease the product quantity
            await updateProductQuantity(item.product_id, item.size_id, item.color_id, item.quantity);

            // Increase the sold count based on product attributes
            await increaseSold(item.product_id, item.size_id, item.color_id, item.quantity);
        }

        await query('COMMIT');

        // Invalidate cart caches as data has changed
        flushAllCarts();

        res.status(201).json({ cart });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating cart:', error);
        res.status(500).json({ message: 'Error creating cart' });
    }
};

/**
 * Updates cart status.
 */
export async function updateCart(req: Request, res: Response, next: NextFunction) {
    try {
        const cartId = parseInt(req.params.id, 10);
        const { status } = req.body;

        const updatedCart = await updateCartStatus(cartId, status);

        if (!updatedCart) {
            return res.status(404).json({ message: 'Cart not found' });
        }

        // Invalidate specific cart cache if necessary
        if (updatedCart.user_id) {
            flushCartCache(updatedCart.user_id);
        }

        res.json({ message: 'Cart status updated successfully', cart: updatedCart });
    } catch (error) {
        next(error);
    }
}

/**
 * Deletes a cart by ID.
 */
export async function deleteCart(req: Request, res: Response, next: NextFunction) {
    try {
        const cartId = parseInt(req.params.id, 10);

        const deleted = await deleteCartById(cartId);

        if (!deleted) {
            return res.status(404).json({ message: 'Cart not found' });
        }

        // Invalidate all carts cache as data has changed
        flushAllCarts();

        res.json({ message: 'Cart deleted successfully' });
    } catch (error) {
        next(error);
    }
}

export async function getAllCarts(req: Request, res: Response, next: NextFunction) {
    try {
        const { page = 1, limit = 10 } = req.query;

        const pageNumber = parseInt(page as string, 10);
        const limitNumber = parseInt(limit as string, 10);

        const cacheKey = `allCarts_page_${pageNumber}_limit_${limitNumber}`;

        // Check if data is in cache
        const cachedCarts = cartCache.get(cacheKey);
        if (cachedCarts) {
            return res.json(cachedCarts);
        }

        const { carts, total } = await getAllCartsWithItemsPaginated(pageNumber, limitNumber);

        const response = { carts, total, page: pageNumber, limit: limitNumber };

        // Store in cache
        cartCache.set(cacheKey, response);

        res.json(response);
    } catch (error) {
        next(error);
    }
}
export async function getAllCartsWithItemsPaginated(page: number, limit: number) {
    const offset = (page - 1) * limit;

    const cartsResult = await query(
        `SELECT * FROM carts ORDER BY created_at DESC LIMIT $1 OFFSET $2`,
        [limit, offset]
    );
    const carts = cartsResult.rows;

    // Fetch total number of carts for pagination
    const totalResult = await query(`SELECT COUNT(*) FROM carts`);
    const total = parseInt(totalResult.rows[0].count, 10);

    // Fetch items for each cart
    for (const cart of carts) {
        const itemsResult = await query(
            `SELECT * FROM cart_items WHERE cart_id = $1`,
            [cart.id]
        );
        cart.items = itemsResult.rows;
    }

    return { carts, total };
}

cartModel.ts:
import { query } from '../config/database';


export interface Cart {
    id: number;
    user_id?: number | null;
    status: string;
    notes?: string;
    full_name?: string;
    governorate?: string;
    city?: string;
    address?: string;
    closest_point?: string;
    phone_number?: string;
    type?: string;
    created_at: Date;
    coupon_id?: number | null;
}

export interface CartItem {
    id: number;
    cart_id: number;
    product_id: number;
    size_id?: number | null;
    color_id?: number | null;
    quantity: number;
}

export interface CartWithItems extends Cart {
    items: CartItem[];
    coupon?: {
        id: number;
        code: string;
        name: string;
        description: string;
        discount_amount: number;
        discount_percentage: number;
        start_date: string;
        end_date: string;
        usage_limit: number;
        min_purchase_amount: number;
        max_discount_amount: number;
        is_active: boolean;
    }
}

async function adjustProductMetrics(
    productId: number,
    sizeId: number | null,
    colorId: number | null,
    quantityChange: number
): Promise<void> {
    let updateSoldQuery: string;
    let updateQtyQuery: string;
    const paramsSold = [quantityChange, productId];
    const paramsQty = [quantityChange, productId];

    if (sizeId) {
        updateSoldQuery = `
            UPDATE sizes
            SET sold = sold + $1
            WHERE id = $3 AND product_id = $2
        `;
        updateQtyQuery = `
            UPDATE sizes
            SET qty = qty - $1
            WHERE id = $3 AND product_id = $2
        `;
        paramsSold.push(sizeId);
        paramsQty.push(sizeId);
    } else if (colorId) {
        updateSoldQuery = `
            UPDATE colors
            SET sold = sold + $1
            WHERE id = $3 AND product_id = $2
        `;
        updateQtyQuery = `
            UPDATE colors
            SET qty = qty - $1
            WHERE id = $3 AND product_id = $2
        `;
        paramsSold.push(colorId);
        paramsQty.push(colorId);
    } else {
        updateSoldQuery = `
            UPDATE products
            SET sold = sold + $1
            WHERE id = $2
        `;
        updateQtyQuery = `
            UPDATE products
            SET qty = qty - $1
            WHERE id = $2
        `;
    }

    await query(updateSoldQuery, paramsSold);
    await query(updateQtyQuery, paramsQty);
}

export async function getAllCartsWithItems(): Promise<CartWithItems[]> {
    try {
        const cartResult = await query<Cart>('SELECT * FROM carts ORDER BY created_at DESC');
        const carts = cartResult.rows;
        const cartsWithItems: CartWithItems[] = [];

        for (const cart of carts) {
            const itemsResult = await query<CartItem>(
                'SELECT * FROM cart_items WHERE cart_id = $1',
                [cart.id]
            );

            let couponInfo = null;
            if (cart.coupon_id) {
                const couponResult = await query(`
                    SELECT id, code, name, description, discount_amount, discount_percentage,
                    start_date, end_date, usage_limit, min_purchase_amount, max_discount_amount, is_active
                    FROM coupons WHERE id = $1
                `, [cart.coupon_id]);
                if (couponResult.rows.length > 0) {
                    couponInfo = couponResult.rows[0];
                }
            }

            cartsWithItems.push({
                ...cart,
                items: itemsResult.rows,
                coupon: couponInfo
            });
        }

        return cartsWithItems;
    } catch (error) {
        console.error('Error fetching carts with items:', error);
        throw error;
    }
}

export async function updateCartStatus(cartId: number, newStatus: string): Promise<Cart | null> {
    try {
        await query('BEGIN');

        const oldStatusResult = await query<Cart>('SELECT status FROM carts WHERE id = $1', [cartId]);
        const oldStatus = oldStatusResult.rows[0]?.status;

        const result = await query<Cart>(
            'UPDATE carts SET status = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2 RETURNING *',
            [newStatus, cartId]
        );

        const updatedCart = result.rows[0];

        if (updatedCart) {
            const cartItemsResult = await query<CartItem>(
                'SELECT * FROM cart_items WHERE cart_id = $1',
                [cartId]
            );
            const cartItems = cartItemsResult.rows;

            for (const item of cartItems) {
                let quantityChange = 0;

                if (newStatus.toLowerCase() === 'rejected') {
                    quantityChange = -item.quantity;
                    await adjustProductMetrics(item.product_id, item.size_id, item.color_id, quantityChange);
                } else if (oldStatus.toLowerCase() === 'rejected') {
                    quantityChange = item.quantity;
                    await adjustProductMetrics(item.product_id, item.size_id, item.color_id, quantityChange);
                }
            }
        }

        await query('COMMIT');

        return updatedCart || null;
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating cart status:', error);
        throw error;
    }
}

export async function deleteCartById(cartId: number): Promise<boolean> {
    try {
        await query('BEGIN');

        await query('DELETE FROM cart_items WHERE cart_id = $1', [cartId]);
        const result = await query('DELETE FROM carts WHERE id = $1 RETURNING *', [cartId]);

        await query('COMMIT');

        return result.rows.length > 0;
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error deleting cart:', error);
        throw error;
    }
}

// Function to create a cart with items in a single transaction
export async function createCartWithItems(
    cartData: Partial<Cart>,
    items: Partial<CartItem>[]
): Promise<{ cart: Cart; items: CartItem[] }> {
    try {
        await query('BEGIN');

        // Insert into carts table
        const cartResult = await query<Cart>(
            `INSERT INTO carts (
        user_id, status, notes, full_name, governorate, city, address, closest_point, phone_number, type, coupon_id
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
      ) RETURNING *`,
            [
                cartData.user_id,
                cartData.status || 'Pending',
                cartData.notes,
                cartData.full_name,
                cartData.governorate,
                cartData.city,
                cartData.address,
                cartData.closest_point,
                cartData.phone_number,
                cartData.type,
                cartData.coupon_id,
            ]
        );

        const cart = cartResult.rows[0];
        if (!cart) {
            throw new Error('Failed to create cart');
        }

        // Insert cart items
        const cartItems: CartItem[] = [];
        const insertItemText = `INSERT INTO cart_items (
      cart_id, product_id, size_id, color_id, quantity
    ) VALUES ($1, $2, $3, $4, $5) RETURNING *`;

        for (const item of items) {
            const itemResult = await query<CartItem>(insertItemText, [
                cart.id,
                item.product_id,
                item.size_id,
                item.color_id,
                item.quantity,
            ]);
            cartItems.push(itemResult.rows[0]);
        }

        await query('COMMIT');
        return { cart, items: cartItems };
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating cart with items:', error);
        throw error;
    }
}

export async function getCartWithItemsByUserId(
    userId: number
): Promise<{ cart: Cart; items: CartItem[] } | null> {
    // Get the most recent cart for the user
    const cartResult = await query<Cart>(
        `SELECT * FROM carts WHERE user_id = $1 ORDER BY created_at DESC LIMIT 1`,
        [userId]
    );

    const cart = cartResult.rows[0];
    if (!cart) {
        return null;
    }

    // Get the cart items
    const itemsResult = await query<CartItem>(
        `SELECT * FROM cart_items WHERE cart_id = $1`,
        [cart.id]
    );

    return { cart, items: itemsResult.rows };
}
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);