CREATE TABLE family_users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  birthday DATE NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE family_relationship_types (
  id INT NOT NULL AUTO_INCREMENT,
  relationship_type VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE family_relationships (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  relative_id INT NOT NULL,
  relationship_type_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES family_users (id),
  FOREIGN KEY (relative_id) REFERENCES family_users (id),
  FOREIGN KEY (relationship_type_id) REFERENCES family_relationship_types (id)
);

CREATE TABLE family_posts (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  image_filename VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES family_users (id)
);

CREATE TABLE family_comments (
  id INT NOT NULL AUTO_INCREMENT,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (post_id) REFERENCES family_posts (id),
  FOREIGN KEY (user_id) REFERENCES family_users (id)
);

CREATE TABLE family_messages (
  id INT NOT NULL AUTO_INCREMENT,
  sender_id INT NOT NULL,
  recipient_id INT NOT NULL,
  subject VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  sent_at TIMESTAMP NOT NULL,
  read_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (sender_id) REFERENCES family_users (id),
  FOREIGN KEY (recipient_id) REFERENCES family_users (id)
);
