192 lines
8.1 KiB
SQL
192 lines
8.1 KiB
SQL
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_work_orders` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`work_order_ref` VARCHAR(20) NOT NULL,
|
||
|
|
`customer_contact_id` INT DEFAULT NULL,
|
||
|
|
`customer_name` VARCHAR(255) NOT NULL,
|
||
|
|
`customer_phone` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`service_address` VARCHAR(500) NOT NULL,
|
||
|
|
`service_lat` DECIMAL(10,7) DEFAULT NULL,
|
||
|
|
`service_lng` DECIMAL(10,7) DEFAULT NULL,
|
||
|
|
`category` ENUM('plumbing','electrical','hvac','appliance','general','emergency','inspection','installation','other') NOT NULL DEFAULT 'general',
|
||
|
|
`priority` ENUM('emergency','high','medium','low','scheduled') NOT NULL DEFAULT 'medium',
|
||
|
|
`status` ENUM('requested','scheduled','dispatched','en_route','in_progress','completed','invoiced','cancelled','on_hold') NOT NULL DEFAULT 'requested',
|
||
|
|
`title` VARCHAR(255) NOT NULL,
|
||
|
|
`description` TEXT,
|
||
|
|
`technician_id` INT UNSIGNED DEFAULT NULL,
|
||
|
|
`pm_agreement_id` INT UNSIGNED DEFAULT NULL,
|
||
|
|
`scheduled_date` DATE DEFAULT NULL,
|
||
|
|
`labor_hours` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`labor_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`parts_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`payment_status` ENUM('pending','partial','paid','void') NOT NULL DEFAULT 'pending',
|
||
|
|
`customer_signature` TEXT,
|
||
|
|
`photos_before` JSON DEFAULT NULL,
|
||
|
|
`photos_after` JSON DEFAULT NULL,
|
||
|
|
`notes` TEXT,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `idx_ref` (`work_order_ref`),
|
||
|
|
KEY `idx_customer` (`customer_contact_id`),
|
||
|
|
KEY `idx_technician` (`technician_id`),
|
||
|
|
KEY `idx_status` (`status`),
|
||
|
|
KEY `idx_category` (`category`),
|
||
|
|
KEY `idx_priority` (`priority`),
|
||
|
|
KEY `idx_scheduled` (`scheduled_date`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_equipment` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`customer_contact_id` INT DEFAULT NULL,
|
||
|
|
`service_address` VARCHAR(500) NOT NULL DEFAULT '',
|
||
|
|
`equipment_type` ENUM('hvac','furnace','ac','water_heater','boiler','plumbing','electrical_panel','generator','appliance','other') NOT NULL DEFAULT 'other',
|
||
|
|
`brand` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`model` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`serial_number` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`install_date` DATE DEFAULT NULL,
|
||
|
|
`warranty_expiry` DATE DEFAULT NULL,
|
||
|
|
`condition_rating` ENUM('excellent','good','fair','poor','replace') NOT NULL DEFAULT 'good',
|
||
|
|
`last_service_date` DATE DEFAULT NULL,
|
||
|
|
`next_service_date` DATE DEFAULT NULL,
|
||
|
|
`notes` TEXT,
|
||
|
|
`published` TINYINT NOT NULL DEFAULT 1,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_customer` (`customer_contact_id`),
|
||
|
|
KEY `idx_type` (`equipment_type`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_equipment_history` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`equipment_id` INT UNSIGNED NOT NULL,
|
||
|
|
`work_order_id` INT UNSIGNED DEFAULT NULL,
|
||
|
|
`service_type` ENUM('installation','repair','maintenance','inspection','replacement','warranty') NOT NULL DEFAULT 'maintenance',
|
||
|
|
`description` TEXT,
|
||
|
|
`service_date` DATE NOT NULL,
|
||
|
|
`cost` DECIMAL(10,2) DEFAULT NULL,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_equipment` (`equipment_id`),
|
||
|
|
KEY `idx_work_order` (`work_order_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_technicians` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`contact_id` INT DEFAULT NULL,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`email` VARCHAR(255) NOT NULL DEFAULT '',
|
||
|
|
`phone` VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
`specialties` JSON DEFAULT NULL,
|
||
|
|
`certifications` VARCHAR(500) NOT NULL DEFAULT '',
|
||
|
|
`license_number` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`hourly_rate` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`status` ENUM('available','on_job','off_duty','vacation','inactive') NOT NULL DEFAULT 'available',
|
||
|
|
`current_lat` DECIMAL(10,7) DEFAULT NULL,
|
||
|
|
`current_lng` DECIMAL(10,7) DEFAULT NULL,
|
||
|
|
`rating` DECIMAL(3,2) NOT NULL DEFAULT 5.00,
|
||
|
|
`total_jobs` INT UNSIGNED NOT NULL DEFAULT 0,
|
||
|
|
`photo` VARCHAR(500) NOT NULL DEFAULT '',
|
||
|
|
`published` TINYINT NOT NULL DEFAULT 1,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_contact` (`contact_id`),
|
||
|
|
KEY `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_parts` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`part_number` VARCHAR(50) NOT NULL,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`category` VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
`unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`retail_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`warehouse_stock` INT NOT NULL DEFAULT 0,
|
||
|
|
`minimum_stock` INT NOT NULL DEFAULT 0,
|
||
|
|
`supplier` VARCHAR(255) NOT NULL DEFAULT '',
|
||
|
|
`published` TINYINT NOT NULL DEFAULT 1,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `idx_part_number` (`part_number`),
|
||
|
|
KEY `idx_category` (`category`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_truck_inventory` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`technician_id` INT UNSIGNED NOT NULL,
|
||
|
|
`part_id` INT UNSIGNED NOT NULL,
|
||
|
|
`quantity` INT NOT NULL DEFAULT 0,
|
||
|
|
`min_quantity` INT NOT NULL DEFAULT 0,
|
||
|
|
`last_restocked` DATE DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `idx_tech_part` (`technician_id`, `part_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_work_order_parts` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`work_order_id` INT UNSIGNED NOT NULL,
|
||
|
|
`part_id` INT UNSIGNED NOT NULL,
|
||
|
|
`quantity` INT UNSIGNED NOT NULL DEFAULT 1,
|
||
|
|
`unit_price` DECIMAL(10,2) NOT NULL,
|
||
|
|
`line_total` DECIMAL(10,2) NOT NULL,
|
||
|
|
`source` ENUM('warehouse','truck','purchased','customer_supplied') NOT NULL DEFAULT 'truck',
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_work_order` (`work_order_id`),
|
||
|
|
KEY `idx_part` (`part_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_checklists` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`category` ENUM('plumbing','electrical','hvac','appliance','general','safety','inspection') NOT NULL DEFAULT 'general',
|
||
|
|
`items` JSON NOT NULL,
|
||
|
|
`published` TINYINT NOT NULL DEFAULT 1,
|
||
|
|
`ordering` INT NOT NULL DEFAULT 0,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_category` (`category`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_pm_agreements` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`agreement_ref` VARCHAR(20) NOT NULL,
|
||
|
|
`customer_contact_id` INT NOT NULL,
|
||
|
|
`customer_name` VARCHAR(255) NOT NULL,
|
||
|
|
`service_address` VARCHAR(500) NOT NULL,
|
||
|
|
`plan_name` VARCHAR(255) NOT NULL,
|
||
|
|
`status` ENUM('active','expired','cancelled','pending_renewal') NOT NULL DEFAULT 'active',
|
||
|
|
`start_date` DATE NOT NULL,
|
||
|
|
`end_date` DATE DEFAULT NULL,
|
||
|
|
`visit_frequency` ENUM('monthly','quarterly','semi_annual','annual') NOT NULL DEFAULT 'semi_annual',
|
||
|
|
`visits_per_year` TINYINT UNSIGNED NOT NULL DEFAULT 2,
|
||
|
|
`visits_completed` TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
||
|
|
`annual_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`auto_renew` TINYINT NOT NULL DEFAULT 0,
|
||
|
|
`notes` TEXT,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `idx_ref` (`agreement_ref`),
|
||
|
|
KEY `idx_customer` (`customer_contact_id`),
|
||
|
|
KEY `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuitefield_dispatches` (
|
||
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
|
|
`work_order_id` INT UNSIGNED NOT NULL,
|
||
|
|
`technician_id` INT UNSIGNED NOT NULL,
|
||
|
|
`status` ENUM('offered','accepted','rejected','en_route','arrived','completed','cancelled') NOT NULL DEFAULT 'offered',
|
||
|
|
`offered_at` DATETIME NOT NULL,
|
||
|
|
`responded_at` DATETIME DEFAULT NULL,
|
||
|
|
`arrived_at` DATETIME DEFAULT NULL,
|
||
|
|
`distance_km` DECIMAL(10,2) DEFAULT NULL,
|
||
|
|
`eta_minutes` DECIMAL(10,2) DEFAULT NULL,
|
||
|
|
`attempt_number` TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
||
|
|
`notes` TEXT,
|
||
|
|
`created` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `idx_work_order` (`work_order_id`),
|
||
|
|
KEY `idx_technician` (`technician_id`),
|
||
|
|
KEY `idx_status` (`status`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|