-- 3. STAFF & GUARDIAN PROFILES
-- ---------------------------------------------------------------------

CREATE TABLE staff_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  staff_no VARCHAR(30) NOT NULL,
  firstname VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  othername VARCHAR(50) DEFAULT NULL,
  dob DATE DEFAULT NULL,
  gender ENUM('male','female') DEFAULT NULL,
  state_id INT UNSIGNED DEFAULT NULL,
  lga_id INT UNSIGNED DEFAULT NULL,
  address TEXT,
  qualification VARCHAR(100) DEFAULT NULL,
  passport_url VARCHAR(255) DEFAULT NULL,
  employment_date DATE DEFAULT NULL,
  employment_status ENUM('active','on_leave','terminated','retired') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uq_staff_no (staff_no),
  CONSTRAINT fk_staffprofile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_staffprofile_state FOREIGN KEY (state_id) REFERENCES states(id),
  CONSTRAINT fk_staffprofile_lga FOREIGN KEY (lga_id) REFERENCES lgas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE guardians (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,   -- NULL until guardian activates portal access
  fullname VARCHAR(150) NOT NULL,
  occupation VARCHAR(100) DEFAULT NULL,
  phone VARCHAR(20) NOT NULL,
  alt_phone VARCHAR(20) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  address TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guardian_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;