# Hickey Plant Hire — Database Schema Reference

> Database: `hickeyplant` | Generated: 2026-05-22

---

## contact_list

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_contact_list | int(11) | NO | NULL | auto_increment |
| contact_name | varchar(50) | YES | NULL |  |
| contact_surname | varchar(50) | YES | NULL |  |
| customer_acc | varchar(50) | YES | NULL |  |
| customer_acc_id | varchar(50) | YES | NULL |  |
| contact_phone_number | varchar(50) | YES | NULL |  |
| contact_email | varchar(250) | YES | NULL |  |

**Primary key:** id_contact_list

---

## customers

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_customer | int(11) | NO | NULL | auto_increment |
| customer_acc | varchar(50) | YES | NULL |  |
| customer_name | varchar(50) | YES | NULL |  |
| customer_accounts_contact | varchar(50) | YES | NULL |  |
| customer_accounts_contact_no | varchar(50) | YES | NULL |  |
| customer_accounts_email | varchar(250) | YES | NULL |  |
| customer_email_invoices | varchar(50) | YES | NULL |  |
| customer_notes | varchar(250) | YES | NULL |  |
| customer_requirePO | varchar(50) | YES | NULL |  |
| customer_plant_insurance | varchar(50) | YES | NULL |  |
| customer_plant_ins_expiry | date | YES | NULL |  |
| customer_credit_check_rate | varchar(50) | YES | NULL |  |
| customer_plant_ins_limit | varchar(50) | YES | NULL |  |
| customer_vat_no | varchar(50) | YES | NULL |  |
| customer_comp_no | varchar(50) | YES | NULL |  |
| customer_creditcheck_date | date | YES | NULL |  |
| customer_creditcheck_pf | varchar(50) | YES | NULL |  |
| customer_active | varchar(50) | YES | NULL |  |

**Primary key:** id_customer

---

## customer_review_history

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_review | int(11) | NO | NULL | auto_increment |
| id_customer | int(11) | NO | NULL |  |
| review_entered_at | datetime | NO | current_timestamp() |  |
| review_entered_by | varchar(100) | YES | NULL |  |
| customer_plant_insurance | varchar(255) | YES | NULL |  |
| customer_plant_ins_expiry | date | YES | NULL |  |
| customer_plant_ins_limit | decimal(15,2) | YES | NULL |  |
| customer_credit_check_rate | varchar(100) | YES | NULL |  |
| customer_creditcheck_date | date | YES | NULL |  |
| customer_creditcheck_pf | varchar(255) | YES | NULL |  |
| review_notes | text | YES | NULL |  |

**Primary key:** id_review
**Indexes:**
-  `idx_customer_review` (id_customer,review_entered_at)
**Foreign keys:**
- `id_customer` → `customers.id_customer`

---

## customer_statements

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_statement | int(11) | NO | NULL | auto_increment |
| id_batch | int(11) | NO | NULL |  |
| customer_account | varchar(100) | YES | NULL |  |
| filename | varchar(255) | NO | NULL |  |
| page_count | int(11) | YES | 1 |  |
| file_path | varchar(500) | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** id_statement
**Indexes:**
-  `created_at` (created_at)
-  `customer_account` (customer_account)
-  `id_batch` (id_batch)
**Foreign keys:**
- `id_batch` → `customer_statement_batches.id_batch`

---

## customer_statement_batches

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_batch | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| uploaded_by | varchar(255) | YES | NULL |  |
| batch_status | enum('processing','completed','error') | YES | 'processing' |  |
| statement_count | int(11) | YES | 0 |  |
| page_count | int(11) | YES | 0 |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** id_batch
**Indexes:**
-  `batch_status` (batch_status)
-  `created_at` (created_at)

---

## daily_runs

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| run_id | int(11) | NO | NULL | auto_increment |
| run_date | date | NO | NULL |  |
| lorry_id | int(11) | NO | NULL |  |
| user_id | int(11) | NO | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** run_id

---

## daily_run_pods

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| run_pod_id | int(11) | NO | NULL | auto_increment |
| run_id | int(11) | NO | NULL |  |
| pod_id | int(11) | NO | NULL |  |
| status | enum('pending','signed','sent') | YES | 'pending' |  |
| signature_image | longblob | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** run_pod_id
**Indexes:**
-  `pod_id` (pod_id)
-  `run_id` (run_id)

---

## debtors

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_debtor | int(11) | NO | NULL | auto_increment |
| customer_account | varchar(100) | NO | NULL |  |
| customer_name | varchar(255) | NO | NULL |  |
| current_balance | decimal(12,2) | YES | 0.00 |  |
| current_0_30 | decimal(12,2) | YES | 0.00 |  |
| current_31_60 | decimal(12,2) | YES | 0.00 |  |
| current_61_90 | decimal(12,2) | YES | 0.00 |  |
| current_90_plus | decimal(12,2) | YES | 0.00 |  |
| debtor_status | enum('Good Standing','Overdue 30+ days','Overdue 60+ days','Overdue 90+ days','Older than 90','Passed to Top Service','Resolved') | YES | 'Good Standing' |  |
| invoice_date | date | YES | NULL |  |
| days_overdue | int(11) | YES | 0 |  |
| total_promised | decimal(12,2) | YES | 0.00 |  |
| total_paid | decimal(12,2) | YES | 0.00 |  |
| last_contact_date | datetime | YES | NULL |  |
| next_review_date | date | YES | NULL |  |
| assigned_to | varchar(100) | YES | NULL |  |
| notes | longtext | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |
| updated_at | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_debtor
**Indexes:**
-  `assigned_to` (assigned_to)
- UNIQUE `customer_account` (customer_account)
-  `customer_account_2` (customer_account)
-  `days_overdue` (days_overdue)
-  `debtor_status` (debtor_status)
-  `idx_aging_breakdown` (current_0_30,current_31_60,current_61_90,current_90_plus)
-  `next_review_date` (next_review_date)

---

## debtor_actions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_action | int(11) | NO | NULL | auto_increment |
| id_debtor | int(11) | NO | NULL |  |
| action_type | enum('Phone call','Email sent','Promise made','Payment received','Letter sent','Legal action','Other') | NO | NULL |  |
| action_date | datetime | YES | current_timestamp() |  |
| amount | decimal(12,2) | YES | NULL |  |
| promised_date | date | YES | NULL |  |
| action_notes | longtext | YES | NULL |  |
| taken_by | varchar(100) | YES | NULL |  |
| follow_up_date | date | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** id_action
**Indexes:**
-  `action_date` (action_date)
-  `action_type` (action_type)
-  `follow_up_date` (follow_up_date)
-  `id_debtor` (id_debtor)
**Foreign keys:**
- `id_debtor` → `debtors.id_debtor`

---

## debtor_assignments

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_assignment | int(11) | NO | NULL | auto_increment |
| id_debtor | int(11) | NO | NULL |  |
| assigned_to | varchar(100) | NO | NULL |  |
| assigned_date | timestamp | NO | current_timestamp() |  |
| removed_date | datetime | YES | NULL |  |
| is_active | tinyint(1) | YES | 1 |  |

**Primary key:** id_assignment
**Indexes:**
-  `assigned_to` (assigned_to)
-  `id_debtor` (id_debtor)
-  `is_active` (is_active)
**Foreign keys:**
- `id_debtor` → `debtors.id_debtor`

---

## debtor_audit_log

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_audit | int(11) | NO | NULL | auto_increment |
| id_debtor | int(11) | NO | NULL |  |
| field_name | varchar(100) | NO | NULL |  |
| old_value | longtext | YES | NULL |  |
| new_value | longtext | YES | NULL |  |
| changed_by | varchar(100) | YES | NULL |  |
| change_date | timestamp | NO | current_timestamp() |  |
| change_source | varchar(50) | YES | 'manual' |  |
| notes | longtext | YES | NULL |  |

**Primary key:** id_audit
**Indexes:**
-  `changed_by` (changed_by)
-  `change_date` (change_date)
-  `field_name` (field_name)
-  `id_debtor` (id_debtor)
**Foreign keys:**
- `id_debtor` → `debtors.id_debtor`

---

## debtor_import_logs

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_log | int(11) | NO | NULL | auto_increment |
| filename | varchar(255) | YES | NULL |  |
| imported_by | varchar(100) | YES | NULL |  |
| total_records | int(11) | YES | NULL |  |
| successful_records | int(11) | YES | NULL |  |
| failed_records | int(11) | YES | NULL |  |
| import_date | timestamp | NO | current_timestamp() |  |
| notes | longtext | YES | NULL |  |

**Primary key:** id_log

---

