71 lines
2.9 KiB
PHP
71 lines
2.9 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../config.php';
|
|
|
|
try {
|
|
$pdo = db();
|
|
echo "Connected to database successfully.\n";
|
|
|
|
$sql = <<<SQL
|
|
CREATE TABLE IF NOT EXISTS `leave_types` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`company_id` INT NOT NULL,
|
|
`name` VARCHAR(100) NOT NULL,
|
|
`days_per_year` INT NOT NULL DEFAULT 0,
|
|
`is_accrued` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `leave_requests` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`employee_id` INT NOT NULL,
|
|
`leave_type_id` INT NOT NULL,
|
|
`start_date` DATE NOT NULL,
|
|
`end_date` DATE NOT NULL,
|
|
`reason` TEXT,
|
|
`status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
|
|
`reviewed_by` INT NULL, -- user_id of the admin/manager
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=INNODB;
|
|
|
|
-- Add a default leave type for existing companies
|
|
-- This is a bit of a hack for a migration, but useful for development
|
|
-- In a real app, this would be handled by an application setup process
|
|
|
|
SQL;
|
|
|
|
$pdo->exec($sql);
|
|
echo "Successfully created leave tables: leave_types, leave_requests.\n";
|
|
|
|
// Add some default leave types for any existing companies to make testing easier
|
|
$stmt = $pdo->query("SELECT id FROM companies");
|
|
$companies = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
$default_leave_types = [
|
|
['name' => 'Annual Leave', 'days' => 21],
|
|
['name' => 'Sick Leave', 'days' => 10],
|
|
['name' => 'Maternity Leave', 'days' => 90]
|
|
];
|
|
|
|
$insert_stmt = $pdo->prepare("INSERT INTO leave_types (company_id, name, days_per_year) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name=name"); // Using ON DUPLICATE to avoid errors on re-run
|
|
|
|
foreach ($companies as $company_id) {
|
|
foreach ($default_leave_types as $type) {
|
|
// Check if it exists first to be truly idempotent
|
|
$check_stmt = $pdo->prepare("SELECT id FROM leave_types WHERE company_id = ? AND name = ?");
|
|
$check_stmt->execute([$company_id, $type['name']]);
|
|
if (!$check_stmt->fetch()) {
|
|
$insert_stmt->execute([$company_id, $type['name'], $type['days']]);
|
|
}
|
|
}
|
|
}
|
|
echo "Added default leave types for existing companies.\n";
|
|
|
|
|
|
} catch (PDOException $e) {
|
|
die("Database migration failed: " . $e->getMessage() . "\n");
|
|
}
|