Model

// Creating tables
Table Users as U {
  id int [pk, increment] // auto-increment
	username varchar
	password varchar 
	last_login timestampt
	first_name varchar 
	last_name varchar
	email varchar 
	is_active boolean 
  institution_id int
  updatedAt timestamp
  createdAt timestamp
}

Table Role_to_User {
  user_id int
  role_id int
  updatedAt timestamp
  createdAt timestamp
}


Table Nodes as N {
  id int [pk, increment] // auto-increment
  //ID humano que Pedro usa para identificar 2_14_0_683
  nomenclatura varchar  
  con_socio int
  fid int
  location point
	cat_itegr varchar // Es una integridad para modulo
	cumulus_id int
	ecosystem_id int
  updatedAt timestamp
  createdAt timestamp
}

Table Institutions as I {
  id int [pk, increment] // auto-increment
  name varchar
  address varchar
  phone_number int
  updatedAt timestamp
  createdAt timestamp
}

Table Ecosystems as E {
  id int [pk, increment] // auto-increment
  name varchar [not null, unique]
  updatedAt timestamp
  createdAt timestamp
}

Table Cumulus as C {
  id int [pk, increment] // auto-increment
  name varchar
  geometry polygon
  ecosystem_id int
  con_socio int
  criteria_id id
  updatedAt timestamp
  createdAt timestamp
}

Table Cumulus_criteria as CC {
  id int [pk, increment] // auto-increment
  name varchar
  updatedAt timestamp
  createdAt timestamp
}


Table Visits as V {
  id int [pk, increment] // auto-increment
  user_id int
  calendar_id int
  comments varchar
  date_sipecam_first_season Date
  date_sipecam_second_season Date
  date_first_season Date
  date_second_season Date
  report_first_season varchar
  report_second_season varchar
  cumulus_id int
  pristine_id int
  disturbed_id int
  monitor_ids [int]
  updatedAt timestamp
  createdAt timestamp
}



// Posibles Roles
// Socio
// Admin
// 
Table Roles as R {
  id int [pk, increment] // auto-increment
  name varchar
  description varchar
  updatedAt timestamp
  createdAt timestamp
}



Table Physical_Devices as D {
  id int [pk, increment] // auto-increment
  serial_number varchar
  comments varchar
  device_id int
  cumulus_id int
  updatedAt timestamp
  createdAt timestamp
}

Table Devices_Catalog as DC {
  id int [pk, increment] // auto-
  brand varchar
  type varchar
}

Table Calendar as Cal {
  id int [pk, increment] // auto-increment
  date_started date
  date_finished date
  sipecam_year varchar
  order int
  updatedAt timestamp
  createdAt timestamp
}


Table Deployments as Dep {
  id int [pk, increment] // auto-increment
  date_deployment date
  latitude float8
  longitude float8
  altitude float8
  comments varchar
  metadata json
  kobo_url varchar
  device_id int
  node_id int
  cumulus_id int
  updatedAt timestamp
  createdAt timestamp
}


Table Files {
  id int
  mime_type varchar // mime type
  url varchar //s3 o glacier url
  id_alfresco varchar
  storage varchar //s3 or glacier
  deployment_id int
  updatedAt timestamp
  createdAt timestamp
}


Table Annotations_Geom_Obs_Type{
  id int
  classified_by varchar //megaD, Rekog, yuntu
  classification_method varchar // human o machine
  observation_type varchar //vehicle, animal, human, empty, no clasified
  confidence float8 //resultado de modelo
  pipeline_id int //relación con tabla Pipeline_Info
  file_id   int
  geometry GeometryCollection 
  video_frame_num int // para ubicar dentro del video la anotación
  frequency_min float8
  frequency_max float8
  time_min float8
  time_max float8
  updatedAt timestamp
  createdAt timestamp
}

// Relaciones Uno a Muchos
Ref: "Files"."id" < "Annotations_Geom_Obs_Type"."file_id"

Table Pipeline_Info{
  id int
  version varchar //model version name
  commit_dvc_of_data_ref varchar //id commit in dvc for data referen
  url_repo_model varchar //descripción del modelo usado y código
  createdAt timestamp
  updatedAt timestamp
  comments varchar //clasification description: better for humans, animals, empty ?
}