## delivery_docket

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pod_id | int(11) | NO | NULL | auto_increment |
| id_hire_list_pod | int(11) | YES | NULL |  |
| pod_number | varchar(50) | YES | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |
| pod_del_type | varchar(50) | YES | NULL |  |
| pod_del_customer | varchar(50) | YES | NULL |  |
| pod_del_customer_id | varchar(10) | YES | NULL |  |
| pod_del_site | varchar(50) | YES | NULL |  |
| pod_site_id | int(11) | YES | NULL |  |
| pod_date | date | YES | NULL |  |
| submitted_at | datetime | YES | NULL |  |
| pod_fleet_no | varchar(255) | YES | NULL |  |
| pod_needed | varchar(50) | YES | NULL |  |
| pod_additions | varchar(250) | NO | NULL |  |
| pod_del_vehicle | varchar(50) | YES | NULL |  |
| pod_del_driver | varchar(50) | YES | NULL |  |
| pod_bucket300 | varchar(50) | YES | NULL |  |
| pod_bucket450 | varchar(50) | YES | NULL |  |
| pod_bucket600 | varchar(50) | YES | NULL |  |
| pod_riddlebucket | varchar(50) | YES | NULL |  |
| pod_bucket900 | varchar(50) | YES | NULL |  |
| pod_bucket1050 | varchar(50) | YES | NULL |  |
| pod_bucket1200 | varchar(50) | YES | NULL |  |
| pod_bucket1500 | varchar(50) | YES | NULL |  |
| pod_bucketdig | varchar(50) | YES | NULL |  |
| pod_bucketditch | varchar(50) | YES | NULL |  |
| pod_bucket750 | varchar(50) | YES | NULL |  |
| pod_riddlebucket_fleet_no | varchar(50) | YES | NULL |  |
| pod_forks | varchar(50) | YES | NULL |  |
| pod_forks_fleet_no | varchar(50) | YES | NULL |  |
| pod_breaker | varchar(50) | YES | NULL |  |
| pod_breaker_fleet_no | varchar(50) | YES | NULL |  |
| pod_fuel | varchar(50) | YES | NULL |  |
| pod_tracks_tyres | varchar(10) | YES | NULL |  |
| pod_panels | varchar(10) | YES | NULL |  |
| pod_windows | varchar(10) | YES | NULL |  |
| pod_mirrors | varchar(10) | YES | NULL |  |
| pod_lights | varchar(10) | YES | NULL |  |
| pod_beacons | varchar(10) | YES | NULL |  |
| pod_rams | varchar(10) | YES | NULL |  |
| pod_hoses | varchar(10) | YES | NULL |  |
| pod_keys | varchar(255) | YES | NULL |  |
| pod_adblue | varchar(50) | YES | NULL |  |
| pod_attachment1 | varchar(50) | YES | NULL |  |
| pod_attachment2 | varchar(50) | YES | NULL |  |
| pod_attachment3 | varchar(50) | YES | NULL |  |
| pod_comments | varchar(250) | YES | NULL |  |
| pod_signed_by | mediumtext | YES | NULL |  |
| pod_signedby_name | varchar(50) | YES | NULL |  |
| pod_img | varchar(100) | YES | NULL |  |
| tonnes_carried | decimal(8,2) | YES | NULL |  |

**Primary key:** pod_id
**Indexes:**
- UNIQUE `uniq_hirelist_type` (id_hire_list_pod,pod_del_type)

---

## delivery_docket_files

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_delivery_docket_files | int(11) | NO | NULL | auto_increment |
| delivery_docket_file_name | varchar(50) | YES | NULL |  |
| delivery_docket_file_path | varchar(50) | YES | NULL |  |

**Primary key:** id_delivery_docket_files

---

## delivery_type

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_delivery_type | int(11) | NO | NULL | auto_increment |
| delivery_type_name | varchar(50) | YES | NULL |  |

**Primary key:** id_delivery_type

---

## delivery_vehicle

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_delivery_vehicle | int(11) | NO | NULL | auto_increment |
| delivery_vehicle_name | varchar(50) | YES | NULL |  |
| make | varchar(50) | YES | NULL |  |
| model | varchar(50) | YES | NULL |  |
| year_first_reg | smallint(6) | YES | NULL |  |
| fuel_type | varchar(20) | NO | 'Diesel' |  |
| euro_class | varchar(10) | YES | NULL |  |
| gross_weight_kg | int(11) | YES | NULL |  |
| mot_due_date | date | YES | NULL |  |
| pmi_due_date | date | YES | NULL |  |
| tax_due_date | date | YES | NULL |  |
| active | tinyint(1) | NO | 1 |  |
| notes | text | YES | NULL |  |
| assigned_driver_id | int(11) | YES | NULL |  |

**Primary key:** id_delivery_vehicle

---

## del_pod_photos

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| pod_id | int(11) | NO | NULL |  |
| file_path | varchar(255) | NO | NULL |  |
| uploaded_at | timestamp | NO | current_timestamp() |  |

**Primary key:** id
**Indexes:**
-  `pod_id` (pod_id)

---

## drivers

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_drivers | int(11) | NO | NULL | auto_increment |
| driver_name | varchar(50) | YES | NULL |  |
| driver_comments | varchar(240) | YES | NULL |  |
| driver_360_above | varchar(50) | YES | NULL |  |
| driver_360_below | varchar(50) | YES | NULL |  |
| driver_duck | varchar(50) | YES | NULL |  |
| driver_lifting_ops | varchar(50) | YES | NULL |  |
| driver_HGV | varchar(50) | YES | NULL |  |
| driver_phone_no | varchar(50) | YES | NULL |  |
| driver_company | varchar(50) | YES | NULL |  |
| driver_CPCS | varchar(50) | YES | NULL |  |
| drivers_CPCS_expiry | date | YES | NULL |  |
| driver_add1 | varchar(50) | YES | NULL |  |
| driver_add2 | varchar(50) | YES | NULL |  |
| driver_add3 | varchar(50) | YES | NULL |  |
| driver_town | varchar(50) | YES | NULL |  |
| driver_county | varchar(50) | YES | NULL |  |
| driver_postcode | varchar(50) | YES | NULL |  |
| driver_pts | varchar(50) | YES | NULL |  |
| driver_pts_expiry | date | YES | NULL |  |
| driver_active | varchar(50) | YES | NULL |  |
| driver_pts_yn | varchar(50) | YES | NULL |  |
| driver_dob | date | YES | NULL |  |
| driver_email | varchar(50) | YES | NULL |  |
| driver_medical | varchar(50) | YES | NULL |  |
| driver_medical_exp_date | date | YES | NULL |  |
| driver_emergency_contact1 | varchar(50) | YES | NULL |  |
| driver_emergency_contact1_no | varchar(50) | YES | NULL |  |
| driver_emergency_contact1_rel | varchar(50) | YES | NULL |  |
| driver_emergency_contact2 | varchar(50) | YES | NULL |  |
| driver_emergency_contact2_no | varchar(50) | YES | NULL |  |
| driver_emergency_contact2_rel | varchar(50) | YES | NULL |  |
| driver_CPCS_checked | date | YES | NULL |  |
| first_aid_training | varchar(50) | NO | NULL |  |
| driver_dateadd | date | YES | NULL |  |

**Primary key:** id_drivers

---

## driver_alerts

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| alert_id | int(11) | NO | NULL | auto_increment |
| staff_id | int(11) | NO | NULL |  |
| alert_title | varchar(255) | NO | NULL |  |
| alert_description | text | YES | NULL |  |
| frequency_interval | int(11) | YES | 6 |  |
| next_due | date | NO | NULL |  |
| last_completed | date | YES | NULL |  |
| status | enum('Pending','Complete','Overdue') | YES | 'Pending' |  |
| created_on | datetime | YES | current_timestamp() |  |
| updated_on | datetime | YES | current_timestamp() | on update current_timestamp() |

**Primary key:** alert_id
**Indexes:**
-  `staff_id` (staff_id)

---

## emission_factor

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| factor_key | varchar(50) | NO | NULL |  |
| factor_value | decimal(10,4) | NO | NULL |  |
| unit | varchar(30) | NO | NULL |  |
| description | varchar(255) | YES | NULL |  |
| valid_from | date | YES | NULL |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id
**Indexes:**
- UNIQUE `factor_key` (factor_key)

---

## files

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_files | int(11) | NO | NULL | auto_increment |
| filepath | varchar(50) | YES | NULL |  |
| filename | varchar(50) | YES | NULL |  |
| description | varchar(50) | YES | NULL |  |

**Primary key:** id_files

---

## fitter

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| fitter_id | int(11) | NO | NULL | auto_increment |
| fitter_date | date | NO | NULL |  |
| fitter_job | varchar(250) | NO | NULL |  |
| fitter_cus | varchar(50) | NO | NULL |  |
| fitter_job_location | varchar(250) | NO | NULL |  |
| fit_machine_hrs | varchar(50) | NO | NULL |  |
| fitter_priority | varchar(50) | NO | NULL |  |
| fitter_comments | varchar(250) | NO | NULL |  |
| fitter_completed | varchar(5) | NO | NULL |  |

**Primary key:** fitter_id

---

## fitters_report

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_fitters_report | int(11) | NO | NULL | auto_increment |
| fr_number | varchar(20) | NO | NULL |  |
| fr_date | date | NO | NULL |  |
| fr_driver | varchar(100) | YES | NULL |  |
| fr_fleet_no | varchar(50) | NO | NULL |  |
| fr_make | varchar(50) | NO | NULL |  |
| fr_machine | varchar(100) | YES | NULL |  |
| fr_hours | decimal(6,2) | YES | NULL |  |
| fr_customer_name | varchar(150) | YES | NULL |  |
| fr_customer_acc | varchar(100) | YES | NULL |  |
| fr_site_address | text | YES | NULL |  |
| fr_fitter_name | varchar(100) | YES | NULL |  |
| fr_fitter_signature | text | YES | NULL |  |
| fr_customer_sign_name | varchar(100) | YES | NULL |  |
| fr_customer_signature | text | YES | NULL |  |
| fr_comments | text | YES | NULL |  |
| fr_parts_used | text | YES | NULL |  |
| fr_chargeable_customer | enum('Yes','No') | YES | NULL |  |
| fr_breakdown_completed | enum('Yes','No') | YES | NULL |  |
| fr_time_on_site | time | YES | NULL |  |
| fr_time_off_site | time | YES | NULL |  |
| fr_travel_to_from | time | YES | NULL |  |
| fr_image | varchar(255) | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |
| updated_at | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_fitters_report
**Indexes:**
- UNIQUE `fr_number` (fr_number)

---

## forms_staff

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| sf_id | int(11) | NO | NULL | auto_increment |
| staff_id | int(11) | NO | NULL |  |
| form_id | int(11) | NO | NULL |  |
| last_completed | date | YES | NULL |  |
| next_due | date | YES | NULL |  |
| status | enum('Pending','Complete','Overdue') | YES | 'Pending' |  |
| deleted | tinyint(1) | YES | 0 |  |

**Primary key:** sf_id
**Indexes:**
-  `fk_form_id` (form_id)

---

## form_assignments

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| assign_id | int(11) | NO | NULL | auto_increment |
| staff_id | int(11) | NO | NULL |  |
| form_id | int(11) | YES | NULL |  |
| form_name | varchar(255) | NO | NULL |  |
| pdf_path | varchar(255) | NO | NULL |  |
| assigned_by | int(11) | YES | NULL |  |
| assigned_on | datetime | YES | current_timestamp() |  |
| due_date | date | YES | NULL |  |
| completed_on | datetime | YES | NULL |  |
| status | enum('Pending','Complete') | YES | 'Pending' |  |
| signed_pdf_path | varchar(255) | YES | NULL |  |

