-- Schema for EEO discrimination intake form

CREATE TABLE IF NOT EXISTS eeo_intake (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(40) NULL,
  employer VARCHAR(255) NOT NULL,
  job_title VARCHAR(150) NULL,
  work_location VARCHAR(255) NULL,
  incident_start_date DATE NOT NULL,
  incident_end_date DATE NULL,
  issue_type VARCHAR(120) NOT NULL,
  description TEXT NOT NULL,
  witnesses TEXT NULL,
  requested_remedy TEXT NULL,
  attest TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT ck_attest CHECK (attest IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS eeo_intake_discrimination_basis (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  intake_id BIGINT UNSIGNED NOT NULL,
  basis VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_intake_id (intake_id),
  CONSTRAINT fk_basis_intake
    FOREIGN KEY (intake_id) REFERENCES eeo_intake(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS eeo_intake_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  intake_id BIGINT UNSIGNED NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  content_type VARCHAR(120) NOT NULL,
  file_size_bytes BIGINT UNSIGNED NOT NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_intake_file_intake_id (intake_id),
  CONSTRAINT fk_files_intake
    FOREIGN KEY (intake_id) REFERENCES eeo_intake(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
