-- Minimal DB schema for MVP

CREATE DATABASE IF NOT EXISTS medbooking CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE medbooking;

-- users/practitioners
CREATE TABLE practitioners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(191) NOT NULL UNIQUE,
  phone VARCHAR(20),
  name VARCHAR(100),
  password_hash VARCHAR(255),
  -- autres colonnes...
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- patients
CREATE TABLE IF NOT EXISTS patients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(255),
  consent_sms TINYINT(1) DEFAULT 1,
  cancel_count_day INT DEFAULT 0,
  cancel_count_month INT DEFAULT 0,
  last_cancel_date DATE DEFAULT NULL,
  blocked_until DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- appointments
CREATE TABLE IF NOT EXISTS appointments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practitioner_id INT NOT NULL,
  patient_id INT NOT NULL,
  start DATETIME NOT NULL,
  end DATETIME NOT NULL,
  status ENUM('booked','confirmed','cancelled','noshow') DEFAULT 'booked',
  created_by ENUM('patient','pro','admin') DEFAULT 'patient',
  cancellation_token VARCHAR(128),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (practitioner_id) REFERENCES practitioners(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- sms logs
CREATE TABLE IF NOT EXISTS sms_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practitioner_id INT,
  appointment_id INT NULL,
  phone VARCHAR(30),
  message TEXT,
  status VARCHAR(50),
  provider_message_id VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- patient blocks (manuels)
CREATE TABLE IF NOT EXISTS patient_blocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practitioner_id INT NOT NULL,
  patient_id INT NOT NULL,
  reason VARCHAR(255),
  blocked_until DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- availabilities (disponibilités)
CREATE TABLE IF NOT EXISTS availabilities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practitioner_id INT NOT NULL,
  day_of_week ENUM('monday','tuesday','wednesday','thursday','friday','saturday','sunday') NULL,
  specific_date DATE NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  slot_duration INT NOT NULL DEFAULT 30,
  is_recurring TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (practitioner_id) REFERENCES practitioners(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- practitioner settings
CREATE TABLE IF NOT EXISTS practitioner_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  practitioner_id INT NOT NULL UNIQUE,
  ovh_app_key VARCHAR(255),
  ovh_app_secret VARCHAR(255),
  ovh_consumer_key VARCHAR(255),
  ovh_service_name VARCHAR(100),
  sms_sender VARCHAR(11),
  smtp_host VARCHAR(255),
  smtp_port INT DEFAULT 587,
  smtp_encryption ENUM('tls','ssl','none') DEFAULT 'tls',
  smtp_username VARCHAR(255),
  smtp_password VARCHAR(255),
  email_from VARCHAR(255),
  msg_confirmation TEXT,
  msg_reminder TEXT,
  msg_cancellation TEXT,
  auto_reminder TINYINT(1) DEFAULT 1,
  notify_new_booking TINYINT(1) DEFAULT 1,
  notify_cancellation TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (practitioner_id) REFERENCES practitioners(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
