57 lines
2.2 KiB
PHP
57 lines
2.2 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../config.php';
|
|
|
|
try {
|
|
$pdo = db();
|
|
echo "Connected to database successfully.\n";
|
|
|
|
$sql = <<<SQL
|
|
CREATE TABLE IF NOT EXISTS `companies` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`company_id` INT NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
|
`password` VARCHAR(255) NOT NULL,
|
|
`role` ENUM('super_admin', 'admin', 'employee', 'manager', 'payroll_clerk', 'accountant', 'auditor') NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `employees` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`company_id` INT NOT NULL,
|
|
`first_name` VARCHAR(100) NOT NULL,
|
|
`last_name` VARCHAR(100) NOT NULL,
|
|
`date_of_birth` DATE,
|
|
`hire_date` DATE,
|
|
`position` VARCHAR(255),
|
|
`basic_salary` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `settings` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`company_id` INT NOT NULL,
|
|
`setting_key` VARCHAR(255) NOT NULL,
|
|
`setting_value` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY `company_setting` (`company_id`, `setting_key`),
|
|
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
SQL;
|
|
|
|
$pdo->exec($sql);
|
|
echo "Successfully created initial tables: companies, users, employees, settings.\n";
|
|
|
|
} catch (PDOException $e) {
|
|
die("Database migration failed: " . $e->getMessage() . "\n");
|
|
}
|