36 lines
1.6 KiB
SQL
36 lines
1.6 KiB
SQL
-- 1. เพิ่มคอลัมน์ใหม่ในตาราง jobs
|
|
ALTER TABLE `jobs`
|
|
ADD COLUMN `signature_path` VARCHAR(255) DEFAULT NULL AFTER `notes`,
|
|
ADD COLUMN `sla_minutes` INT DEFAULT 15 AFTER `priority`;
|
|
|
|
-- 2. สร้างตาราง user_locations (Live Tracking)
|
|
CREATE TABLE IF NOT EXISTS `user_locations` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`latitude` DECIMAL(10, 8) NOT NULL,
|
|
`longitude` DECIMAL(11, 8) NOT NULL,
|
|
`last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. สร้างตาราง notification_logs (LINE/Telegram Audit)
|
|
CREATE TABLE IF NOT EXISTS `notification_logs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`job_id` INT NOT NULL,
|
|
`channel` ENUM('LINE', 'Telegram') NOT NULL,
|
|
`message` TEXT NOT NULL,
|
|
`status` ENUM('success', 'failed') NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`job_id`) REFERENCES `jobs`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. สร้างตาราง equipment_logs (Asset Tracking History)
|
|
CREATE TABLE IF NOT EXISTS `equipment_logs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`equipment_id` INT NOT NULL,
|
|
`job_id` INT DEFAULT NULL,
|
|
`action` VARCHAR(100) NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`equipment_id`) REFERENCES `equipments`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|