**Primary key:** assign_id
**Indexes:**
-  `staff_id` (staff_id)

---

## form_submissions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| submission_id | int(11) | NO | NULL | auto_increment |
| staff_form_id | int(11) | NO | NULL |  |
| submitted_at | datetime | YES | current_timestamp() |  |
| submitted_by | int(11) | YES | NULL |  |
| form_data | longtext | YES | NULL |  |
| pdf_path | varchar(500) | YES | NULL |  |

**Primary key:** submission_id
**Indexes:**
-  `fk_formsub_formstaff` (staff_form_id)

---

## form_types

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| form_id | int(11) | NO | NULL | auto_increment |
| form_name | varchar(255) | NO | NULL |  |
| form_description | text | YES | NULL |  |
| form_path | varchar(255) | YES | NULL |  |
| recurrence_interval | int(11) | YES | 365 |  |
| active | tinyint(1) | YES | 1 |  |

**Primary key:** form_id

---

## hire_list

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| uniqid | varchar(50) | YES | NULL |  |
| id_hire_list | int(11) | NO | NULL | auto_increment |
| on_hire_date | date | YES | NULL |  |
| off_hire_date | date | YES | NULL |  |
| fleet_number | varchar(255) | YES | NULL |  |
| customer_acc | varchar(50) | YES | NULL |  |
| customer_acc_id | int(11) | YES | NULL |  |
| id_site_address | varchar(50) | YES | NULL |  |
| site_name | varchar(50) | YES | NULL |  |
| site_postcode_now | varchar(50) | YES | NULL |  |
| site_lat | decimal(18,15) | YES | NULL |  |
| site_long | decimal(18,15) | YES | NULL |  |
| job_type_name | varchar(50) | YES | NULL |  |
| purchase_order | varchar(50) | YES | NULL |  |
| drivers_name | varchar(50) | YES | NULL |  |
| delivery_vehicle_name | varchar(50) | YES | NULL |  |
| delivery_type | varchar(50) | YES | NULL |  |
| Xhire_from | varchar(50) | YES | NULL |  |
| Xhire_ref | varchar(50) | YES | NULL |  |
| xh_rate | varchar(50) | YES | NULL |  |
| hl_hire_extinguisher | varchar(50) | YES | NULL |  |
| hl_tracker | varchar(10) | YES | NULL |  |
| hl_cameras_360 | varchar(10) | YES | NULL |  |
| hl_rails | varchar(10) | YES | NULL |  |
| hl_zts | varchar(10) | YES | NULL |  |
| hl_heigh_slew | varchar(10) | YES | NULL |  |
| hl_rubber_tracks | varchar(10) | YES | NULL |  |
| hl_rotate_circuit | varchar(10) | YES | NULL |  |
| hl_bio_oil | varchar(10) | YES | NULL |  |
| hl_bladed_bkts | varchar(10) | YES | NULL |  |
| hl_vcas | varchar(10) | YES | NULL |  |
| hl_straight | varchar(10) | YES | NULL |  |
| hl_swivel | varchar(10) | YES | NULL |  |
| hl_pts | varchar(10) | YES | NULL |  |
| hl_driver_medical | varchar(10) | YES | NULL |  |
| hl_lifting_ops | varchar(10) | YES | NULL |  |
| hire_rate | varchar(50) | YES | NULL |  |
| transport_rate | varchar(50) | YES | NULL |  |
| hire_status_name | varchar(50) | YES | NULL |  |
| hire_comments | varchar(250) | YES | NULL |  |
| delivery_docket_number | varchar(50) | YES | NULL |  |
| delivery_docket_file | mediumblob | YES | NULL |  |
| collection_docket_number | varchar(50) | YES | NULL |  |
| off_hire_number | varchar(50) | NO | 'On hire' |  |
| xh_plantlist_description | varchar(50) | YES | NULL |  |
| needed | varchar(50) | YES | NULL |  |
| event_changedate | date | YES | NULL |  |
| sd_op_changes | varchar(50) | YES | NULL |  |
| bkt300 | varchar(10) | YES | NULL |  |
| bkt450 | varchar(10) | YES | NULL |  |
| bkt600 | varchar(10) | YES | NULL |  |
| bkt750 | varchar(10) | YES | NULL |  |
| bkt900 | varchar(10) | YES | NULL |  |
| bkt1050 | varchar(10) | YES | NULL |  |
| bkt1200 | varchar(10) | YES | NULL |  |
| bkt1500 | varchar(10) | YES | NULL |  |
| bktdig | varchar(10) | YES | NULL |  |
| bktditch | varchar(10) | YES | NULL |  |
| fuel_on_return | decimal(10,3) | YES | NULL |  |
| adblue_on_return | decimal(10,3) | YES | NULL |  |
| XH_actioned_OH | tinyint(1) | YES | 0 |  |

**Primary key:** id_hire_list

---

## hire_list_additions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hla_id | int(10) unsigned | NO | NULL | auto_increment |
| hla_hire_id | int(10) unsigned | NO | NULL |  |
| hla_addition_id | int(10) unsigned | NO | NULL |  |
| hla_qty | decimal(10,2) | YES | NULL |  |
| hla_notes | varchar(255) | YES | NULL |  |
| hla_added_at | timestamp | NO | current_timestamp() |  |

**Primary key:** hla_id
**Indexes:**
- UNIQUE `uniq_hire_addition` (hla_hire_id,hla_addition_id)

---

## hire_list_audit

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| audit_id | int(10) unsigned | NO | NULL | auto_increment |
| uniqid | varchar(50) | YES | NULL |  |
| id_hire_list | int(11) | NO | NULL |  |
| on_hire_date | date | YES | NULL |  |
| off_hire_date | date | YES | NULL |  |
| fleet_number | varchar(50) | YES | NULL |  |
| customer_acc | varchar(50) | YES | NULL |  |
| customer_acc_id | int(11) | YES | NULL |  |
| id_site_address | varchar(50) | YES | NULL |  |
| site_name | varchar(50) | YES | NULL |  |
| site_postcode_now | varchar(50) | YES | NULL |  |
| site_lat | decimal(18,15) | YES | NULL |  |
| site_long | decimal(18,15) | YES | NULL |  |
| job_type_name | varchar(50) | YES | NULL |  |
| purchase_order | varchar(50) | YES | NULL |  |
| drivers_name | varchar(50) | YES | NULL |  |
| delivery_vehicle_name | varchar(50) | YES | NULL |  |
| delivery_type | varchar(50) | YES | NULL |  |
| Xhire_from | varchar(50) | YES | NULL |  |
| Xhire_ref | varchar(50) | YES | NULL |  |
| xh_rate | varchar(50) | YES | NULL |  |
| hl_hire_extinguisher | varchar(50) | YES | NULL |  |
| hl_tracker | varchar(10) | YES | NULL |  |
| hl_cameras_360 | varchar(10) | YES | NULL |  |
| hl_rails | varchar(10) | YES | NULL |  |
| hl_zts | varchar(10) | YES | NULL |  |
| hl_heigh_slew | varchar(10) | YES | NULL |  |
| hl_rubber_tracks | varchar(10) | YES | NULL |  |
| hl_rotate_circuit | varchar(10) | YES | NULL |  |
| hl_bio_oil | varchar(10) | YES | NULL |  |
| hl_bladed_bkts | varchar(10) | YES | NULL |  |
| hl_vcas | varchar(10) | YES | NULL |  |
| hl_straight | varchar(10) | YES | NULL |  |
| hl_swivel | varchar(10) | YES | NULL |  |
| hl_pts | varchar(10) | YES | NULL |  |
| hl_driver_medical | varchar(10) | YES | NULL |  |
| hl_lifting_ops | varchar(10) | YES | NULL |  |
| hire_rate | varchar(50) | YES | NULL |  |
| transport_rate | varchar(50) | YES | NULL |  |
| hire_status_name | varchar(50) | YES | NULL |  |
| hire_comments | varchar(250) | YES | NULL |  |
| delivery_docket_number | varchar(50) | YES | NULL |  |
| delivery_docket_file | mediumblob | YES | NULL |  |
| collection_docket_number | varchar(50) | YES | NULL |  |
| off_hire_number | varchar(50) | NO | 'On hire' |  |
| xh_plantlist_description | varchar(50) | YES | NULL |  |
| needed | varchar(50) | YES | NULL |  |
| event_changedate | date | YES | NULL |  |
| sd_op_changes | varchar(50) | YES | NULL |  |
| bkt300 | varchar(10) | YES | NULL |  |
| bkt450 | varchar(10) | YES | NULL |  |
| bkt600 | varchar(10) | YES | NULL |  |
| bkt750 | varchar(10) | YES | NULL |  |
| bkt900 | varchar(10) | YES | NULL |  |
| bkt1050 | varchar(10) | YES | NULL |  |
| bkt1200 | varchar(10) | YES | NULL |  |
| bkt1500 | varchar(10) | YES | NULL |  |
| bktdig | varchar(10) | YES | NULL |  |
| bktditch | varchar(10) | YES | NULL |  |
| fuel_on_return | decimal(10,3) | YES | NULL |  |
| adblue_on_return | decimal(10,3) | YES | NULL |  |
| XH_actioned_OH | tinyint(1) | YES | 0 |  |
| hl_date_change | datetime | NO | current_timestamp() |  |
| audit_action | enum('INSERT','UPDATE','DELETE') | NO | NULL |  |
| audit_changed_at | datetime | NO | current_timestamp() |  |
| audit_changed_by | varchar(100) | YES | NULL |  |

**Primary key:** audit_id
**Indexes:**
-  `idx_hire_list_audit_id_hire_list` (id_hire_list)

---

