SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS doctors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  specialty VARCHAR(120) NOT NULL DEFAULT '',
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  gemini_api_key TEXT NULL,
  gemini_model VARCHAR(100) NOT NULL DEFAULT '',
  agent_prompt TEXT NULL,
  voice VARCHAR(30) NOT NULL DEFAULT 'Kore',
  clinic_address TEXT NULL,
  clinic_phone VARCHAR(40) NOT NULL DEFAULT '',
  extra_notes TEXT NULL,
  bg_image VARCHAR(120) NULL,
  booking_enabled TINYINT(1) NOT NULL DEFAULT 0,
  slot_minutes SMALLINT NOT NULL DEFAULT 20,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doctor_id INT NOT NULL,
  weekday TINYINT NOT NULL COMMENT '0=Sunday .. 6=Saturday (PHP date w)',
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  KEY (doctor_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS days_off (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doctor_id INT NOT NULL,
  off_date DATE NOT NULL,
  UNIQUE KEY (doctor_id, off_date),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS appointments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doctor_id INT NOT NULL,
  appt_date DATE NOT NULL,
  appt_time TIME NOT NULL,
  patient_name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  national_id VARCHAR(10) NOT NULL,
  birth_date VARCHAR(12) NOT NULL,
  insurance VARCHAR(80) NOT NULL,
  status ENUM('booked','done','cancelled') NOT NULL DEFAULT 'booked',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (doctor_id, appt_date, appt_time),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(45) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(50) PRIMARY KEY,
  v TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;