//// -- LEVEL 1
//// -- Schemas, Tables and References

// Creating tables
// You can define the tables with full schema names

Table tenants {
  id uuid [pk]
  name varchar
  description varchar
  support_phone varchar
  support_url varchar
  website_url varchar
  store_url varchar
}

Table orgs {
  id uuid [pk]
  name varchar
  org_number varchar
  orgID int // to remove
  site varchar
  address varchar
  main_contact varchar
  contact_phone varchar
  contact_email varchar
  is_financial boolean
  is_active boolean
  status varchar // choice: Pending	Inactive	Retired	Demo	StockHold
  subscription_type varchar // choice: lite, fleet, enterprise, free
  owns_equipment boolean
  reseller_id int [ref: > resellers.id]
  tennant_id int [ref: > tennants.id]
  require_subs boolean
  enable_projects boolean
  allow_sentry boolean
  can_qr_scan boolean
  can_add_equipment boolean
  hide_manual_equipment_select boolean
  manual_transactions boolean
  paramaters varchar
  custDispAuth int //choice 1, 2, 3,4,5 ... relates to fieds enforced in dispense
  allow_hardware boolean
  allow_shake boolean
  user_save_password boolean
  allow_offline_login boolean
  is_trial boolean
  trial_expires_on date
  // daysLeft number calculated
  //countClient number calculated
  //countProj number calculated
  //countEquipment number calculated
  //countUsers number calculated
  //countAdmins number calculated
  //countQR number calculated
  //countTrans number calculated
  //countDevices number calculated
  allow_search boolean
  max_transactions_shown int
  allow_levels boolean
  transaction_sync_default_hours int
  allow_batch_odo boolean
  connection_drop varchar //choice onPumpStop	onDispSummary
  query_size int
  enable_transaction_notes boolean
  transaction_notes_name varchar
  transaction_notes_placeholder varchar
  report_damage boolean
  options json // autoconnect etc
  allow_custom_qr boolean
  org_groups varchar [ref: > org_groups.id] // many to many
  number_of_seats int // nSubs, calculated
  max_transactions_per_day int
  days_since_last_transaction int
  can_scan_tanks boolean
  enable_transaction_notes_2 boolean
  notes_2_field_name varchar
  notes_2_field_placeholder varchar
}

// Primary types of equipment
// MANY TO MANY WITH ORG
Table equipment_types {
  id uuid [pk]
  name varchar
  description varchar
  is_global boolean
  tenant_id uuid
}

// onroad offroad fixed etc
Table equipment_modes {
  id varchar [pk]
  name varchar
  description varchar
  tenant_id uuid
}

// Kilometers Miles etc
Table mileage_unit_of_measures {
  id varchar [pk]
  unit_of_measure varchar
  label varchar
}

// primary equipment
Table equipments {
  id varchar [pk]
  name varchar
  make varchar
  model varchar
  serial varchar
  rego varchar
  asset varchar
  number_id int [increment]
  is_tank boolean
  customer_id uuid [ref: > orgs.id]
  client_id uuid [ref: > clients.id]
  user_id uuid [ref: > users.id]
  equipment_type_id uuid [ref: < equipment_types.id]
  type_custom varchar
  last_mileage int
  mileage_unit_of_measure_id uuid [ref: > mileage_unit_of_measures.id]
  equipment_mode_id uuid [ref: - equipment_modes.id]
  active boolean
  last_write datetime
  manufacture_year int
  vin varchar
  cost_center varchar
  ein varchar
  last_odo_update datetime
  org_id varchar [ref: > org.id]
  org_group_id varchar [ref: > org_groups.id]
  // 
  created_at datetime
  updated_at datetime
  created_by_id uuid [ref: < users.id]
  updated_by_id uuid [ref: < users.id]
  deleted_by_id uuid [ref: < users.id]
 }

// fluid compartments per equipment
Table compartments {
  id uuid [pk]
  name varchar
  equipment_id uuid [ref: > equipments.id]
  fluid_id uuid [ref: > fluids.id]
  volume int
  fluid_uom varchar
  preset boolean
  preset_volume int
  active boolean
  qr_code varchar // QRLink
  source varchar
  createdBy varchar // "web" or mobile
  org_id uuid [ref: > orgs.id]
  qr_code_added_datetime datetime
  qr_author varchar // leave as varchar
  qr_in_use boolean
  qr_only boolean // to remove
}

