
-- admin migration
CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('president','dm') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Helpful indexes for dashboard
ALTER TABLE residents
  ADD INDEX IF NOT EXISTS idx_residents_ward (ward),
  ADD INDEX IF NOT EXISTS idx_residents_waste (waste_segregation);

ALTER TABLE trainings
  ADD INDEX IF NOT EXISTS idx_trainings_resident (resident_id),
  ADD INDEX IF NOT EXISTS idx_trainings_num (training_number),
  ADD INDEX IF NOT EXISTS idx_trainings_ws (waste_segregation);