## hire_list_changexh

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| uniqid | varchar(50) | YES | NULL |  |
| id_hire_list | int(11) | NO | NULL | auto_increment |
| on_hire_date | date | YES | NULL |  |
| off_hire_date | date | YES | NULL |  |
| fleet_number | varchar(50) | YES | NULL |  |
| customer_acc | varchar(50) | YES | NULL |  |
| customer_acc_id | int(11) | YES | NULL |  |
| id_site_address | varchar(50) | YES | NULL |  |
| site_name | varchar(50) | YES | NULL |  |
| site_postcode_now | varchar(50) | YES | NULL |  |
| site_lat | decimal(18,15) | YES | NULL |  |
| site_long | decimal(18,15) | YES | NULL |  |
| job_type_name | varchar(50) | YES | NULL |  |
| purchase_order | varchar(50) | YES | NULL |  |
| drivers_name | varchar(50) | YES | NULL |  |
| delivery_vehicle_name | varchar(50) | YES | NULL |  |
| delivery_type | varchar(50) | YES | NULL |  |
| Xhire_from | varchar(50) | YES | NULL |  |
| Xhire_ref | varchar(50) | YES | NULL |  |
| xh_rate | varchar(50) | YES | NULL |  |
| hl_hire_extinguisher | varchar(50) | YES | NULL |  |
| hl_tracker | varchar(10) | YES | NULL |  |
| hl_cameras_360 | varchar(10) | YES | NULL |  |
| hl_rails | varchar(10) | YES | NULL |  |
| hl_zts | varchar(10) | YES | NULL |  |
| hl_heigh_slew | varchar(10) | YES | NULL |  |
| hl_rubber_tracks | varchar(10) | YES | NULL |  |
| hl_rotate_circuit | varchar(10) | YES | NULL |  |
| hl_bio_oil | varchar(10) | YES | NULL |  |
| hl_bladed_bkts | varchar(10) | YES | NULL |  |
| hl_vcas | varchar(10) | YES | NULL |  |
| hl_straight | varchar(10) | YES | NULL |  |
| hl_swivel | varchar(10) | YES | NULL |  |
| hl_pts | varchar(10) | YES | NULL |  |
| hl_driver_medical | varchar(10) | YES | NULL |  |
| hl_lifting_ops | varchar(10) | YES | NULL |  |
| hire_rate | varchar(50) | YES | NULL |  |
| transport_rate | varchar(50) | YES | NULL |  |
| hire_status_name | varchar(50) | YES | NULL |  |
| hire_comments | varchar(250) | YES | NULL |  |
| delivery_docket_number | varchar(50) | YES | NULL |  |
| delivery_docket_file | mediumblob | YES | NULL |  |
| collection_docket_number | varchar(50) | YES | NULL |  |
| off_hire_number | varchar(50) | NO | 'On hire' |  |
| xh_plantlist_description | varchar(50) | YES | NULL |  |
| needed | varchar(50) | YES | NULL |  |
| event_changedate | date | YES | NULL |  |
| sd_op_changes | varchar(50) | NO | NULL |  |
| bkt300 | varchar(10) | YES | NULL |  |
| bkt450 | varchar(10) | YES | NULL |  |
| bkt600 | varchar(10) | YES | NULL |  |
| bkt750 | varchar(10) | YES | NULL |  |
| bkt900 | varchar(10) | YES | NULL |  |
| bkt1050 | varchar(10) | YES | NULL |  |
| bkt1200 | varchar(10) | YES | NULL |  |
| bkt1500 | varchar(10) | YES | NULL |  |
| bktdig | varchar(10) | YES | NULL |  |
| bktditch | varchar(10) | YES | NULL |  |
| fuel_on_return | decimal(10,3) | YES | NULL |  |
| adblue_on_return | decimal(10,3) | YES | NULL |  |

**Primary key:** id_hire_list

---

## hire_plant_additions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hpa_id | int(11) | NO | NULL | auto_increment |
| hpa_addition_code | varchar(10) | NO | NULL |  |
| hpa_description | varchar(50) | NO | NULL |  |
| hpa_addition_plant | varchar(50) | NO | NULL |  |

**Primary key:** hpa_id

---

## hire_site_map

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hsm_id | int(11) | NO | NULL | auto_increment |
| id_hire_list | int(11) | NO | NULL |  |
| from_site_id | int(11) | YES | NULL |  |
| to_site_id | int(11) | YES | NULL |  |

**Primary key:** hsm_id
**Indexes:**
-  `fk_from_site` (from_site_id)
-  `fk_hire` (id_hire_list)
-  `fk_to_site` (to_site_id)

---

## hire_status

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hire_status_id | int(11) | NO | NULL | auto_increment |
| hire_status_name | varchar(50) | YES | NULL |  |

**Primary key:** hire_status_id

---

## holiday_dates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hol_uniq | int(11) | NO | NULL | auto_increment |
| hol_date | date | NO | NULL |  |
| hol_type | varchar(50) | NO | NULL |  |
| hol_desc | varchar(50) | NO | NULL |  |

**Primary key:** hol_uniq

---

## holiday_events

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| evt_id | int(11) | YES | NULL |  |
| evt_start | date | YES | NULL |  |
| evt_end | date | YES | NULL |  |
| evt_text | varchar(50) | YES | NULL |  |
| evt_color | varchar(7) | YES | NULL |  |
| evt_bg | varchar(7) | YES | NULL |  |


---

## hours_drivers

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| hours_id | int(11) | NO | NULL | auto_increment |
| hours_timesheet_no | varchar(50) | YES | NULL |  |
| hours_cus_acc | varchar(50) | YES | NULL |  |
| hours_cus_acc_id | int(11) | YES | NULL |  |
| hours_site | varchar(50) | YES | NULL |  |
| hours_site_id | int(11) | YES | NULL |  |
| hours_op | varchar(50) | YES | NULL |  |
| hours_WE | date | YES | NULL |  |
| hours_machine | varchar(50) | YES | NULL |  |
| day1 | date | YES | NULL |  |
| day2 | date | YES | NULL |  |
| day3 | date | YES | NULL |  |
| day4 | date | YES | NULL |  |
| day5 | date | YES | NULL |  |
| day6 | date | YES | NULL |  |
| day7 | date | YES | NULL |  |
| day_hours1 | varchar(10) | YES | NULL |  |
| day_hours2 | varchar(10) | YES | NULL |  |
| day_hours3 | varchar(10) | YES | NULL |  |
| day_hours4 | varchar(10) | YES | NULL |  |
| day_hours5 | varchar(10) | YES | NULL |  |
| day_hours6 | varchar(10) | YES | NULL |  |
| day_hours7 | varchar(10) | YES | NULL |  |
| remarks_day_hours_1 | varchar(255) | YES | NULL |  |
| remarks_day_hours_2 | varchar(255) | YES | NULL |  |
| remarks_day_hours_3 | varchar(255) | YES | NULL |  |
| remarks_day_hours_4 | varchar(255) | YES | NULL |  |
| remarks_day_hours_5 | varchar(255) | YES | NULL |  |
| remarks_day_hours_6 | varchar(255) | YES | NULL |  |
| remarks_day_hours_7 | varchar(255) | YES | NULL |  |
| day_hours_nights1 | varchar(50) | YES | NULL |  |
| day_hours_nights2 | varchar(50) | YES | NULL |  |
| day_hours_nights3 | varchar(50) | YES | NULL |  |
| day_hours_nights4 | varchar(50) | YES | NULL |  |
| day_hours_nights5 | varchar(50) | YES | NULL |  |
| day_hours_nights6 | varchar(50) | YES | NULL |  |
| day_hours_nights7 | varchar(50) | YES | NULL |  |
| day1_bh | varchar(5) | YES | NULL |  |
| day2_bh | varchar(5) | YES | NULL |  |
| day3_bh | varchar(5) | YES | NULL |  |
| day4_bh | varchar(5) | YES | NULL |  |
| day5_bh | varchar(5) | YES | NULL |  |
| total_ot1 | varchar(50) | YES | NULL |  |
| total_ot2 | varchar(50) | YES | NULL |  |
| total_ot1_n | varchar(50) | YES | NULL |  |
| total_ot2_n | varchar(50) | YES | NULL |  |
| d_bonus | varchar(50) | YES | NULL |  |
| d_travel | varchar(50) | YES | NULL |  |
| total_hours_bh | varchar(50) | YES | NULL |  |
| total_hours_days | varchar(50) | YES | NULL |  |
| total_nights | varchar(50) | YES | NULL |  |
| total_week_hours_days | varchar(50) | YES | NULL |  |
| totalBankholidayNights | varchar(50) | YES | NULL |  |
| hirer_sign_name | varchar(250) | YES | NULL |  |
| hirer_sign_img | mediumtext | YES | NULL |  |
| op_sign_img | mediumtext | YES | NULL |  |
| signed_date | date | NO | NULL |  |

**Primary key:** hours_id

---

## internal_invoice_batches

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_invoice_batch | int(10) unsigned | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_original_path | varchar(500) | NO | NULL |  |
| batch_status | enum('uploaded','split','ocr_done','reviewing','complete','error') | NO | 'uploaded' |  |
| page_count | int(10) unsigned | NO | 0 |  |
| uploaded_by | varchar(100) | YES | NULL |  |
| uploaded_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_invoice_batch

---

## internal_invoice_ocr_templates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_invoice_template | int(10) unsigned | NO | NULL | auto_increment |
| template_name | varchar(255) | NO | NULL |  |
| supplier_name | varchar(255) | YES | NULL |  |
| page_width_points | decimal(10,2) | YES | NULL |  |
| page_height_points | decimal(10,2) | YES | NULL |  |
| boxes_json | longtext | NO | NULL |  |
| filename_pattern | varchar(500) | NO | '{invoice_number} - {customer} - {po}.pdf' |  |
| is_active | enum('Y','N') | NO | 'Y' |  |
| created_by | varchar(100) | YES | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_invoice_template

---