Table qr_codes {
  id uuid [pk]
  number_id number
  label_id number
  code varchar
  file_name varchar
  pack varchar
  org_id int [ref: > orgs.id] //replace with qrhead ref
  attached_datetime date
  added_to_entity_datetime date
  brand varchar
  client_id int [ref: > clients.id]
  equipment_id int [ref: > equipments.id]
  compartment_id int [ref: > compartments.id]
  tank_id int [ref: > tanks.id]
  allocated boolean
  file varchar
  // ADD qrHeader LATER
  qrHeader int [ref: > QRHeader.qrheadId] //not built in knack
  
  source_id number
  active boolean
  deleted_by_user varchar
  assigned_type varchar //choice none, compartment, tank
}

// TO BE ADDED LATER
Table QRHeader {
  id uuid [pk]
  header varchar
  code_count int
  customer int [ref: > customers.id]
}

//fluids - consumables in greaseboss
// MANY TO MANY WITH ORG
Table fluids {
  id uuid [pk]
  name varchar
  description varchar
  color varchar
  is_global boolean
}

//qrcode RFID, ble etc
// DON'T DO THIS, IT'S FOR LATER
Table identifierType {
  id varchar [pk]
  name varchar
  description varchar
}

// actual idetifier. Qrcode or RFID string
// DON'T DO THIS, IT'S FOR LATER
Table identifier {
  identifierUuid varchar [pk]
  compUuid varchar [ref: > compartment.CompartmentUUID]
  identifierType varchar [ref: - identifierType.idTypeName]
  identifierId int [increment]
  identifierString varchar
  identifierActive boolean
  identifierAdded datetime
}

Table resellers {
  id uuid [pk]
  name varchar
  address varchar
  active boolean
}

Table clients {
  id uuid [pk]
  org_id uuid [ref: < orgs.id]
  client_number int
  name varchar
  description varchar
  location varchar
  address varchar
  is_active boolean
  default_client boolean
}

Table sites {
  id uuid [pk]
  site_number int
  name varchar
  client_id varchar [ref: > clients.id]
  address varchar
  description varchar
  is_active boolean
}

Table projects {
  id uuid [pk]
  name varchar
  project_number varchar
  job varchar
  client varchar
  cost_center varchar
  location varchar
  geolocation geolocation
  reference varchar
  is_active boolean
  org_id varchar [ref: > orgs.id]
  external_id varchar
}

Table devices {
  id uuid [pk]
  internal_name varchar
  device_number number
  org_id int [ref: > orgs.id]
  broadcast_id varchar // dev_id
  alias varchar // devClientName
  model int [ref: > models.id]
  serial_number varchar
  firmware_version varchar
  fluid_name varchar
  fluid_id uuid [ref: > fluids.id]
  is_active boolean
  fluid_uom varchar // liters or gallons
  pulse_make varchar
  pulse_model varchar
  pulse_k_factor double // needs at least 6
  pulse_k_factor_unit int
  notes varchar
  precision int
  estop_installed boolean
  level_installed boolean
  over_ride_installed boolean
  bt_type varchar // to remove
  volume_strapping_table json
  commission_date date
  install varchar // portable or fixed
  event_sync_type int // list of event sync options TBA
  hometown_time_zone varchar //list of timezones
  description varchar
  defualt_latitude double
  default_longitude double
  hold_connect boolean // to remove
  update_on_connection boolean
  fuellox_ini_file_source varchar
  secure_key varchar
  reference varchar // double check needed
  calibrator_id uuid [ref: > calibrators.id]
  last_calibration_datetime datetime
  last_level_volume int
  last_level_recorded_datetime datetime
  // Start Here ---------------------------------------------------
  volume_capacity int
  ullage int
  // dip_age number: calculated
  app_connection_timeout_ms int
  // transaction_count number calculated
  connection_range_warning int
  connection_range_disconnect int // cnxRngTerminate
  collect_meta int
  transaction_resume_timeout_ms int
  config_updated date
  iotiq_io_device_active boolean // to remove
  io_device_id varchar // to remove
  io_device_access_token varchar // to remove
  tank_name varchar
  reseller_id int [ref: > resellers.id]
  allow_auto_connect boolean
  level_percentage double
  level_warning boolean // will be calculated
  level_reorder boolean // will be calculated
  // warning_state boolean calculated
  // reorder_state boolean calculated
  org_group_id int [ref: > org_groups.id]
  // MaxTransDayDevice number calculated
  // todayDay number calculated
  // DaysSinceLastTrans number calculated
}

