schema.sql

Type
sql
Size
26.53 KB
Modified
15 May
schema.sql 26.53 KB
-- WorkersPanel v5.1.0 - Full Database Schema
-- Shared application database schema
-- Generated: 2026-02-17

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'driver',
  `secondary_group` enum('office','driver','porter') DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `must_change_password` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ROLES
-- ============================================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PERMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ROLE_PERMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` int(10) UNSIGNED NOT NULL,
  `permission_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission` (`role_id`,`permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- VANS
-- ============================================================
CREATE TABLE IF NOT EXISTS `vans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plate_full` varchar(20) NOT NULL,
  `plate_short` varchar(10) DEFAULT NULL,
  `make` varchar(50) DEFAULT NULL,
  `model` varchar(50) DEFAULT NULL,
  `year` int(4) DEFAULT NULL,
  `color` varchar(30) DEFAULT NULL,
  `tag_color` varchar(16) DEFAULT '#ff8c1a',
  `notes` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- TRUTRAK VEHICLE MAPPING
-- ============================================================
CREATE TABLE IF NOT EXISTS `vehicle_trutrak_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(11) NOT NULL,
  `trutrak_device_id` varchar(32) NOT NULL,
  `linked_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_vehicle` (`vehicle_id`),
  UNIQUE KEY `uniq_device` (`trutrak_device_id`),
  KEY `idx_vehicle` (`vehicle_id`),
  CONSTRAINT `fk_vehicle_trutrak_map_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRUTRAK GEOFENCE META (category + icon)