## internal_invoice_pages

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_invoice_page | int(10) unsigned | NO | NULL | auto_increment |
| id_invoice_batch | int(10) unsigned | NO | NULL |  |
| page_number | int(10) unsigned | NO | NULL |  |
| page_pdf_path | varchar(500) | NO | NULL |  |
| ocr_text | longtext | YES | NULL |  |
| extracted_json | longtext | YES | NULL |  |
| annotations_json | longtext | YES | NULL |  |
| extracted_invoice_number | varchar(100) | YES | NULL |  |
| extracted_customer | varchar(255) | YES | NULL |  |
| extracted_po | varchar(255) | YES | NULL |  |
| extracted_date | date | YES | NULL |  |
| extracted_amount | decimal(10,2) | YES | NULL |  |
| suggested_filename | varchar(500) | YES | NULL |  |
| confirmed_filename | varchar(500) | YES | NULL |  |
| final_pdf_path | varchar(500) | YES | NULL |  |
| review_status | enum('pending','reviewed','confirmed','error') | NO | 'pending' |  |
| review_notes | text | YES | NULL |  |
| confirmed_by | varchar(100) | YES | NULL |  |
| confirmed_at | datetime | YES | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_invoice_page
**Indexes:**
-  `idx_internal_invoice_pages_batch` (id_invoice_batch)
-  `idx_internal_invoice_pages_status` (review_status)

---

## invoice_monthly_report_periods

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_invoice_monthly_report_period | int(10) unsigned | NO | NULL | auto_increment |
| report_mth | varchar(20) | NO | NULL |  |
| start_date | date | YES | NULL |  |
| end_date | date | YES | NULL |  |
| weeks_inc_bh | decimal(6,2) | YES | NULL |  |
| notes | varchar(255) | YES | NULL |  |
| updated_by | varchar(100) | YES | NULL |  |
| updated_at | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_invoice_monthly_report_period
**Indexes:**
- UNIQUE `report_mth` (report_mth)

---

## invoice_pdf_reviews

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| filename | varchar(255) | NO | NULL |  |
| status | enum('pending','approved','amend') | NO | 'pending' |  |
| notes | text | YES | NULL |  |
| annotations_json | mediumtext | YES | NULL |  |
| updated_by | varchar(100) | YES | NULL |  |
| updated_at | datetime | NO | current_timestamp() | on update current_timestamp() |
| created_at | datetime | NO | current_timestamp() |  |

**Primary key:** id
**Indexes:**
- UNIQUE `filename` (filename)

---

## invoice_prepare_queue

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_invoice_prepare | int(10) unsigned | NO | NULL | auto_increment |
| filename | varchar(255) | NO | NULL |  |
| hire_ref | varchar(255) | YES | NULL |  |
| customer | varchar(255) | YES | NULL |  |
| supplier | varchar(255) | YES | NULL |  |
| selected_docs_json | longtext | YES | NULL |  |
| prep_status | varchar(50) | NO | 'not_prepared' |  |
| updated_by | varchar(100) | YES | NULL |  |
| updated_at | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_invoice_prepare
**Indexes:**
- UNIQUE `uq_invoice_prepare_filename` (filename)

---

## job_type

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_job_type | int(11) | NO | NULL | auto_increment |
| job_type_name | varchar(50) | YES | NULL |  |

**Primary key:** id_job_type

---

## lorry_assignments

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| job_id | int(11) | NO | NULL |  |
| lorry_day | varchar(50) | NO | NULL |  |
| assigned_date | timestamp | NO | current_timestamp() |  |

**Primary key:** id
**Indexes:**
- UNIQUE `job_id` (job_id)

---

## monthly_invoice_check

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| mic_uniqid | int(11) | NO | NULL | auto_increment |
| mic_id | varchar(50) | YES | NULL |  |
| mic_month | varchar(50) | YES | NULL |  |
| mic_invoice_no | varchar(50) | NO | NULL |  |

**Primary key:** mic_uniqid
**Indexes:**
- UNIQUE `uniq_mic_month` (mic_id,mic_month)

---

## off_hire_numbers

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| Offhire_id | int(11) | NO | NULL | auto_increment |
| Off_id_hire_list | varchar(50) | YES | NULL |  |
| Off_fleet_number | varchar(50) | YES | NULL |  |
| Off_customer | varchar(50) | YES | NULL |  |
| Off_Site | varchar(50) | YES | NULL |  |
| Off_off_hire_date | date | YES | NULL |  |
| Off_hire_number | varchar(50) | YES | NULL |  |

**Primary key:** Offhire_id

---

## pdi

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pdi_id | int(11) | NO | NULL | auto_increment |
| pdi_model | varchar(100) | YES | NULL |  |
| pdi_number | int(11) | YES | NULL |  |
| pdi_date | date | YES | NULL |  |
| pdi_fleet_no | varchar(50) | YES | NULL |  |
| pdi_hours | int(11) | YES | NULL |  |
| pdi_nxt_svc_due | int(11) | YES | NULL |  |
| pdi_service250 | varchar(10) | YES | NULL |  |
| pdi_service500 | varchar(10) | YES | NULL |  |
| pdi_service1000 | varchar(10) | YES | NULL |  |
| pdi_resetComputer | varchar(10) | YES | NULL |  |
| pdi_updateSticker | varchar(10) | YES | NULL |  |
| pdi_hs | varchar(10) | YES | NULL |  |
| pdi_rt | varchar(10) | YES | NULL |  |
| pdi_fe | varchar(10) | YES | NULL |  |
| pdi_rot | varchar(10) | YES | NULL |  |
| pdi_bio | varchar(10) | YES | NULL |  |
| pdi_rams_bushings | varchar(10) | YES | NULL |  |
| pdi_cab_guards | varchar(10) | YES | NULL |  |
| pdi_fuel_connectors | varchar(10) | YES | NULL |  |
| pdi_fueling_hose | varchar(50) | YES | NULL |  |
| pdi_lifting_eye | varchar(10) | YES | NULL |  |
| pdi_engine_oil | varchar(10) | YES | NULL |  |
| pdi_cab_glass | varchar(10) | YES | NULL |  |
| pdi_coolant_level | varchar(10) | YES | NULL |  |
| pdi_chassis_bolts | varchar(10) | YES | NULL |  |
| pdi_hydraulic_fluid | varchar(10) | YES | NULL |  |
| pdi_seat_secure | varchar(10) | YES | NULL |  |
| pdi_washer_fluid | varchar(10) | YES | NULL |  |
| pdi_tracks | varchar(10) | YES | NULL |  |
| pdi_ad_blue | varchar(10) | YES | NULL |  |
| pdi_blade_stabilizers | varchar(10) | YES | NULL |  |
| pdi_seat_belt | varchar(10) | YES | NULL |  |
| pdi_cable_connections | varchar(10) | YES | NULL |  |
| pdi_steering | varchar(10) | YES | NULL |  |
| pdi_cleanliness | varchar(10) | YES | NULL |  |
| pdi_brakes | varchar(10) | YES | NULL |  |
| pdi_battery_security | varchar(10) | YES | NULL |  |
| pdi_bodywork | varchar(10) | YES | NULL |  |
| pdi_operating_controls | varchar(10) | YES | NULL |  |
| pdi_front_left_wheel | varchar(10) | YES | NULL |  |
| pdi_warning_lights | varchar(10) | YES | NULL |  |
| pdi_front_right_wheel | varchar(10) | YES | NULL |  |
| pdi_gauges_instruments | varchar(10) | YES | NULL |  |
| pdi_rear_left_wheel | varchar(10) | YES | NULL |  |
| pdi_railings_boxing_ring | varchar(10) | YES | NULL |  |
| pdi_rear_right_wheel | varchar(10) | YES | NULL |  |
| pdi_fan_belts_chains | varchar(10) | YES | NULL |  |
| pdi_fuel_cap_secured | varchar(10) | YES | NULL |  |
| pdi_lenses_mirrors | varchar(10) | YES | NULL |  |
| pdi_rear_360_cameras | varchar(10) | YES | NULL |  |
| pdi_horn | varchar(10) | YES | NULL |  |
| pdi_travel_alarm | varchar(10) | YES | NULL |  |
| pdi_lights_beacons | varchar(10) | YES | NULL |  |
| pdi_obvious_leaks | varchar(10) | YES | NULL |  |
| pdi_safety_pins_bolts | varchar(10) | YES | NULL |  |
| pdi_floor_mats_air_freshener | varchar(10) | YES | NULL |  |
| pdi_comments | varchar(500) | YES | NULL |  |
| pdi_fuel | decimal(3,2) | YES | NULL |  |
| pdi_signed_canvas | mediumtext | YES | NULL |  |
| pdi_signed_by_data | varchar(100) | YES | NULL |  |
| pdi_signed_name | varchar(100) | YES | NULL |  |
| pdi_image | varchar(255) | YES | NULL |  |

**Primary key:** pdi_id

---

## pdi_ocr_templates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_template | int(11) | NO | NULL | auto_increment |
| template_name | varchar(100) | NO | NULL |  |
| page_no | int(11) | NO | 1 |  |
| dpi | int(11) | NO | 300 |  |
| boxes_json | longtext | NO | NULL |  |
| is_active | tinyint(4) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | YES | NULL |  |

**Primary key:** id_template

---

## pdi_uploads

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_pdi_upload | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| extracted_json | longtext | YES | NULL |  |
| extracted_at | datetime | YES | NULL |  |
| needs_review | tinyint(1) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| ocr_status | varchar(20) | YES | NULL |  |
| ocr_ran_at | datetime | YES | NULL |  |
| ocr_json | longtext | YES | NULL |  |
| ocr_error | text | YES | NULL |  |
| id_batch | int(11) | YES | NULL |  |
| page_no | int(11) | YES | NULL |  |

**Primary key:** id_pdi_upload
**Indexes:**
-  `id_batch` (id_batch)
-  `page_no` (page_no)
- UNIQUE `uq_pod_sha` (sha256)

---

## pdi_uploads_archive_2026_05

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_pdi_upload | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| extracted_json | longtext | YES | NULL |  |
| extracted_at | datetime | YES | NULL |  |
| needs_review | tinyint(1) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| ocr_status | varchar(20) | YES | NULL |  |
| ocr_ran_at | datetime | YES | NULL |  |
| ocr_json | longtext | YES | NULL |  |
| ocr_error | text | YES | NULL |  |
| id_batch | int(11) | YES | NULL |  |
| page_no | int(11) | YES | NULL |  |

