CREATE DATABASE IF NOT EXISTS dr_ahrari_bot
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE dr_ahrari_bot;

CREATE TABLE IF NOT EXISTS users (
  telegram_id BIGINT PRIMARY KEY,
  username VARCHAR(255) DEFAULT NULL,
  first_name VARCHAR(255) DEFAULT NULL,
  state VARCHAR(64) NOT NULL DEFAULT 'idle',
  temp_name VARCHAR(255) DEFAULT NULL,
  temp_phone VARCHAR(32) DEFAULT NULL,
  temp_doctor_id VARCHAR(64) DEFAULT NULL,
  temp_service_id VARCHAR(64) DEFAULT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS registrations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL,
  username VARCHAR(255) DEFAULT NULL,
  full_name VARCHAR(255) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  doctor_id VARCHAR(64) NOT NULL,
  doctor_name VARCHAR(255) NOT NULL,
  service_id VARCHAR(64) NOT NULL,
  service_name VARCHAR(255) NOT NULL,
  time_slot VARCHAR(64) NOT NULL,
  time_slot_label VARCHAR(128) NOT NULL,
  appointment_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_created_at (created_at),
  INDEX idx_telegram_id (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;