-- ============================================================
CREATE TABLE IF NOT EXISTS `trutrak_geofence_meta` (
  `geofence_key` varchar(64) NOT NULL,
  `category` varchar(32) NOT NULL DEFAULT 'other',
  `icon` varchar(32) NOT NULL DEFAULT '📍',
  `label` varchar(255) DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`geofence_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CALENDAR CATEGORIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `calendar_categories` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `color` varchar(16) DEFAULT '#3788d8',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CALENDAR CATEGORY FIELDS (custom fields per category)
-- ============================================================
CREATE TABLE IF NOT EXISTS `calendar_category_fields` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(10) UNSIGNED NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_label` varchar(100) NOT NULL,
  `field_type` enum('text','textarea','number','select','user') NOT NULL DEFAULT 'text',
  `field_options` longtext DEFAULT NULL,
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `calendar_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CALENDAR EVENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `calendar_events` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `run_number` varchar(50) DEFAULT NULL,
  `van_id` int(11) DEFAULT NULL,
  `van_plate_temp` varchar(20) DEFAULT NULL,
  `driver_user_id` int(11) DEFAULT NULL,
  `driver_name_other` varchar(100) DEFAULT NULL,
  `porter_user_id` int(11) DEFAULT NULL,
  `porter_name_other` varchar(100) DEFAULT NULL,
  `office_user_id` int(11) DEFAULT NULL,
  `office_user_other` varchar(255) DEFAULT NULL,
  `is_run_event` tinyint(1) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `meeting_with` varchar(255) DEFAULT NULL,
  `meeting_by_user_id` int(11) DEFAULT NULL,
  `meeting_status` enum('pending','attended','no_show','cancelled') DEFAULT 'pending',
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `all_day` tinyint(1) NOT NULL DEFAULT 0,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `meta` longtext DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `updated_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_start_at` (`start_at`),
  KEY `idx_category` (`category_id`),
  KEY `idx_van` (`van_id`),
  KEY `idx_driver` (`driver_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ANNUAL LEAVE STAFF
-- ============================================================
CREATE TABLE IF NOT EXISTS `annual_leave_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ACTIVITY LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(10) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOGIN LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `login_logs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `role` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `failure_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- REMEMBER TOKENS
-- ============================================================
CREATE TABLE IF NOT EXISTS `remember_tokens` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `selector` varchar(64) NOT NULL,
  `hashed_validator` varchar(255) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `selector` (`selector`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PASSWORD RESET TOKENS
-- ============================================================
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DB CHANGE LOGS (audit trail)
-- ============================================================
CREATE TABLE IF NOT EXISTS `db_change_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `table_name` varchar(64) NOT NULL,
  `action` enum('INSERT','UPDATE','DELETE') NOT NULL,
  `row_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `old_data` longtext DEFAULT NULL,
  `new_data` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_table` (`table_name`),
  KEY `idx_user` (`user_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SYSTEM INFO
-- ============================================================
CREATE TABLE IF NOT EXISTS `system_info` (
  `key` varchar(50) NOT NULL,
  `value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SYSTEM MIGRATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `system_migrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `applied_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- DEFAULT DATA
-- ============================================================

-- Default Roles
INSERT IGNORE INTO `roles` (`name`, `display_name`, `description`, `is_system`) VALUES
('administrator', 'Administrator', 'Full system access', 1),
('director', 'Director', 'Management and reporting access', 1),
('manager', 'Manager', 'Operations management access', 1),
('driver', 'Driver', 'Driver schedule access', 1),
('porter', 'Porter', 'Porter schedule access', 1);

-- Default Permissions
INSERT IGNORE INTO `permissions` (`name`, `display_name`, `category`) VALUES
('dashboard.view',   'Open dashboard',        'Navigation'),
('calendar.view',    'View calendar',         'Calendar'),
('calendar.edit',    'Edit calendar events',  'Calendar'),
('calendar.create',  'Create calendar events','Calendar'),
('calendar.delete',  'Delete calendar events','Calendar'),
('calendar.categories.manage','Manage calendar setup','Calendar'),
('management.access','Open management area',  'Navigation'),
('staff.view',       'View staff',            'Staff'),
('staff.edit',       'Manage staff accounts', 'Staff'),
('vehicles.view',    'View vehicles',         'Vehicles'),
('vehicles.edit',    'Manage vehicles',      'Vehicles'),
('logs.view',        'View activity logs',   'System'),
('trutrak.view',     'View TruTrak Map','TruTrak'),
('trutrak.history',  'View TruTrak history', 'TruTrak'),
('trutrak.manage',   'Manage TruTrak Admin','TruTrak'),
('clock.use',        'Use shift clock',   'Operations'),
('clock.driver_start', 'Complete driver start checks', 'Operations'),
('clock.porter_start', 'Complete porter start checks', 'Operations'),
('exports.calendar', 'Export calendar data', 'Exports'),
('exports.clock',    'Export clocking data', 'Exports'),
('exports.rota',     'Export rota data',     'Exports'),
('exports.holidays', 'Export holiday data',  'Exports'),
('exports.incidents','Export incident data', 'Exports');

-- Assign all permissions to administrator
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p WHERE r.name = 'administrator';

-- Assign all permissions to director
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p WHERE r.name = 'director';


-- Default Calendar Categories (system defaults)
INSERT IGNORE INTO `calendar_categories` (`id`, `name`, `color`, `sort_order`) VALUES
(10, 'Jobs', '#696969', 1),
(20, 'Meetings', '#42e9ff', 2),
(40, 'Annual Leave', '#6041aa', 3),
(30, 'Incidents', '#eeff00', 4),
(50, 'Other', '#00ff00', 5);

ALTER TABLE `calendar_categories` AUTO_INCREMENT = 51;

-- System info defaults

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r
JOIN `permissions` p ON p.name IN (
  'dashboard.view','management.access',
  'calendar.view','calendar.create','calendar.edit',
  'vehicles.view','vehicles.edit',
  'trutrak.view','trutrak.history',
  'rota.view','rota.edit','clock.use','clock.view_team','clock.driver_start','clock.driver_checkout','clock.porter_start','clock.porter_checkout','clock.photo_upload',
  'holidays.view','holidays.request','holidays.manage',
  'incidents.view','incidents.create','incidents.manage',
  'contacts.view','contacts.manage',
  'alerts.view','alerts.send'
)
WHERE r.name = 'manager';

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r
JOIN `permissions` p ON p.name IN ('dashboard.view','rota.view','holidays.request','incidents.create','contacts.view','alerts.view','clock.use','clock.driver_start','clock.driver_checkout','clock.photo_upload')
WHERE r.name = 'driver';

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r
JOIN `permissions` p ON p.name IN ('dashboard.view','rota.view','holidays.request','incidents.create','contacts.view','alerts.view','clock.use','clock.porter_start','clock.porter_checkout')
WHERE r.name = 'porter';

INSERT IGNORE INTO `system_info` (`key`, `value`) VALUES
('version', '5.1.0'),
('company_name', 'Company Name'),
('calendar_default_category_id','10'),
('calendar_default_view','month'),
('logo_path', '/assets/images/logo.png'),
('favicon_path', ''),
('timezone', 'UTC'),
('date_format', 'd/m/Y'),
('time_format', 'H:i'),

('trutrak_base_url', 'https://ttapi.trutrakpro.co.uk'),
('trutrak_login', ''),
('trutrak_password', ''),
('trutrak_secret_key', ''),
('trutrak_auth_mode', 'U'),
('trutrak_xmltype', '0'),
('trutrak_token', ''),
('trutrak_token_expires', '0'),
('trutrak_dailytrail_last_ts', '0'),
('onedrive_tenant_id', ''),
('onedrive_client_id', ''),
('onedrive_client_secret', ''),
('onedrive_drive_id', ''),
('onedrive_base_folder', 'WorkersPanel');


-- ============================================================
-- FEATURE MODULE TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS `rota_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `shift_date` date NOT NULL,
  `staff_user_id` int(11) DEFAULT NULL,
  `staff_name` varchar(120) NOT NULL,
  `route_name` varchar(120) DEFAULT NULL,
  `van_label` varchar(80) DEFAULT NULL,
  `role_label` varchar(80) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_by_name` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_rota_code` (`code`),
  KEY `idx_rota_date` (`shift_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `holiday_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_code` varchar(30) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `username` varchar(120) NOT NULL,
  `role_name` varchar(80) DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_days` int(11) NOT NULL DEFAULT 1,
  `reason` text DEFAULT NULL,
  `priority` varchar(32) NOT NULL DEFAULT 'Normal',
  `status` varchar(32) NOT NULL DEFAULT 'Pending',
  `notes` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_by_name` varchar(120) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_holiday_request_code` (`request_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `incidents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_code` varchar(30) NOT NULL,
  `incident_date` date NOT NULL,
  `driver_user_id` int(11) DEFAULT NULL,
  `driver_name` varchar(120) NOT NULL,
  `porter_user_id` int(11) DEFAULT NULL,
  `porter_name` varchar(120) DEFAULT NULL,
  `van_label` varchar(80) DEFAULT NULL,
  `description` text NOT NULL,
  `damage_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` varchar(32) NOT NULL DEFAULT 'Open',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_by_name` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` datetime DEFAULT NULL,
  `chargeback_applied` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_incident_code` (`incident_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `incident_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `storage` varchar(32) NOT NULL DEFAULT 'onedrive',
  `file_name` varchar(255) DEFAULT NULL,
  `onedrive_item_id` varchar(255) DEFAULT NULL,
  `onedrive_web_url` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_incident_photo_incident` (`incident_id`),
  CONSTRAINT `fk_incident_photos_incident` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contact_code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `position` varchar(150) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `phone` varchar(80) DEFAULT NULL,
  `email` varchar(180) DEFAULT NULL,
  `type` varchar(64) NOT NULL DEFAULT 'Internal',
  `notes` text DEFAULT NULL,
  `added_by` int(11) DEFAULT NULL,
  `added_by_name` varchar(120) DEFAULT NULL,
  `added_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_contact_code` (`contact_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_code` varchar(30) NOT NULL,
  `sent_by` int(11) DEFAULT NULL,
  `sent_by_name` varchar(120) DEFAULT NULL,
  `sender_role` varchar(80) DEFAULT NULL,
  `sent_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `title` varchar(180) NOT NULL,
  `message` text NOT NULL,
  `target_type` varchar(32) NOT NULL DEFAULT 'all',
  `target_value` varchar(180) DEFAULT NULL,
  `priority` varchar(32) NOT NULL DEFAULT 'normal',
  `link_url` text DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_alert_code` (`alert_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alert_reads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `username` varchar(120) DEFAULT NULL,
  `read_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_alert_read` (`alert_id`,`username`),
  CONSTRAINT `fk_alert_reads_alert` FOREIGN KEY (`alert_id`) REFERENCES `alerts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `time_clock_entries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `username` varchar(120) NOT NULL,
  `display_name` varchar(150) DEFAULT NULL,
  `role_name` varchar(80) DEFAULT NULL,
  `van_label` varchar(120) DEFAULT NULL,
  `van_reg` varchar(120) DEFAULT NULL,
  `partner_name` varchar(160) DEFAULT NULL,
  `mileage_in` int(11) DEFAULT NULL,
  `mileage_out` int(11) DEFAULT NULL,
  `clock_in_at` datetime NOT NULL,
  `clock_out_at` datetime DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'clocked_in',
  `ip_address` varchar(45) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `start_meta_json` longtext DEFAULT NULL,
  `end_meta_json` longtext DEFAULT NULL,
  `photo_count` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clock_user` (`user_id`),
  KEY `idx_clock_in_at` (`clock_in_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `time_clock_photos` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `clock_entry_id` bigint(20) UNSIGNED NOT NULL,
  `phase` varchar(20) NOT NULL DEFAULT 'clock_out',
  `storage` varchar(32) NOT NULL DEFAULT 'onedrive',
  `file_name` varchar(255) NOT NULL,
  `onedrive_item_id` varchar(255) DEFAULT NULL,
  `onedrive_web_url` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clock_entry_phase` (`clock_entry_id`,`phase`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `permissions` (`name`, `display_name`, `category`) VALUES
('rota.view', 'View Rota', 'Operations'),
('rota.edit', 'Edit Rota', 'Operations'),
('holidays.view', 'View Holiday Requests', 'Operations'),
('holidays.request', 'Submit Holiday Requests', 'Operations'),
('holidays.manage', 'Manage Holiday Requests', 'Operations'),
('incidents.view', 'View Incidents', 'Operations'),
('incidents.create', 'Create Incidents', 'Operations'),
('incidents.manage', 'Manage Incidents', 'Operations'),
('contacts.view', 'View Contacts', 'Operations'),
('contacts.manage', 'Manage Contacts', 'Operations'),
('alerts.view', 'View Alerts', 'Operations'),
('alerts.send', 'Send Alerts', 'Operations'),
('exports.manage', 'Export Operational Data', 'System'),
('integrations.manage', 'Manage Integrations', 'System'),
('clock.use', 'Use shift clock', 'Operations'),
('clock.driver_start', 'Complete driver start checks', 'Operations'),
('clock.porter_start', 'Complete porter start checks', 'Operations'),
('clock.view_team', 'View team clock records', 'Operations'),
('clock.driver_checkout', 'Complete driver shift checks', 'Operations'),
('clock.porter_checkout', 'Complete porter shift checks', 'Operations'),
('clock.photo_upload', 'Upload shift clock photos', 'Operations'),
('exports.calendar', 'Export calendar data', 'Exports'),
('exports.clock', 'Export Clocking Data', 'Exports'),
('exports.rota', 'Export Rota Data', 'Exports'),
('exports.holidays', 'Export Holiday Data', 'Exports'),
('exports.incidents', 'Export Incident Data', 'Exports');

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p WHERE r.name IN ('administrator','director');

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r
JOIN `permissions` p ON p.name IN (
  'dashboard.view','management.access',
  'calendar.view','calendar.create','calendar.edit',
  'vehicles.view','vehicles.edit',
  'trutrak.view','trutrak.history',
  'rota.view','rota.edit','clock.use','clock.view_team','clock.driver_start','clock.driver_checkout','clock.porter_start','clock.porter_checkout','clock.photo_upload',
  'holidays.view','holidays.request','holidays.manage',
  'incidents.view','incidents.create','incidents.manage',
  'contacts.view','contacts.manage','alerts.view','alerts.send'
)
WHERE r.name = 'manager';