Table transactions {
  id uuid [pk]
  transaction_number int
  org_id int [ref: > orgs.id]
  app_transaction_id varchar
  device_local_number int
  device_number int
  client_id int [ref: > clients.id]
  user_number int
  notes_a varchar
  equipment_id int [ref: > equipments.id]
  compartment_id int [ref: > compartments.id]
  event_datetime date
  // Change to geolocation?
  lat float
  long float
  pulse_count int
  raw_volume float
  event_k_factor float
  volume_uom varchar // liters or gallons
  termination_state int
  state_auth varchar
  fluid_name varchar // Fluid
  fluid_id int [ref: > fluids.id]
  odo int
  odo_uom varchar
  last_odo int
  odo_delta int
  notes_b varchar
  compartment_name varchar
  timezone varchar
  project_id int [ref: > projects.id]
  site_id int [ref: > sites.id]
  gps_address varchar
  calculated_level_start int //lvl_dip_in
  calculated_level_end int //lvl_dip_out
  level_sensor_start int // lvl_sens_in
  level_sensor_end int // lvl_sens_out
  level_time_start datetime //lvl_time_in
  level_time_end datetime //lvl_time_out
// start here....
  level_delta int
  device_id int [ref: > devices.id]
  user_id int [ref: > users.id]
  transaction_type_id int [ref: > transaction_types.id]
  // Month number // calculated
  // Year number // calculated
  // Day number // calculated
  // Period_month varchar // calculated
  // Client_Site varchar // calculated
  // Quarter number // calculated
  // Period_qtr varchar // calculated
  mode varchar
  number_of_seconds_to_complete int
  flow_rate double // will be calculated
  app_version varchar
  termination_code varchar // termReason
  validated boolean
  gps_accuracy varchar
  hardwares_firmware_version varchar
  meta_data json
  multiplier int // only +1 or -1
  archive boolean
  calendar_view varchar
  // week number // calculated
  // Week_Yr varchar // calculated
  cost_center varchar
  total_cost double
  cents_per_liter double // will later become calculated
  tank_id int [ref: > tanks.id]
  volume double // will later be calculated
  purchase_site varchar
  asset_number varchar
  equipment_type varchar
  thisDayLastYr int // will later be calculated
  thisMonthVol int // will later be calculated
  lastMTDVol int // will later be calculated
  lastMonthVol int // will later be calculated
  thisQtrVol int // will later be calculated
  lastQTDVol int // will later be calculated
  lastQtrVol int // will later be calculated
  thisYearVol int // will later be calculated
  lastYearVol int // will later be calculated
  lastYTDVol int // will later be calculated
  org_groups int [ref: > org_groups.id]
  project_cost_center varchar
  equipment_cost_center varchar
}

Table tanks {
  id uuid [pk]
  name varchar
  tank_number int
  org_id int [ref: > orgs.id]
  device_id int [ref: > devices.id]
  location varchar
  capacity int
  fluid_uom varchar // L or Gallon
  make varchar
  is_active boolean
  org_tank_name varchar
  fluid_id int [ref: > fluids.id]
  serial varchar
  is_device_enabled boolean // workout how relationship between tank and device will work
  // cumulative_total number // calculated
  qr_scan_active boolean // to be calculated
  qr_code varchar
  date_qr_code_added datetime
}

Table org_groups {
  knid uuid [pk]
  name varchar
  address varchar
  primary_contact_name varchar
  office_phone varchar
  mobile_phone varchar
  can_view_transactions boolean // authTrans
  can_view_equipment boolean // authEquip
  can_view_levels boolean // authLevels
  can_view_reports boolean // authReports
  reseller_id int [ref: > resellers.id]
  // subCusts number // will be calculated as "active_seats"
}

Table device_models {
  id uuid [pk]
  name varchar
  model_number number
  reference varchar
  description varchar // becomes mod_name
  board varchar
  firmware varchar
  enclosure_type varchar
  status varchar
  is_active boolean // to be added
  notes varchar
}

Table calibration_orgs {
  id uuid [pk]
  calibrator_number int
  name varchar
  address varchar
  phone varchar
  main_contact varchar
}

Table users {
  id uuid [pk]
  email varchar
  first_name varchar
  last_name varchar
  password varchar
  tenant_id uuid [ref: > tenants.id]
  org_id uuid [ref: > org.id]
}

Table transaction_types {
  id uuid [pk]
  name varchar //Dispense	Transfer	ManualDispense	Adjust	Purchase	Logbook
  multiplier int // +1 or -1
}