SQL File

CREATE TABLE IF NOT EXISTS `trucking_companies` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `owner` VARCHAR(50) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `warehouse_id` INT(11) NOT NULL,
  `balance` BIGINT(20) NOT NULL DEFAULT 0,
  `level` INT(11) NOT NULL DEFAULT 1,
  `xp` INT(11) NOT NULL DEFAULT 0,
  `skills` LONGTEXT NOT NULL DEFAULT '[]',
  `stats` LONGTEXT NOT NULL DEFAULT '[]',
  PRIMARY KEY (`id`),
  KEY `owner` (`owner`),
  INDEX `idx_companies_owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_employees` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `identifier` VARCHAR(100) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `grade` VARCHAR(50) NOT NULL DEFAULT 'driver', -- 'driver', 'manager', 'co_owner'
  `pay_rate` INT NOT NULL DEFAULT 80, 
  `permissions` TEXT, 
  `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp` (`company_id`, `identifier`),
  INDEX `idx_emp_identifier` (`identifier`),
  CONSTRAINT `fk_employee_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_fleet` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `model` VARCHAR(50) NOT NULL,
  `plate` VARCHAR(15) NOT NULL,
  `engine_health` FLOAT NOT NULL DEFAULT 1000,
  `body_health` FLOAT NOT NULL DEFAULT 1000,
  `transmission_health` FLOAT NOT NULL DEFAULT 1000,
  `wheels_health` FLOAT NOT NULL DEFAULT 1000,
  `fuel` FLOAT NOT NULL DEFAULT 100,
  `mileage` INT(11) NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL DEFAULT 'idle',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  INDEX `idx_fleet_company` (`company_id`, `status`),
  CONSTRAINT `fk_fleet_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_drivers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `portrait` VARCHAR(50) NOT NULL,
  `skills` LONGTEXT NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'idle',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  INDEX `idx_drivers_company` (`company_id`, `status`),
  CONSTRAINT `fk_driver_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_active_missions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `truck_id` INT(11) NOT NULL,
  `driver_id` INT(11) NOT NULL,
  `job_id` INT(11) NOT NULL DEFAULT 1,
  `job_title` VARCHAR(100) NOT NULL,
  `base_reward` INT(11) NOT NULL,
  `base_xp` INT(11) NOT NULL,
  `fuel_cost` FLOAT NOT NULL,
  `end_time` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fk_mission_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_fines` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `driver_name` VARCHAR(50) NOT NULL,
  `truck_id` INT NOT NULL,
  `truck_plate` VARCHAR(20) NOT NULL,
  `violation_type` VARCHAR(120) NOT NULL, 
  `amount` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `due_date` INT NOT NULL,
  `status` VARCHAR(20) DEFAULT 'unpaid',
  CONSTRAINT `fk_fines_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE,
  INDEX `idx_fines_company_status` (`company_id`, `status`, `due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trucking_activities` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `job_name` VARCHAR(255) NOT NULL,
  `type` VARCHAR(100) NOT NULL,
  `amount` INT NOT NULL,
  `date` VARCHAR(50) NOT NULL DEFAULT '',
  `day_name` VARCHAR(10) NOT NULL DEFAULT '',
  `driver_name` VARCHAR(100) NOT NULL DEFAULT 'Unknown',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_company_date` (`company_id`, `date`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_activities_company` FOREIGN KEY (`company_id`) REFERENCES `trucking_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;