77 lines
2.8 KiB
PHP
77 lines
2.8 KiB
PHP
<?php
|
|
// This script sets up the database tables required for the application.
|
|
// You can run this file from your browser or the command line to initialize the database.
|
|
|
|
require_once 'db/config.php';
|
|
|
|
try {
|
|
$pdo = db();
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
$sql = "
|
|
CREATE TABLE IF NOT EXISTS equipment (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS operators (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS locations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
address TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS fuel_receipts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
receipt_date DATE NOT NULL,
|
|
quantity DECIMAL(10, 2) NOT NULL,
|
|
supplier VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS fuel_issues (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
issue_date DATE NOT NULL,
|
|
equipment_id INT NOT NULL,
|
|
operator_id INT NOT NULL,
|
|
location_id INT NOT NULL,
|
|
quantity DECIMAL(10, 2) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE RESTRICT
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
";
|
|
|
|
$pdo->exec($sql);
|
|
|
|
echo "<div style='font-family: sans-serif; padding: 20px; border: 1px solid #d4edda; background-color: #f5fff7; color: #155724;'>";
|
|
echo "<strong>Success!</strong> Database tables created successfully.";
|
|
echo "<br><br><a href='/'>Go to Dashboard</a>";
|
|
echo "</div>";
|
|
|
|
} catch (PDOException $e) {
|
|
echo "<div style='font-family: sans-serif; padding: 20px; border: 1px solid #f5c6cb; background-color: #fff6f6; color: #721c24;'>";
|
|
die("<strong>Error:</strong> Could not create tables. " . $e->getMessage());
|
|
echo "</div>";
|
|
}
|