**Primary key:** id_pdi_upload
**Indexes:**
-  `id_batch` (id_batch)
-  `page_no` (page_no)
- UNIQUE `uq_pod_sha` (sha256)

---

## pdi_upload_batches

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_batch | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| status | varchar(20) | NO | 'processing' |  |

**Primary key:** id_batch

---

## phone_calls

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| call_id | int(11) | NO | NULL | auto_increment |
| caller_name | varchar(255) | YES | NULL |  |
| caller_number | varchar(50) | YES | NULL |  |
| call_subject | varchar(255) | YES | NULL |  |
| call_details | text | YES | NULL |  |
| action_required | enum('Action Required','No Action','Message to Pass On','Other') | YES | 'Other' |  |
| message_to_user | int(11) | YES | NULL |  |
| action_status | enum('Pending','Completed') | YES | 'Pending' |  |
| call_datetime | datetime | NO | NULL |  |
| created_by | int(11) | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |
| updated_at | timestamp | YES | NULL |  |

**Primary key:** call_id
**Indexes:**
-  `created_by` (created_by)

---

## plant_category

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| xh_plantlist_id | int(11) | NO | NULL | auto_increment |
| xh_plantlist_description | varchar(50) | YES | NULL |  |
| xh_list_order | varchar(50) | YES | NULL |  |
| plant_category_name | varchar(50) | NO | '0' |  |
| plant_sub_category_name | varchar(50) | YES | NULL |  |
| pc_weight_category | varchar(45) | YES | NULL |  |

**Primary key:** xh_plantlist_id

---

## plant_category_types

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pc_id | int(11) | NO | NULL | auto_increment |
| pct_plant_category_name | varchar(50) | NO | NULL |  |
| pct_plant_sub_category_name | varchar(50) | NO | NULL |  |

**Primary key:** pc_id

---

## plant_category_weights

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pcw_id | int(11) | NO | NULL | auto_increment |
| pcw_category_weight | varchar(10) | NO | NULL |  |

**Primary key:** pcw_id
**Indexes:**
- UNIQUE `pcw_category_weight` (pcw_category_weight)

---

## plant_equipment_capacity_defaults

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| needed_key | varchar(100) | NO | NULL |  |
| fuel_tank_litres | decimal(10,2) | YES | NULL |  |
| adblue_tank_litres | decimal(10,2) | YES | NULL |  |
| notes | varchar(255) | YES | NULL |  |

**Primary key:** id
**Indexes:**
- UNIQUE `uq_needed_key` (needed_key)

---

## plant_list

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_plant_list | int(11) | NO | NULL | auto_increment |
| fleet_Number | varchar(50) | NO | NULL |  |
| Model | varchar(50) | YES | NULL |  |
| display | varchar(50) | YES | NULL |  |
| Serial_Number | varchar(50) | YES | NULL |  |
| Quick_Hitch_Number | varchar(50) | YES | NULL |  |
| Fire_Extinguisher | varchar(50) | YES | NULL |  |
| Tracker | varchar(50) | YES | NULL |  |
| Cameras_360 | varchar(50) | YES | NULL |  |
| Rails | varchar(50) | YES | NULL |  |
| Immobiliser | varchar(50) | YES | NULL |  |
| Height_Slew | varchar(50) | YES | NULL |  |
| Rubber_Tracks | varchar(50) | YES | NULL |  |
| Rotate_Circuit | varchar(50) | YES | NULL |  |
| Bio_Oil | varchar(50) | YES | NULL |  |
| Rego | varchar(50) | YES | NULL |  |
| vcas | varchar(50) | YES | NULL |  |
| code | varchar(50) | YES | NULL |  |
| Last_Serviced | date | YES | NULL |  |
| Last_Serviced_Hours | varchar(50) | YES | NULL |  |
| DPF_Cleaned | varchar(50) | YES | NULL |  |
| DPF_Cleaned_date | date | YES | NULL |  |
| Lifting_Cert_Expiry_Date | date | YES | NULL |  |
| Loler_Cert_Path | varchar(255) | YES | NULL |  |
| Lifting_Cert_File | varchar(255) | YES | NULL |  |
| Hitch_Cert_Expiry_Date | date | YES | NULL |  |
| Hitch_Cert_Path | varchar(255) | YES | NULL |  |
| Hitch_Cert_File | varchar(255) | YES | NULL |  |
| Machine_hours | varchar(50) | YES | NULL |  |
| Machine_hours_date_recorded | date | YES | NULL |  |
| Next_service_due | varchar(50) | YES | NULL |  |
| service_due_500_or_1000 | varchar(50) | YES | NULL |  |
| Last_PDI | varchar(50) | YES | NULL |  |
| Last_service_PDI | varchar(50) | YES | NULL |  |
| category_and_weight | varchar(50) | NO | NULL |  |
| Category | varchar(50) | YES | NULL |  |
| pl_sub_category | varchar(50) | NO | NULL |  |
| Weight_category | varchar(50) | YES | NULL |  |
| Purchase_Date | date | YES | NULL |  |
| master_pin | varchar(50) | YES | NULL |  |
| depot_pin | varchar(50) | YES | NULL |  |
| track_size | varchar(50) | YES | NULL |  |
| pl_sold_date | date | YES | NULL |  |
| plant_archive | varchar(50) | YES | NULL |  |
| ready_flag | enum('Y','N') | YES | 'N' |  |

**Primary key:** id_plant_list
**Indexes:**
-  `idx_pl_Model_archive` (Model,plant_archive)

---

## plant_list_additions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pla_id | int(10) unsigned | NO | NULL | auto_increment |
| pla_plant_id | int(10) unsigned | NO | NULL |  |
| pla_addition_id | int(10) unsigned | NO | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |

**Primary key:** pla_id
**Indexes:**
-  `idx_pla_addition_id` (pla_addition_id)
-  `idx_pla_plant_id` (pla_plant_id)
- UNIQUE `uk_plant_addition` (pla_plant_id,pla_addition_id)

---

## plant_list_audit

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| pl_uniq_id | int(11) | NO | NULL | auto_increment |
| id_plant_list | int(11) | YES | NULL |  |
| fleet_Number | varchar(50) | NO | NULL |  |
| Model | varchar(50) | YES | NULL |  |
| display | varchar(50) | YES | NULL |  |
| Serial_Number | varchar(50) | YES | NULL |  |
| Quick_Hitch_Number | varchar(50) | YES | NULL |  |
| Fire_Extinguisher | varchar(50) | YES | NULL |  |
| Tracker | varchar(50) | YES | NULL |  |
| Cameras_360 | varchar(50) | YES | NULL |  |
| Rails | varchar(50) | YES | NULL |  |
| Immobiliser | varchar(50) | YES | NULL |  |
| Height_Slew | varchar(50) | YES | NULL |  |
| Rubber_Tracks | varchar(50) | YES | NULL |  |
| Rotate_Circuit | varchar(50) | YES | NULL |  |
| Bio_Oil | varchar(50) | YES | NULL |  |
| Rego | varchar(50) | YES | NULL |  |
| code | varchar(50) | YES | NULL |  |
| Last_Serviced | date | YES | NULL |  |
| Last_Serviced_Hours | varchar(50) | YES | NULL |  |
| DPF_Cleaned | varchar(50) | YES | NULL |  |
| DPF_Cleaned_date | date | YES | NULL |  |
| Lifting_Cert_Expiry_Date | date | YES | NULL |  |
| Hitch_Cert_Expiry_Date | date | YES | NULL |  |
| Machine_hours | varchar(50) | YES | NULL |  |
| Machine_hours_date_recorded | date | YES | NULL |  |
| Next_service_due | varchar(50) | YES | NULL |  |
| Last_PDI | varchar(50) | YES | NULL |  |
| Last_service_PDI | varchar(50) | YES | NULL |  |
| Category | varchar(50) | YES | NULL |  |
| Weight_category | varchar(50) | YES | NULL |  |
| Purchase_Date | date | YES | NULL |  |
| master_pin | varchar(50) | YES | NULL |  |
| depot_pin | varchar(50) | YES | NULL |  |
| track_size | varchar(50) | YES | NULL |  |
| plant_archive | varchar(50) | YES | NULL |  |
| pl_date_change | date | YES | NULL |  |
| audit_action | enum('INSERT','UPDATE','DELETE') | NO | NULL |  |
| audit_changed_at | datetime | NO | current_timestamp() |  |
| audit_changed_by | varchar(100) | YES | NULL |  |

**Primary key:** pl_uniq_id

---

## plant_list_works

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| plw_uniq_id | int(11) | NO | NULL | auto_increment |
| plw_id_plant_list | int(11) | NO | NULL |  |
| plw_fleet_number | varchar(50) | YES | NULL |  |
| plw_model | varchar(50) | YES | NULL |  |
| plw_date | varchar(50) | YES | NULL |  |
| plw_works | varchar(500) | YES | NULL |  |

**Primary key:** plw_uniq_id

---

## plant_specifications

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_plant_specifications | int(11) | NO | NULL | auto_increment |
| Model | varchar(50) | NO | NULL |  |
| EC_conform_no | varchar(50) | YES | NULL |  |
| EU_Stage | varchar(50) | NO | NULL |  |
| Fuel_tank_size | varchar(50) | YES | NULL |  |
| Adblue_tank_size | varchar(50) | YES | NULL |  |
| Pin_size | varchar(50) | YES | NULL |  |
| Pipes | varchar(50) | YES | NULL |  |
| Boom_length | varchar(50) | YES | NULL |  |
| Arm_length | varchar(50) | YES | NULL |  |
| Test_radius | varchar(50) | YES | NULL |  |
| SWL | varchar(50) | YES | NULL |  |
| engine_power_rate | varchar(50) | YES | NULL |  |
| engine | varchar(50) | YES | NULL |  |
| height_to_cab | varchar(50) | YES | NULL |  |
| height_to_upper_structure | varchar(50) | YES | NULL |  |
| length | varchar(50) | YES | NULL |  |
| operating_weight | varchar(50) | YES | NULL |  |
| width | varchar(50) | YES | NULL |  |
| weight_of_quick_hitch | varchar(50) | YES | NULL |  |
| Track_size | varchar(50) | YES | NULL |  |
| Master_pin | varchar(50) | YES | NULL |  |
| Depot_pin | varchar(50) | YES | NULL |  |
| category | varchar(50) | NO | NULL |  |
| ps_sub_category | varchar(50) | YES | NULL |  |
| ps_weight_category | varchar(50) | YES | NULL |  |
| archive | varchar(50) | YES | NULL |  |

