// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs

// Tradeoffs
// - Sponsorship for the club in generel and for events is in one table; decided by eid = NULL

Table person {
  id integer [primary key]
  email email [not null]
  username varchar
  first_name varchar
  last_name varchar
  created_at timestamp
  // CV in here or generel "person has file" relation
}

Table student_info {
  id integer [primary key]
  pid integer [not null]
  gpa integer
}
Ref: student_info.pid > person.id

Table job_info {
  id integer [primary key]
  pid integer [not null]
}
Ref: job_info.pid > person.id

Table member {
  id integer [primary key]
  ec_email email [not null]
}
Ref: member.id > person.id

Table alumni {
  id integer [primary key]
}
Ref: alumni.id > member.id

// member can have multiple roles per semester
Table member_semester_info {
  id integer [primary key]
  member_id integer [not null]
  role varchar
  semester varchar
}
Ref: member_semester_info.member_id > member.id
Ref: member_semester_info.id > department.id

Table department {
  id integer [primary key]
  name varchar [not null]
  type enum [not null] // committee, initiative, ...
}
// view filter for type = committee / initiative [/ advisor]

// ---------------------------------------------------

Table event {
  id integer [primary key]
  type enum [not null] // internal, ACM, talk, Hackathon, Both, SDD, Launch, ..., other
  start timestamp
  end timestamp
  parent_event integer
}
Ref: event.parent_event > event.id

// Add Luma feedback fields, e.g. x/5 rating & text field
Table person_event {
  pid integer
  eid integer
  primary key (pid, eid)
  role enum [not null]
  description varchar
  person_organisation_association integer
}
Ref: person_event.pid > person.id
Ref: person_event.eid > event.id
Ref: person_event.person_organisation_association > person_organisation_association.primary

// tracking helpers, organizers, etc.
Table helper_shift {
  id integer [primary key]
  pid integer
  eid integer
  role enum
  start timestamp
  end timestamp
}
Ref: helper_shift.eid > event.id
Ref: helper_shift.pid > person.id


// ---------------------------------------

Table organisation {
  id integer [primary key]
}

Table organisation_event {
  oid integer
  eid integer
  primary key (oid, eid)
  role enum
}
Ref: organisation_event.oid > organisation.id
Ref: organisation_event.eid > event.id

Table sponsorship {
  id integer [primary key]
  type enum [not null] // in kind
  eid integer // optional sponsor deal associated with event
}
Ref: sponsorship.id > organisation.id
Ref: sponsorship.eid > event.id

Table person_organisation_association {
  oid integer
  pid integer
  primary key (oid, pid)
  jid integer // job_info id
}
Ref: person_organisation_association.oid > organisation.id
Ref: person_organisation_association.pid > person.id
Ref: person_organisation_association.jid > job_info.id