// Relaciones Muchos a uno
Ref: "Annotations_Geom_Obs_Type"."pipeline_id" > "Pipeline_Info"."id"

Table Products{
  id int [pk, increment]
  file_id int
  type varchar //video, audio, image, spectrogram, image_without_bboxes_from_video
  url varchar //Unique, ruta en s3
  pipeline_id int
  observation_type varchar //animal, empty, person
  producer varchar //CONABIO
  project varchar //SiPeCaM
  metadata json // bboxes list of every animal in image, simex_metadata
  createdAt timestamp
  updatedAt timestamp
  comments varchar //methodology, how to cite, clasification description: better for humans, animals, empty ?
}

// Relaciones Uno a Muchos
Ref: "Files"."id" < "Products"."file_id"
// Relaciones Muchos a uno
Ref: "Products"."pipeline_id" > "Pipeline_Info"."id"



Table Individual as Ind {
  id int [pk, increment] // auto-increment
  date_trap date
  latitude float8
  longitude float8
  altitude float8
  comments varchar
  metadata json //morfometría de roedor
  kobo_url varchar
  clave_posicion_malla varchar
  arete int
  node_id int
  cumulus_id int
  updatedAt timestamp
  createdAt timestamp
}


Table PartnersCumulus as Pacu {
  id int [pk, increment] // auto-increment
  user_id int
  cumulus_id int
  updatedAt timestamp
  createdAt timestamp
}

// Establecer Relaciones
// Un cumuluo muchos monitores
Table Monitors as Mon {
 id int [pk, increment] // auto-increment
 deployments_id int
 cumulus_id int
 first_name varchar
 last_name varchar
 second_last_name varchar
 contact varchar
 visit_ids [int]
  updatedAt timestamp
  createdAt timestamp
}

Table Transecto{
  id int [pk, increment]
  number int //número del Transecto
  sum_vegetation_structure float8
  sum_indicator_species float8
  sum_impact float8
  date_transecto timestamp
  latitude float8 //of node
  longitude float8 //of node
  percentage float8
  node_id int
}

Table delivered_files {
  id int [pk,increment]
  who_delivers varchar
  reception_date date
  total_files int
  audio_files int
  image_files int
  node_id int
}

// Relaciones uno a uno
Ref: V.(calendar_id) - Cal.(id)

// Relaciones Muchos a Uno
Ref: N.(cumulus_id) > C.(id)
Ref: D.(device_id) > DC.id
Ref: N.(ecosystem_id) > E.(id)
Ref: C.(criteria_id) > CC.(id)

// Relaciones Uno a Muchos
Ref: Pacu.(user_id) < U.(id)
Ref: Pacu.(cumulus_id) < C.(id)
Ref: V.(user_id) < U.(id)
Ref: I.(id) < U.(institution_id)
Ref: C.(id) < Mon.(cumulus_id)
Ref: C.(id) < V.(cumulus_id)
Ref: C.(id) < D.(cumulus_id)


Ref: "Physical_Devices"."id" < "Deployments"."device_id"

Ref: "Role_to_User"."user_id" < "Users"."id"

Ref: "Role_to_User"."role_id" < "Roles"."id"

Ref: "Nodes"."id" < "Visits"."pristine_id"

Ref: "Nodes"."id" < "Visits"."disturbed_id"

Ref: "Ecosystems"."id" < "Cumulus"."ecosystem_id"

Ref: "Files"."deployment_id" < "Deployments"."id"

Ref: "Cumulus"."id" < "Deployments"."cumulus_id"

Ref: "Nodes"."id" < "Deployments"."node_id"

Ref: "Nodes"."id" < "Ind"."node_id"

Ref: "Cumulus"."id" < "Ind"."cumulus_id"

Ref: "Transecto"."node_id" < "Nodes"."id"

Ref: "Visits"."monitor_ids" < "Monitors"."id"

Ref: "Monitors"."visit_ids" < "Visits"."id"

Ref: "Nodes"."id" < "delivered_files"."node_id"