= Relation Design
== Notation ==
- PK marked with [PK]
- FK marked with [FK→Table(column)]
- UQ = unique constraint
== Relation Schema ==

{{{ USERS + SUBTYPES }}}

- Users(
  user_id [PK],
  username [UQ],
  email [UQ],
  name,
  surname,
  password_hash,
  created_at)

- Admin(
  user_id [PK, FK→Users(user_id)])

- Client(
  user_id [PK, FK→Users(user_id)],
  is_blocked,
  blocked_at,
  blocked_reason,
  blocked_by [FK→Admin(user_id)])

- Owner(
  user_id [PK, FK→Client(user_id)])

- VetClinic(
  clinic_id [PK],
  user_id [FK→Users(user_id), UQ])

VetClinic is also a specialization of Users. It keeps its own clinic_id as the primary key, while user_id connects it to the Users supertype.

----

{{{ CLIENT INTERACTIONS }}}

- ClientInteraction(
  source_client_id [PK, FK→Client(user_id)],
  target_client_id [PK, FK→Client(user_id)],
)

This represents the recursive Client–Client relationship `interaction_with`.

----

{{{ NOTIFICATIONS }}}

- Notification(
  notification_id [PK],
  user_id [FK→Users(user_id)],
  type,
  message,
  is_read,
  created_at)

----

{{{ VET CLINIC APPLICATIONS }}}

- VetClinicApplication(
  application_id [PK],
  clinic_id [FK→VetClinic(clinic_id), UQ],
  name,
  email,
  phone,
  city,
  address,
  submitted_at,
  status,
  reviewed_at,
  reviewed_by [FK→Admin(user_id)],
  denial_reason)

----

{{{ VET CLINICS }}}

- VetClinic(
  clinic_id [PK],
  user_id [FK→Users(user_id), UQ],
  application_id [FK→VetClinicApplication(application_id), UQ],
  name,
  email,
  phone,
  city,
  address,
  location,
  work_days,
  start_time,
  end_time)

----

{{{ ANIMALS }}}

- Animal(
  animal_id [PK],
  owner_id [FK→Owner(user_id)],
  name,
  sex,
  date_of_birth,
  photo_url,
  type,
  species,
  breed,
  located_name)

----

{{{ LISTINGS }}}

- Listing(
  listing_id [PK],
  owner_id [FK→Owner(user_id)],
  animal_id [FK→Animal(animal_id)],
  status,
  price,
  description,
  created_at)

- FavoriteListing(
  fav_listing_id [PK],
  user_id [FK→Users(user_id)],
  listing_id [FK→Listing(listing_id)])

----

{{{ APPOINTMENTS }}}

- Appointment(
  appointment_id [PK],
  clinic_id [FK→VetClinic(clinic_id)],
  animal_id [FK→Animal(animal_id)],
  responsible_owner_id [FK→Owner(user_id)],
  status,
  date_time,
  notes)

----

{{{ REVIEWS: SUPERTYPE + SUBTYPES }}}

- Review(
  review_id [PK],
  reviewer_id [FK→Client(user_id)],
  rating,
  comment,
  created_at,
  updated_at,
  is_deleted)

- UserReview(
  review_id [PK, FK→Review(review_id)],
  target_user_id [FK→Users(user_id)],
  interaction_type)

- ClinicReview(
  review_id [PK, FK→Review(review_id)],
  target_clinic_id [FK→VetClinic(clinic_id)],
  appointment_id [FK→Appointment(appointment_id)])

----

{{{ HEALTH RECORDS }}}

- HealthRecord(
  healthrecord_id [PK],
  animal_id [FK→Animal(animal_id)],
  appointment_id [FK→Appointment(appointment_id)],
  type,
  description,
  date)

----

{{{ CLINIC UNAVAILABLE SLOTS }}}

- ClinicUnavailableSlot(
  slot_id [PK],
  clinic_id [FK→VetClinic(clinic_id)],
  date_time,
  reason,
  created_at)

----

== Business Rules Enforced in DDL / Triggers ==

[[BR]]
Review.rating must be in range 1–5.
[[BR]]
Review specialization is disjoint: one Review can be either UserReview or ClinicReview, not both.
[[BR]]
A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
[[BR]]
A Client can leave a UserReview only for another Client/User with whom they have an existing ClientInteraction.
[[BR]]
ClientInteraction is recursive: both source_client_id and target_client_id reference Client(user_id).
[[BR]]
A Client cannot have an interaction with themselves.
[[BR]]
HealthRecord consistency: HealthRecord.animal_id must match Appointment.animal_id.
[[BR]]
UserReview.interaction_type must be one of the allowed interaction types defined in the DDL.
== DDL
 * [attachment:schema_creation.sql]
== DML
* [attachment:data_load.sql]
== Relational diagram
[[Image(relational_schema.jpg)]]