47 lines
2.1 KiB
PHP
47 lines
2.1 KiB
PHP
<?php
|
|
function setup_database($pdo) {
|
|
try {
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('Admin', 'Sales Rep', 'Dispatch') NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
)");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS orders (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
order_number VARCHAR(255) NOT NULL UNIQUE,
|
|
order_date DATE NOT NULL,
|
|
order_text TEXT NOT NULL,
|
|
status ENUM('Pending', 'Query', 'Query Replied', 'Shipped', 'Cancelled') NOT NULL DEFAULT 'Pending',
|
|
query_text TEXT DEFAULT NULL,
|
|
reply_text TEXT DEFAULT NULL,
|
|
sales_rep_id INT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (sales_rep_id) REFERENCES users(id)
|
|
)");
|
|
$pdo->exec("ALTER TABLE orders ADD COLUMN IF NOT EXISTS query_text TEXT DEFAULT NULL;");
|
|
$pdo->exec("ALTER TABLE orders ADD COLUMN IF NOT EXISTS reply_text TEXT DEFAULT NULL;");
|
|
|
|
// Add a default admin user if one doesn't exist
|
|
$stmt = $pdo->query("SELECT COUNT(*) FROM users WHERE role = 'Admin'");
|
|
if ($stmt->fetchColumn() == 0) {
|
|
$admin_email = 'admin@example.com';
|
|
$admin_name = 'Admin';
|
|
// In a real app, use a more secure password policy
|
|
$admin_password = password_hash('password', PASSWORD_DEFAULT);
|
|
$admin_role = 'Admin';
|
|
|
|
$insert_stmt = $pdo->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)");
|
|
$insert_stmt->execute([$admin_name, $admin_email, $admin_password, $admin_role]);
|
|
}
|
|
} catch (PDOException $e) {
|
|
// In a real app, you would log this error.
|
|
die("Database setup failed: " . $e->getMessage());
|
|
}
|
|
}
|