**Primary key:** id_plant_specifications

---

## plant_specification_models

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_plant_specification_model | int(10) unsigned | NO | NULL | auto_increment |
| psm_model | varchar(150) | NO | NULL |  |
| psm_category | varchar(150) | NO | NULL |  |
| psm_sub_category | varchar(150) | NO | NULL |  |
| psm_weight_category | varchar(50) | NO | NULL |  |
| psm_archive | char(1) | NO | 'N' |  |
| psm_created_at | timestamp | NO | current_timestamp() |  |
| psm_updated_at | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** id_plant_specification_model
**Indexes:**
-  `idx_psm_archive` (psm_archive)
-  `idx_psm_category` (psm_category)
-  `idx_psm_sub_category` (psm_sub_category)
-  `idx_psm_weight_category` (psm_weight_category)
- UNIQUE `uq_psm_model` (psm_model)

---

## pod_ocr_templates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_template | int(11) | NO | NULL | auto_increment |
| template_name | varchar(100) | NO | NULL |  |
| page_no | int(11) | NO | 1 |  |
| dpi | int(11) | NO | 300 |  |
| boxes_json | longtext | NO | NULL |  |
| is_active | tinyint(4) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | YES | NULL |  |

**Primary key:** id_template

---

## pod_uploads

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_pod_upload | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| extracted_json | longtext | YES | NULL |  |
| extracted_at | datetime | YES | NULL |  |
| needs_review | tinyint(1) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| ocr_status | varchar(20) | YES | NULL |  |
| ocr_ran_at | datetime | YES | NULL |  |
| ocr_json | longtext | YES | NULL |  |
| ocr_error | text | YES | NULL |  |
| id_batch | int(11) | YES | NULL |  |
| page_no | int(11) | YES | NULL |  |

**Primary key:** id_pod_upload
**Indexes:**
-  `id_batch` (id_batch)
-  `page_no` (page_no)
- UNIQUE `uq_pod_sha` (sha256)

---

## pod_upload_batches

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_batch | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| status | varchar(20) | NO | 'processing' |  |

**Primary key:** id_batch

---

## postcodelatlng

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| postcode | varchar(8) | NO | NULL |  |
| latitude | decimal(18,15) | NO | NULL |  |
| longitude | decimal(18,15) | NO | NULL |  |
| postcode_active | varchar(50) | NO | NULL |  |

**Primary key:** id
**Indexes:**
-  `idx_postcode` (postcode)

---

## price_list

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| price_id | int(11) | NO | NULL | auto_increment |
| xh_plantlist_id | int(11) | YES | NULL |  |
| price_description | varchar(50) | NO | NULL |  |
| hpa_id | int(11) | NO | NULL |  |
| price_item_type | varchar(50) | YES | NULL |  |
| hire_type | varchar(50) | YES | NULL |  |
| additions_type | varchar(50) | YES | NULL |  |
| price | decimal(10,2) | YES | NULL |  |

**Primary key:** price_id

---

## price_list_customer_override

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| plco_id | int(11) | NO | NULL | auto_increment |
| plco_price_id | int(11) | NO | NULL |  |
| plco_id_customer | int(11) | NO | NULL |  |
| plco_price | decimal(10,2) | NO | NULL |  |

**Primary key:** plco_id

---

## site_address

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_site_address | int(11) | NO | NULL | auto_increment |
| site_name | varchar(50) | YES | NULL |  |
| site_customer | varchar(50) | YES | NULL |  |
| site_customer_id | varchar(10) | YES | NULL |  |
| site_add1 | varchar(100) | YES | NULL |  |
| site_add2 | varchar(100) | YES | NULL |  |
| site_add3 | varchar(100) | YES | NULL |  |
| site_town | varchar(50) | YES | NULL |  |
| site_county | varchar(50) | YES | NULL |  |
| site_post_code | varchar(50) | YES | NULL |  |
| site_contact | varchar(50) | YES | NULL |  |
| site_contact_no | varchar(50) | YES | NULL |  |
| site_contact_id | int(11) | YES | NULL |  |
| site_active | varchar(50) | YES | NULL |  |
| site_description | varchar(50) | YES | NULL |  |
| site_lat | decimal(18,15) | YES | NULL |  |
| site_long | decimal(18,15) | YES | NULL |  |

**Primary key:** id_site_address
**Indexes:**
-  `idx_site_post_code` (site_post_code)

---

## suppliers

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_supplier | int(11) | NO | NULL | auto_increment |
| supplier_acc | varchar(50) | YES | NULL |  |
| supplier_customer_no | varchar(50) | YES | NULL |  |
| supplier_type | varchar(50) | YES | NULL |  |
| supplier_sub_type | varchar(50) | YES | NULL |  |
| supplier_name | varchar(50) | YES | NULL |  |
| supplier_add1 | varchar(50) | YES | NULL |  |
| supplier_add2 | varchar(50) | YES | NULL |  |
| supplier_town | varchar(50) | YES | NULL |  |
| supplier_county | varchar(50) | YES | NULL |  |
| supplier_postcode | varchar(50) | YES | NULL |  |
| supplier_accounts_contact | varchar(50) | YES | NULL |  |
| supplier_accounts_contact_no | varchar(50) | YES | NULL |  |
| supplier_email | varchar(50) | YES | NULL |  |
| supplier_xhire | varchar(50) | YES | NULL |  |
| supplier_active | varchar(50) | YES | NULL |  |
| supplier_name_norm | varchar(255) | YES | NULL |  |
| supplier_aliases | longtext | YES | NULL |  |
| supplier_vat_no | varchar(40) | YES | NULL |  |
| supplier_postcode_norm | varchar(20) | YES | NULL |  |
| supplier_match_notes | varchar(255) | YES | NULL |  |

**Primary key:** id_supplier
**Indexes:**
-  `idx_suppliers_name_norm` (supplier_name_norm)
-  `idx_suppliers_postcode_norm` (supplier_postcode_norm)
-  `idx_suppliers_vat_no` (supplier_vat_no)

---

## suppliers_productlists

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_product_uniq | int(11) | NO | NULL | auto_increment |
| supplier_prod_code | varchar(50) | YES | NULL |  |
| product_hick_code | varchar(50) | YES | NULL |  |
| product_hick_code_id | int(11) | YES | NULL |  |
| product_supplier | varchar(50) | YES | NULL |  |
| product_supplier_id | int(11) | NO | NULL |  |
| product_description | varchar(250) | YES | NULL |  |
| product_uom_qty | varchar(50) | YES | NULL |  |
| product_uom | varchar(50) | YES | NULL |  |
| product_price | varchar(50) | YES | NULL |  |
| product_price_date | date | YES | NULL |  |
| product_vat | varchar(50) | YES | NULL |  |
| product_forplant | varchar(250) | YES | NULL |  |
| product_stock_on_hand | varchar(50) | YES | NULL |  |
| product_bay_location | varchar(50) | YES | NULL |  |
| product_no_machines | varchar(50) | YES | NULL |  |
| product_min_stock | varchar(50) | YES | NULL |  |
| product_max_stock | varchar(50) | YES | NULL |  |

**Primary key:** id_product_uniq
**Indexes:**
-  `idx_spl_product_hick_code` (product_hick_code)
-  `idx_spl_product_supplier_id` (product_supplier_id)

---

## suppliers_product_hickey

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| sphl_id | int(11) | NO | NULL | auto_increment |
| sphl_hickey_stock_code | varchar(50) | YES | NULL |  |
| sphl_stock_hickey_description | varchar(50) | YES | NULL |  |
| sphl_date_added | date | YES | NULL |  |
| sphl_stock_on_hand | int(11) | YES | NULL |  |
| sphl_stock_location | varchar(10) | YES | NULL |  |
| sphl_archive | varchar(10) | YES | NULL |  |

**Primary key:** sphl_id

---

## supplier_invoices

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_supplier_invoice | int(11) | NO | NULL | auto_increment |
| id_supplier | int(11) | YES | NULL |  |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | varchar(500) | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| supplier_name_raw | varchar(255) | YES | NULL |  |
| supplier_match_method | varchar(50) | YES | NULL |  |
| supplier_match_score | decimal(5,2) | YES | NULL |  |
| invoice_number | varchar(80) | YES | NULL |  |
| invoice_date | date | YES | NULL |  |
| vat_no | varchar(50) | YES | NULL |  |
| postcode | varchar(50) | YES | NULL |  |
| tax_point_date | date | YES | NULL |  |
| net_total | decimal(10,2) | YES | NULL |  |
| vat_total | decimal(10,2) | YES | NULL |  |
| gross_total | decimal(10,2) | YES | NULL |  |
| currency | char(3) | YES | 'GBP' |  |
| extraction_method | varchar(50) | NO | 'text_pdf' |  |
| confidence | decimal(4,3) | NO | 0.000 |  |
| needs_review | tinyint(1) | NO | 1 |  |
| raw_text | longtext | YES | NULL |  |
| created_at | timestamp | NO | current_timestamp() |  |
| purchase_order_no | varchar(100) | YES | NULL |  |
| contract_no | varchar(100) | YES | NULL |  |

**Primary key:** id_supplier_invoice
**Indexes:**
-  `idx_invoice_date` (invoice_date)
-  `idx_invoice_number` (invoice_number)
-  `idx_supplier` (id_supplier)
- UNIQUE `uniq_sha256` (sha256)

---

