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;