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

Table users {
  id integer [pk]
  email varchar
  first_name varchar
  last_name varchar
  oidc_id varchar

  indexes {
    id
  }
}

Table orgusers {
  org_id integer
  user_id integer
  is_admin bool

  indexes {
    (org_id, user_id) [pk]
  }
}

Table organisations {
  id integer [pk]
  name varchar
  status varchar
  intake_questionaire json
  billing_contact_id integer
  security_contact_id integer
  owner_contact_id integer

  indexes {
    id
  }
}

Table contacts {
  id integer [pk]
  email varchar
  first_name varchar
  last_name varchar
  phonenumber varchar
  vat_number varchar
  address varchar
  city varchar
  country varchar
  postcode varchar

  indexes {
    id
  }
}

Table products {
  id integer [pk]
  name varchar
  price float
  org_id integer

  indexes {
    id
  }
}

Table projects {
  id integer [pk]
  name varchar
  max_billable float
  end_date timestamp
  org_id integer

  indexes {
    id
  }
}

Table projectproductusage {
  id integer [pk]
  project_id integer
  product_id integer
  price float
  start_time timestamp
  end_time timestamp
  invoice varchar

  indexes {
    id
    (project_id, product_id)
  }
}

Ref: organisations.billing_contact_id - contacts.id
Ref: organisations.security_contact_id - contacts.id
Ref: organisations.owner_contact_id - contacts.id

Ref: orgusers.org_id <> organisations.id
Ref: orgusers.user_id <> users.id

Ref: products.org_id > organisations.id

Ref: projects.org_id > organisations.id

Ref: projectproductusage.product_id <> products.id
Ref: projectproductusage.project_id <> projects.id