## supplier_invoice_items

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_supplier_invoice_item | int(11) | NO | NULL | auto_increment |
| id_supplier_invoice | int(11) | NO | NULL |  |
| line_no | int(11) | YES | NULL |  |
| description | text | YES | NULL |  |
| product_code | varchar(80) | YES | NULL |  |
| qty | decimal(10,3) | YES | NULL |  |
| product_uom_qty | decimal(10,3) | YES | NULL |  |
| product_uom | varchar(20) | YES | NULL |  |
| unit_price | decimal(10,2) | YES | NULL |  |
| line_total | decimal(10,2) | YES | NULL |  |
| is_stock_item | tinyint(1) | NO | 0 |  |

**Primary key:** id_supplier_invoice_item
**Indexes:**
-  `idx_invoice` (id_supplier_invoice)
-  `idx_sii_stock` (is_stock_item)
- UNIQUE `uq_inv_line` (id_supplier_invoice,line_no)

---

## supplier_invoice_templates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_template | int(11) | NO | NULL | auto_increment |
| id_supplier | int(11) | NO | NULL |  |
| template_name | varchar(120) | NO | NULL |  |
| page_no | tinyint(4) | NO | 1 |  |
| dpi | smallint(6) | NO | 300 |  |
| boxes_json | longtext | NO | NULL |  |
| is_active | tinyint(1) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | YES | NULL |  |

**Primary key:** id_template
**Indexes:**
- UNIQUE `uniq_supplier_template` (id_supplier)

---

## supplier_product_machine_compatibility

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| spmc_id | int(11) | NO | NULL | auto_increment |
| supplier_product_id | varchar(50) | YES | NULL |  |
| supplier_machine_id | varchar(50) | YES | NULL |  |

**Primary key:** spmc_id
**Indexes:**
-  `idx_spmc_supplier_machine_id` (supplier_machine_id)
-  `idx_spmc_supplier_product_id` (supplier_product_id)

---

## supplier_product_preferred

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| spp_id | int(11) | NO | NULL | auto_increment |
| spp_hickey_product_id | int(11) | NO | NULL |  |
| spp_stock_product_id | int(11) | NO | NULL |  |
| spp_supplier_id | int(11) | NO | NULL |  |
| spp_is_preferred | tinyint(1) | YES | 0 |  |

**Primary key:** spp_id
**Indexes:**
-  `idx_spp_product_id_preferred` (spp_hickey_product_id,spp_is_preferred)

---

## supplier_product_stock_summary

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| sphl_id | int(11) | NO | NULL |  |
| hickey_stock_code | varchar(255) | YES | NULL |  |
| stock_description | text | YES | NULL |  |
| stock_on_hand | int(11) | YES | NULL |  |
| stock_location | varchar(50) | YES | NULL |  |
| models | text | YES | NULL |  |
| model_usage_count | int(11) | YES | NULL |  |
| preferred_supplier | varchar(255) | YES | NULL |  |
| preferred_stock_code | varchar(255) | YES | NULL |  |
| last_updated | timestamp | NO | current_timestamp() | on update current_timestamp() |

**Primary key:** sphl_id

---

## timesheets

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| timesheets_id | int(11) | NO | NULL | auto_increment |
| timesheets_cus | varchar(50) | NO | NULL |  |
| timesheets_site | varchar(50) | NO | NULL |  |
| timesheet_driver | varchar(50) | NO | NULL |  |
| timesheet_we | varchar(50) | NO | NULL |  |
| timesheet_machine | varchar(50) | NO | NULL |  |
| timesheet_mon | varchar(50) | NO | NULL |  |
| timesheet_tue | decimal(10,0) | NO | NULL |  |
| timesheet_wed | decimal(10,0) | NO | NULL |  |
| timesheet_thu | decimal(10,0) | NO | NULL |  |
| timesheet_fri | decimal(10,0) | NO | NULL |  |
| timesheet_sat | decimal(10,0) | NO | NULL |  |
| timesheet_sun | decimal(10,0) | NO | NULL |  |
| timesheet_signed | varchar(50) | NO | NULL |  |
| timesheet_signdate | varchar(50) | NO | NULL |  |
| timesheet_driv_bon | varchar(50) | NO | NULL |  |
| timesheet_comments | varchar(240) | NO | NULL |  |

**Primary key:** timesheets_id

---

## time_dimension

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL |  |
| db_date | date | NO | NULL |  |
| year | int(11) | NO | NULL |  |
| month | int(11) | NO | NULL |  |
| day | int(11) | NO | NULL |  |
| quarter | int(11) | NO | NULL |  |
| week | int(11) | NO | NULL |  |
| day_name | varchar(9) | NO | NULL |  |
| month_name | varchar(9) | NO | NULL |  |
| holiday_flag | char(1) | YES | 'f' |  |
| weekend_flag | char(1) | YES | 'f' |  |
| event | varchar(50) | YES | NULL |  |
| WE | varchar(50) | NO | NULL |  |
| WE_date | date | NO | NULL |  |
| inv_period | varchar(50) | NO | NULL |  |

**Primary key:** id
**Indexes:**
- UNIQUE `td_dbdate_idx` (db_date)
- UNIQUE `td_ymd_idx` (year,month,day)

---

## ts_ocr_templates

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_template | int(11) | NO | NULL | auto_increment |
| template_name | varchar(100) | NO | NULL |  |
| page_no | int(11) | NO | 1 |  |
| dpi | int(11) | NO | 300 |  |
| boxes_json | longtext | NO | NULL |  |
| is_active | tinyint(4) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| updated_at | datetime | YES | NULL |  |

**Primary key:** id_template

---

## ts_uploads

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_ts_upload | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| extracted_json | longtext | YES | NULL |  |
| extracted_at | datetime | YES | NULL |  |
| needs_review | tinyint(1) | NO | 1 |  |
| created_at | datetime | NO | current_timestamp() |  |
| ocr_status | varchar(20) | YES | NULL |  |
| ocr_ran_at | datetime | YES | NULL |  |
| ocr_json | longtext | YES | NULL |  |
| ocr_error | text | YES | NULL |  |
| id_batch | int(11) | YES | NULL |  |
| page_no | int(11) | YES | NULL |  |

**Primary key:** id_ts_upload
**Indexes:**
-  `id_batch` (id_batch)
-  `page_no` (page_no)
- UNIQUE `uq_pod_sha` (sha256)

---

## ts_upload_batches

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id_batch | int(11) | NO | NULL | auto_increment |
| original_filename | varchar(255) | NO | NULL |  |
| stored_path | text | NO | NULL |  |
| sha256 | char(64) | NO | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| status | varchar(20) | NO | 'processing' |  |

**Primary key:** id_batch

---

## users

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| username | varchar(100) | NO | NULL |  |
| password_hash | varchar(255) | NO | NULL |  |
| role | enum('manager','driver','fitter') | NO | NULL |  |

**Primary key:** id
**Indexes:**
- UNIQUE `username` (username)

---

## users_page_permissions

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| page_path | varchar(255) | NO | NULL |  |
| allowed_role | varchar(50) | NO | NULL |  |

**Primary key:** id

---

## vehicle_alert

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| alert_id | int(11) | NO | NULL | auto_increment |
| id_delivery_vehicle | int(11) | NO | NULL |  |
| alert_type | varchar(50) | NO | NULL |  |
| alert_title | varchar(255) | YES | NULL |  |
| next_due | date | NO | NULL |  |
| frequency_interval | int(11) | YES | NULL |  |
| status | varchar(20) | NO | 'Pending' |  |
| notes | text | YES | NULL |  |
| created_at | datetime | NO | current_timestamp() |  |
| created_by | varchar(100) | YES | NULL |  |
| staff_id | int(11) | YES | NULL |  |

**Primary key:** alert_id
**Indexes:**
-  `idx_va_due` (next_due)
-  `idx_va_staff` (staff_id)
-  `idx_va_status` (status)
-  `idx_va_vehicle` (id_delivery_vehicle)

---

## vehicle_document

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| id_delivery_vehicle | int(11) | NO | NULL |  |
| doc_type | varchar(50) | NO | NULL |  |
| file_path | varchar(500) | NO | NULL |  |
| original_filename | varchar(255) | YES | NULL |  |
| uploaded_at | datetime | NO | current_timestamp() |  |
| uploaded_by | varchar(100) | YES | NULL |  |
| expires_at | date | YES | NULL |  |
| notes | text | YES | NULL |  |

**Primary key:** id
**Indexes:**
-  `idx_vd_expires` (expires_at)
-  `idx_vd_vehicle` (id_delivery_vehicle)

---

## vehicle_monthly_reading

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| id | int(11) | NO | NULL | auto_increment |
| id_delivery_vehicle | int(11) | NO | NULL |  |
| year_month_label | char(7) | NO | NULL |  |
| gallons_used | decimal(10,2) | YES | NULL |  |
| miles_driven | int(11) | YES | NULL |  |
| notes | text | YES | NULL |  |
| recorded_at | datetime | NO | current_timestamp() |  |
| recorded_by | varchar(100) | YES | NULL |  |

**Primary key:** id
**Indexes:**
-  `idx_vmr_vehicle` (id_delivery_vehicle)
- UNIQUE `uk_vehicle_month` (id_delivery_vehicle,year_month_label)

---

## xhire

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| Xhire_id | int(11) | NO | NULL | auto_increment |
| Xhire_Supplier_name | varchar(50) | YES | NULL |  |

**Primary key:** Xhire_id

---

## xhire_specs

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| xh_spec_id | int(11) | NO | NULL | auto_increment |
| xh_spec_fleetno | varchar(50) | NO | NULL |  |
| xh_spec_fleetreq | varchar(50) | NO | NULL |  |
| xh_spec_display | varchar(50) | NO | NULL |  |
| xh_spec_fueltank | varchar(50) | NO | NULL |  |
| xh_spec_adbtank | varchar(50) | NO | NULL |  |

**Primary key:** xh_spec_id

---

## xh_count

| Column | Type | Nullable | Default | Extra |
|--------|------|----------|---------|-------|
| needed | varchar(50) | YES | NULL |  |
| COUNT(needed) | bigint(21) | YES | NULL |  |