const { Pool } = require('pg');
const pool = new Pool({
host: 'localhost',
port: 5432,
user: 'chrondb',
password: 'chrondb',
database: 'chrondb'
});
// OrderSystem class
class OrderSystem {
constructor() {
this.pool = pool;
}
async query(text, params) {
const client = await this.pool.connect();
try {
const result = await client.query(text, params);
return result.rows;
} finally {
client.release();
}
}
// Initialize the database with tables
async initialize() {
const tables = [
`CREATE TABLE IF NOT EXISTS customer (
id TEXT PRIMARY KEY,
name TEXT,
email TEXT,
address TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS product (
id TEXT PRIMARY KEY,
name TEXT,
price NUMERIC,
description TEXT,
stock INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS order_item (
id TEXT PRIMARY KEY,
order_id TEXT,
product_id TEXT,
quantity INTEGER,
price NUMERIC,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS customer_order (
id TEXT PRIMARY KEY,
customer_id TEXT,
total NUMERIC,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`
];
for (const table of tables) {
await this.query(table);
}
console.log('Database initialized');
}
// Customer methods
async createCustomer(id, data) {
const { name, email, address } = data;
const text = `
INSERT INTO customer (id, name, email, address)
VALUES ($1, $2, $3, $4)
RETURNING *
`;
const values = [id, name, email, address];
const rows = await this.query(text, values);
return rows[0];
}
async getCustomer(id) {
const text = 'SELECT * FROM customer WHERE id = $1';
const rows = await this.query(text, [id]);
return rows[0] || null;
}
async updateCustomer(id, updates) {
const fields = [];
const values = [];
let index = 1;
for (const [key, value] of Object.entries(updates)) {
fields.push(`${key} = $${index}`);
values.push(value);
index++;
}
values.push(id);
const text = `
UPDATE customer
SET ${fields.join(', ')}
WHERE id = $${index}
RETURNING *
`;
const rows = await this.query(text, values);
return rows[0] || null;
}
// Product methods
async createProduct(id, data) {
const { name, price, description, stock } = data;
const text = `
INSERT INTO product (id, name, price, description, stock)
VALUES ($1, $2, $3, $4, $5)
RETURNING *
`;
const values = [id, name, price, description, stock];
const rows = await this.query(text, values);
return rows[0];
}
async getProduct(id) {
const text = 'SELECT * FROM product WHERE id = $1';
const rows = await this.query(text, [id]);
return rows[0] || null;
}
async updateProductStock(id, adjustment) {
const text = `
UPDATE product
SET stock = stock + $1
WHERE id = $2
RETURNING *
`;
const values = [adjustment, id];
const rows = await this.query(text, values);
return rows[0] || null;
}
// Order methods
async createOrder(data) {
const { id, customer_id, items } = data;
// Start transaction
const client = await this.pool.connect();
try {
await client.query('BEGIN');
// Calculate total
let total = 0;
for (const item of items) {
const product = await this.getProduct(item.product_id);
if (!product) {
throw new Error(`Product ${item.product_id} not found`);
}
if (product.stock < item.quantity) {
throw new Error(`Insufficient stock for product ${item.product_id}`);
}
total += product.price * item.quantity;
// Update stock
await client.query(
'UPDATE product SET stock = stock - $1 WHERE id = $2',
[item.quantity, item.product_id]
);
// Create order item
const orderItemId = `item:${id}-${item.product_id}`;
await client.query(
`INSERT INTO order_item (id, order_id, product_id, quantity, price)
VALUES ($1, $2, $3, $4, $5)`,
[orderItemId, id, item.product_id, item.quantity, product.price]
);
}
// Create the order
const orderResult = await client.query(
`INSERT INTO customer_order (id, customer_id, total, status)
VALUES ($1, $2, $3, $4)
RETURNING *`,
[id, customer_id, total, 'pending']
);
await client.query('COMMIT');
return orderResult.rows[0];
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}
async getOrder(id) {
const orderText = 'SELECT * FROM customer_order WHERE id = $1';
const orderRows = await this.query(orderText, [id]);
if (orderRows.length === 0) {
return null;
}
const order = orderRows[0];
// Get order items
const itemsText = 'SELECT * FROM order_item WHERE order_id = $1';
const items = await this.query(itemsText, [id]);
return {
...order,
items
};
}
async updateOrderStatus(id, status) {
const text = `
UPDATE customer_order
SET status = $1, updated_at = CURRENT_TIMESTAMP
WHERE id = $2
RETURNING *
`;
const values = [status, id];
const rows = await this.query(text, values);
return rows[0] || null;
}
// Reporting methods
async getCustomerOrders(customerId) {
const text = `
SELECT co.*, c.name as customer_name, c.email
FROM customer_order co
JOIN customer c ON co.customer_id = c.id
WHERE co.customer_id = $1
ORDER BY co.created_at DESC
`;
return await this.query(text, [customerId]);
}
async getOrderHistory(orderId) {
const text = 'SELECT * FROM chrondb_history($1, $2)';
const rows = await this.query(text, ['customer_order', orderId]);
return rows.map(version => ({
timestamp: version.timestamp,
data: JSON.parse(version.data)
}));
}
async getOrderStatistics() {
const text = `
SELECT
status,
COUNT(*) as count,
SUM(total) as total_value,
MIN(total) as min_value,
MAX(total) as max_value,
AVG(total) as avg_value
FROM customer_order
GROUP BY status
`;
return await this.query(text);
}
}
// Usage example
async function runOrderSystemExample() {
const orderSystem = new OrderSystem();
try {
// Initialize database
await orderSystem.initialize();
// Create customers
await orderSystem.createCustomer('cust1', {
name: 'Alice Johnson',
email: '[email protected]',
address: '123 Main St, Anytown, CA'
});
await orderSystem.createCustomer('cust2', {
name: 'Bob Smith',
email: '[email protected]',
address: '456 Oak St, Somewhere, NY'
});
// Create products
await orderSystem.createProduct('prod1', {
name: 'Mechanical Keyboard',
price: 129.99,
description: 'Mechanical keyboard with RGB lighting',
stock: 50
});
await orderSystem.createProduct('prod2', {
name: 'Wireless Mouse',
price: 49.99,
description: 'Ergonomic wireless mouse',
stock: 100
});
await orderSystem.createProduct('prod3', {
name: 'Monitor Stand',
price: 79.99,
description: 'Adjustable monitor stand',
stock: 30
});
// Create an order
const order = await orderSystem.createOrder({
id: 'order1',
customer_id: 'cust1',
items: [
{ product_id: 'prod1', quantity: 1 },
{ product_id: 'prod2', quantity: 2 }
]
});
console.log('Created order:', order);
// Get order details
const orderDetails = await orderSystem.getOrder('order1');
console.log('Order details:', orderDetails);
// Update order status
await orderSystem.updateOrderStatus('order1', 'shipped');
console.log('Updated order status');
// Create another order
await orderSystem.createOrder({
id: 'order2',
customer_id: 'cust2',
items: [
{ product_id: 'prod3', quantity: 1 },
{ product_id: 'prod1', quantity: 1 }
]
});
// Get order history
const history = await orderSystem.getOrderHistory('order1');
console.log('Order history:', history);
// Get customer orders
const customerOrders = await orderSystem.getCustomerOrders('cust1');
console.log('Customer orders:', customerOrders);
// Get order statistics
const statistics = await orderSystem.getOrderStatistics();
console.log('Order statistics:', statistics);
} catch (err) {
console.error('Error:', err);
} finally {
await pool.end();
}
}
// Run the example
runOrderSystemExample();
