// Directus Database Schema for Mayo
// Revised schema proposal
// Updated: 2026-05-20

Table mayo_clients {
  id integer [primary key]
  name "character varying" [unique, not null]
  country "character varying"
}

Table mayo_colors {
  id integer [primary key]
  name "character varying" [unique, not null]
}

Table mayo_finishes {
  id integer [primary key]
  code "character varying" [unique, not null]
  name "character varying" [not null]
}

Table mayo_neck_constructions {
  id integer [primary key]
  code "character varying" [unique, not null]
  name "character varying" [not null]
}

Table mayo_part_types {
  id integer [primary key]
  code "character varying" [unique, not null]
  name "character varying" [not null]
}

Table mayo_product_models {
  id integer [primary key]
  name "character varying" [unique, not null]
  neck_construction_id integer [not null]
}

Table mayo_products {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  source_url "character varying"
  specification json
  specification_last_fetched_at timestamp
  product_model_id integer [not null]
}

Table mayo_orders {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  order_number "character varying" [not null]
  order_year integer [not null]
  client_id integer [not null]
}

Table mayo_order_items {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  product_id integer [unique, not null]
  order_id integer [not null]
  order_index integer [not null]
}

Table mayo_production_lists {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  name "character varying" [unique, not null]
  description text
}

Table mayo_product_production_lists {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  order_item_id integer [not null]
  production_list_id integer [not null]
}

Table mayo_product_parts {
  id integer [primary key]
  product_id integer [not null]
  part_type_id integer [not null]
  top_color_id integer
  back_color_id integer
  top_finish_id integer
  back_finish_id integer
}

Table mayo_production_operations {
  id integer [primary key]
  code "character varying" [unique, not null]
  name "character varying" [unique, not null]
  description text
}

Table mayo_production_events {
  id integer [primary key]
  user_created uuid
  date_created timestamp
  user_updated uuid
  date_updated timestamp
  product_part_id integer [not null]
  ordinal integer [not null]
  event_date date [not null]
  operation_id integer
  note text
}

// Relationships
Ref: mayo_product_models.neck_construction_id > mayo_neck_constructions.id
Ref: mayo_products.product_model_id > mayo_product_models.id
Ref: mayo_orders.client_id > mayo_clients.id
Ref: mayo_order_items.product_id - mayo_products.id
Ref: mayo_order_items.order_id > mayo_orders.id
Ref: mayo_product_production_lists.order_item_id > mayo_order_items.id
Ref: mayo_product_production_lists.production_list_id > mayo_production_lists.id
Ref: mayo_product_parts.product_id > mayo_products.id
Ref: mayo_product_parts.part_type_id > mayo_part_types.id
Ref: mayo_product_parts.top_color_id > mayo_colors.id
Ref: mayo_product_parts.back_color_id > mayo_colors.id
Ref: mayo_product_parts.top_finish_id > mayo_finishes.id
Ref: mayo_product_parts.back_finish_id > mayo_finishes.id
Ref: mayo_production_events.product_part_id > mayo_product_parts.id
Ref: mayo_production_events.operation_id > mayo_production_operations.id

// Suggested seed values
//
// mayo_neck_constructions:
// - NTB: Neck-through-body
// - BOLT_ON: Bolt-on
// - SET_IN: Set-in
//
// mayo_part_types:
// - BODY: Body
// - NECK: Neck
//
// mayo_finishes:
// - GLOSS: Gloss
// - SATIN: Satin
// - MAT: Mat
// - NITRO: Nitro