120 lines
4.0 KiB
PHP
120 lines
4.0 KiB
PHP
<?php
|
|
// db/setup.php
|
|
require_once 'config.php';
|
|
|
|
try {
|
|
// 1. Connect to MySQL without specifying a database
|
|
$dsn_nodb = "mysql:host=" . DB_HOST;
|
|
$pdo_nodb = new PDO($dsn_nodb, DB_USER, DB_PASS);
|
|
$pdo_nodb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
// 2. Create the database if it doesn't exist
|
|
$pdo_nodb->exec("CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "`");
|
|
|
|
// 3. Now connect to the newly created database
|
|
$dsn_db = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME;
|
|
$pdo = new PDO($dsn_db, DB_USER, DB_PASS);
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
// 4. Create the table
|
|
$sql = "CREATE TABLE IF NOT EXISTS team_members (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)";
|
|
|
|
$pdo->exec($sql);
|
|
|
|
$sql_expenses = "CREATE TABLE IF NOT EXISTS expenses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
expense_date DATE NOT NULL,
|
|
description VARCHAR(255) NOT NULL,
|
|
amount DECIMAL(10, 2) NOT NULL,
|
|
category VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)";
|
|
|
|
$pdo->exec($sql_expenses);
|
|
|
|
$sql_items = "CREATE TABLE IF NOT EXISTS items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
price DECIMAL(10, 2) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)";
|
|
|
|
$pdo->exec($sql_items);
|
|
|
|
$sql_tickets = "CREATE TABLE IF NOT EXISTS tickets (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status ENUM('Open', 'In Progress', 'Closed') NOT NULL DEFAULT 'Open',
|
|
priority ENUM('Low', 'Medium', 'High') NOT NULL DEFAULT 'Medium',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)";
|
|
|
|
$pdo->exec($sql_tickets);
|
|
|
|
$sql_clients = "CREATE TABLE IF NOT EXISTS clients (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
website VARCHAR(255),
|
|
status ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)";
|
|
|
|
$pdo->exec($sql_clients);
|
|
|
|
$sql_contacts = "CREATE TABLE IF NOT EXISTS contacts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
client_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
phone VARCHAR(50),
|
|
role VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
|
|
)";
|
|
|
|
$pdo->exec($sql_contacts);
|
|
|
|
$sql_projects = "CREATE TABLE IF NOT EXISTS projects (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
client_id INT,
|
|
status ENUM('Not Started', 'In Progress', 'Completed') NOT NULL DEFAULT 'Not Started',
|
|
start_date DATE,
|
|
end_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
|
|
)";
|
|
|
|
$pdo->exec($sql_projects);
|
|
|
|
// Check if there are any users in the team_members table
|
|
$stmt = $pdo->query("SELECT COUNT(*) FROM team_members");
|
|
$user_count = $stmt->fetchColumn();
|
|
|
|
if ($user_count == 0) {
|
|
// Insert a default admin user if no users exist
|
|
$admin_email = 'admin@example.com';
|
|
$admin_password = 'password123';
|
|
$hashed_password = password_hash($admin_password, PASSWORD_DEFAULT);
|
|
|
|
$insert_stmt = $pdo->prepare("INSERT INTO team_members (name, email, password, role) VALUES (?, ?, ?, ?)");
|
|
$insert_stmt->execute(['Admin', $admin_email, $hashed_password, 'Admin']);
|
|
echo "Default admin user created.\n";
|
|
}
|
|
|
|
echo "Database and table setup completed successfully.";
|
|
|
|
} catch (PDOException $e) {
|
|
die("DB setup failed: " . $e->getMessage());
|
|
}
|
|
?>
|