Spaces:
Running
Running
File size: 6,747 Bytes
9eab1a6 8f23197 9eab1a6 8f23197 9eab1a6 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | const pool = require('./config');
const createTables = async () => {
const client = await pool.connect();
try {
console.log('🚀 Starting database migration...');
await client.query('BEGIN');
// 1. Create parties table
await client.query(`
CREATE TABLE IF NOT EXISTS parties (
id VARCHAR(50) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
phone VARCHAR(20),
city VARCHAR(100),
party_type VARCHAR(20) NOT NULL CHECK (party_type IN ('awaak', 'jawaak', 'both')),
current_balance DECIMAL(12, 2) DEFAULT 0,
past_due DECIMAL(12, 2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: parties');
await client.query(`
ALTER TABLE parties
ADD COLUMN IF NOT EXISTS past_due DECIMAL(12, 2) DEFAULT 0
`);
// 2. Create mirchi_types table
await client.query(`
CREATE TABLE IF NOT EXISTS mirchi_types (
id VARCHAR(50) PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
current_rate DECIMAL(10, 2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: mirchi_types');
// 3. Create lots table
await client.query(`
CREATE TABLE IF NOT EXISTS lots (
id VARCHAR(50) PRIMARY KEY,
lot_number VARCHAR(100) NOT NULL UNIQUE,
mirchi_type_id VARCHAR(50) NOT NULL REFERENCES mirchi_types(id) ON DELETE CASCADE,
mirchi_name VARCHAR(100) NOT NULL,
total_quantity DECIMAL(10, 2) NOT NULL,
remaining_quantity DECIMAL(10, 2) NOT NULL,
purchase_date DATE NOT NULL,
status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'sold_out')),
avg_rate DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: lots');
// 4. Create transactions table
await client.query(`
CREATE TABLE IF NOT EXISTS transactions (
id VARCHAR(50) PRIMARY KEY,
bill_number VARCHAR(100) NOT NULL UNIQUE,
bill_date DATE NOT NULL,
bill_type VARCHAR(20) NOT NULL CHECK (bill_type IN ('awaak', 'jawaak')),
is_return BOOLEAN DEFAULT FALSE,
party_id VARCHAR(50) NOT NULL REFERENCES parties(id) ON DELETE CASCADE,
party_name VARCHAR(255),
gross_weight_total DECIMAL(10, 2) DEFAULT 0,
net_weight_total DECIMAL(10, 2) DEFAULT 0,
subtotal DECIMAL(12, 2) DEFAULT 0,
total_expenses DECIMAL(12, 2) DEFAULT 0,
total_amount DECIMAL(12, 2) NOT NULL,
paid_amount DECIMAL(12, 2) DEFAULT 0,
balance_amount DECIMAL(12, 2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: transactions');
// 5. Create transaction_items table
await client.query(`
CREATE TABLE IF NOT EXISTS transaction_items (
id VARCHAR(50) PRIMARY KEY,
transaction_id VARCHAR(50) NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
mirchi_type_id VARCHAR(50) NOT NULL REFERENCES mirchi_types(id),
mirchi_name VARCHAR(100),
quality VARCHAR(50),
lot_id VARCHAR(50) REFERENCES lots(id),
poti_weights TEXT,
gross_weight DECIMAL(10, 2) NOT NULL,
poti_count INTEGER NOT NULL,
total_potya DECIMAL(10, 2) DEFAULT 0,
net_weight DECIMAL(10, 2) NOT NULL,
rate_per_kg DECIMAL(10, 2) NOT NULL,
item_total DECIMAL(12, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: transaction_items');
// 6. Create expenses table
await client.query(`
CREATE TABLE IF NOT EXISTS expenses (
id SERIAL PRIMARY KEY,
transaction_id VARCHAR(50) NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
cess_percent DECIMAL(5, 2) DEFAULT 0,
cess_amount DECIMAL(10, 2) DEFAULT 0,
adat_percent DECIMAL(5, 2) DEFAULT 0,
adat_amount DECIMAL(10, 2) DEFAULT 0,
poti_rate DECIMAL(10, 2) DEFAULT 0,
poti_amount DECIMAL(10, 2) DEFAULT 0,
hamali_per_poti DECIMAL(10, 2) DEFAULT 0,
hamali_amount DECIMAL(10, 2) DEFAULT 0,
packaging_hamali_per_poti DECIMAL(10, 2) DEFAULT 0,
packaging_hamali_amount DECIMAL(10, 2) DEFAULT 0,
gaadi_bharni DECIMAL(10, 2) DEFAULT 0,
other_expenses DECIMAL(10, 2) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
// Ensure column exists on existing databases as well
await client.query(`
ALTER TABLE expenses
ADD COLUMN IF NOT EXISTS other_expenses DECIMAL(10, 2) DEFAULT 0
`);
console.log('✅ Created table: expenses');
// 7. Create payments table
await client.query(`
CREATE TABLE IF NOT EXISTS payments (
id SERIAL PRIMARY KEY,
transaction_id VARCHAR(50) NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
mode VARCHAR(20) NOT NULL CHECK (mode IN ('cash', 'online', 'cheque', 'due')),
amount DECIMAL(12, 2) NOT NULL,
reference VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ Created table: payments');
// Create indexes for better performance
await client.query(`
CREATE INDEX IF NOT EXISTS idx_transactions_party ON transactions(party_id);
CREATE INDEX IF NOT EXISTS idx_transactions_date ON transactions(bill_date);
CREATE INDEX IF NOT EXISTS idx_transaction_items_tx ON transaction_items(transaction_id);
CREATE INDEX IF NOT EXISTS idx_lots_mirchi ON lots(mirchi_type_id);
CREATE INDEX IF NOT EXISTS idx_lots_status ON lots(status);
`);
console.log('✅ Created indexes');
await client.query('COMMIT');
console.log('✅ Migration completed successfully!');
} catch (error) {
await client.query('ROLLBACK');
console.error('❌ Migration failed:', error);
throw error;
} finally {
client.release();
await pool.end();
}
};
createTables().catch(err => {
console.error('Fatal error:', err);
process.exit(1);
});
|