CREATE DATABASE IF NOT EXISTS misa_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE misa_db;
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  fullname VARCHAR(200) NOT NULL,
  email VARCHAR(200) DEFAULT NULL,
  phone VARCHAR(50) DEFAULT NULL,
  role ENUM('member','kadiv','admin') DEFAULT 'member',
  group_no INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_date DATE NOT NULL,
  event_time TIME NOT NULL,
  description VARCHAR(255) DEFAULT 'Misa',
  group_no INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  schedule_id INT NOT NULL,
  user_id INT NOT NULL,
  role_in_misa VARCHAR(100) DEFAULT 'PA',
  status ENUM('assigned','confirmed','absent','substitute') DEFAULT 'assigned',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE absence_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_id INT NOT NULL,
  schedule_id INT NOT NULL,
  reason TEXT,
  preferred_substitute_id INT DEFAULT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  kadiv_id INT DEFAULT NULL,
  substitute_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (schedule_id) REFERENCES schedules(id)
);
CREATE TABLE reminder_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  schedule_id INT,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(255)
);