-- =====================================================================
-- Moodist IVR — Tek Hastane Randevu Arama Sistemi
-- Veritabanı şeması
-- MySQL 5.7+ / MariaDB 10.3+  (InnoDB, utf8mb4)
-- =====================================================================
-- Tasarım notları:
--  * appointments    : Randevunun GÜNCEL hali (rapor ekranı buradan beslenir).
--  * status_history  : Dış statü geçişleri (Beklemede -> başka statü) kaydı.
--  * call_attempts   : Her arama denemesi ayrı satır (aylık/başarı analizleri için).
--  * sync_runs       : Her senkron çalışmasının özeti.
--  * event_logs      : İnsan-okur olay akışı ("5 değişiklik oldu", "cevap vermedi"...).
--  * hospitals       : Bu web servisin döndürdüğü hastaneler (MOODIST=2, SANA=4).
--  * report_users    : Rapor ekranı kullanıcıları.
-- =====================================================================


-- ---------------------------------------------------------------------
-- hospitals : Bu WS altındaki fiziki hastaneler (HospitalID -> ayar)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `hospitals` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `hospital_id`  INT NOT NULL COMMENT 'Dış API HospitalID (örn 2=MOODIST, 4=SANA)',
  `name`         VARCHAR(255) NOT NULL,
  `callerid`     VARCHAR(32) DEFAULT NULL COMMENT 'Bu hastane için aramada gösterilecek DID',
  `dial_prefix`  VARCHAR(8) NOT NULL DEFAULT '90' COMMENT 'Dış hat çıkış öneki: MOODIST 9->90, SANA 7->70',
  `is_active`    TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=randevuları aranır',
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hospital_id` (`hospital_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Aktif Üsküdar başlangıç verisi
-- dial_prefix '0': dış aramada önek yok, numara 0 + ulusal (05XXXXXXXXX) olarak aranır
INSERT INTO `hospitals` (`hospital_id`, `name`, `callerid`, `dial_prefix`, `is_active`) VALUES
  (2, 'ÖZEL AKTİF İNTERNATİONAL ÜSKÜDAR HASTANESİ', '902163104788', '0', 1)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `callerid`=VALUES(`callerid`), `dial_prefix`=VALUES(`dial_prefix`);

-- ---------------------------------------------------------------------
-- appointments : Randevunun güncel hali
-- ---------------------------------------------------------------------
-- İş anahtarı: appointment_id (dış API'de benzersiz). guid de tutulur.
-- content_hash: değişiklik tespiti için mutable alanların özeti.
-- is_callable : statü/tip/branş filtrelerine göre "aranabilir mi" (sync hesaplar).
-- state       : new | callable | called | answered | not_answered | left_pending | disappeared
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `appointments` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_id`        VARCHAR(64) NOT NULL COMMENT 'Dış API AppointmentID',
  `guid`                  VARCHAR(64) DEFAULT NULL,
  `hospital_id`           INT NOT NULL COMMENT 'Dış API HospitalID',
  `appointment_time`      DATETIME NOT NULL,
  `appointment_type_id`   INT DEFAULT NULL COMMENT '1=Muayene,2=Kontrol,7=eMuayene',
  `appointment_type_name` VARCHAR(255) DEFAULT NULL,
  `status_id`             INT DEFAULT NULL COMMENT '320=Beklemede (aranır)',
  `status_name`           VARCHAR(255) DEFAULT NULL,
  `patient_name`          VARCHAR(255) DEFAULT NULL,
  `patient_last_name`     VARCHAR(255) DEFAULT NULL,
  `patient_phone`         VARCHAR(32) DEFAULT NULL,
  `patient_email`         VARCHAR(255) DEFAULT NULL,
  `patient_address`       VARCHAR(500) DEFAULT NULL,
  `tckno`                 VARCHAR(32) DEFAULT NULL,
  `passport_no`           VARCHAR(32) DEFAULT NULL,
  `upn`                   VARCHAR(64) DEFAULT NULL,
  `department_id`         INT DEFAULT NULL,
  `department_name`       VARCHAR(255) DEFAULT NULL,
  `branch_id`             INT DEFAULT NULL,
  `branch_name`           VARCHAR(255) DEFAULT NULL,
  `doctor_id`             INT DEFAULT NULL,
  `doctor_name`           VARCHAR(255) DEFAULT NULL,
  `process_id`            VARCHAR(64) DEFAULT NULL,
  `process_name`          VARCHAR(255) DEFAULT NULL,
  `process_code`          VARCHAR(128) DEFAULT NULL,
  `content_hash`          CHAR(64) NOT NULL COMMENT 'Değişiklik tespiti (mutable alanlar)',
  `is_callable`           TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Filtrelerden geçti mi',
  `state`                 VARCHAR(24) NOT NULL DEFAULT 'new',
  `last_dtmf`             TINYINT UNSIGNED DEFAULT NULL COMMENT 'Son tuşlama 1/2/3',
  `last_dtmf_at`          DATETIME DEFAULT NULL,
  `last_call_result`      VARCHAR(24) DEFAULT NULL COMMENT 'answered/no_answer/busy/...',
  `hbys_action`           VARCHAR(16) DEFAULT NULL COMMENT 'confirm/cancel (DTMF sonrası HBYS eylemi)',
  `hbys_ok`               TINYINT(1) DEFAULT NULL COMMENT '1=HBYS başarılı, 0=hata',
  `hbys_http_code`        INT DEFAULT NULL COMMENT 'HBYS SOAP yanıt http kodu',
  `hbys_message`          VARCHAR(500) DEFAULT NULL COMMENT 'HBYS yanıt/hata mesajı',
  `hbys_action_at`        DATETIME DEFAULT NULL,
  `called_at`             DATETIME DEFAULT NULL COMMENT 'Son arama başlangıcı',
  `call_attempts`         INT NOT NULL DEFAULT 0,
  `first_seen_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_synced_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `disappeared_at`        DATETIME DEFAULT NULL COMMENT 'API listesinden düştüğü an',
  `created_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appointment` (`appointment_id`),
  KEY `idx_appt_time` (`appointment_time`),
  KEY `idx_status` (`status_id`),
  KEY `idx_callable_time` (`is_callable`,`appointment_time`),
  KEY `idx_state` (`state`),
  KEY `idx_hospital` (`hospital_id`),
  KEY `idx_branch` (`branch_id`),
  KEY `idx_doctor` (`doctor_id`),
  KEY `idx_called_at` (`called_at`),
  KEY `idx_guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- appointment_status_history : Dış statü geçişleri
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `appointment_status_history` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_pk`   BIGINT UNSIGNED NOT NULL COMMENT 'appointments.id',
  `appointment_id`   VARCHAR(64) NOT NULL,
  `old_status_id`    INT DEFAULT NULL,
  `old_status_name`  VARCHAR(255) DEFAULT NULL,
  `new_status_id`    INT DEFAULT NULL,
  `new_status_name`  VARCHAR(255) DEFAULT NULL,
  `sync_run_id`      BIGINT UNSIGNED DEFAULT NULL,
  `changed_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_appt_pk` (`appointment_pk`),
  KEY `idx_appt_id` (`appointment_id`),
  KEY `idx_changed_at` (`changed_at`),
  KEY `idx_new_status` (`new_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- call_attempts : Her arama denemesi
-- ---------------------------------------------------------------------
-- result_label sözlüğü:
--   initiated    : NAC'a istek gönderildi, sonuç bekleniyor
--   answered     : Çağrı cevaplandı (dialstatus=ANSWER)
--   no_answer    : Cevapsız (result=7)
--   busy         : Meşgul (result=6)
--   unreachable  : Ulaşılamadı (result=8)
--   route_error  : Route yok/bloklu/zaman dışı (result=1/2/3)
--   failed       : NAC isteği başarısız (status != 1)
-- dtmf: 1=geliyorum, 2=iptal, 3=operatör/tekrar
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `call_attempts` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_pk`  BIGINT UNSIGNED NOT NULL,
  `appointment_id`  VARCHAR(64) NOT NULL,
  `guid`            VARCHAR(64) DEFAULT NULL,
  `hospital_id`     INT DEFAULT NULL,
  `phone`           VARCHAR(32) DEFAULT NULL,
  `callerid`        VARCHAR(32) DEFAULT NULL,
  `nac_uniqueid`    VARCHAR(64) DEFAULT NULL,
  `nac_status`      VARCHAR(16) DEFAULT NULL COMMENT 'call/call yanıt status',
  `nac_message`     VARCHAR(255) DEFAULT NULL,
  `dialstatus`      VARCHAR(24) DEFAULT NULL COMMENT 'Ring/Answer/Hangup',
  `result_code`     VARCHAR(8) DEFAULT NULL COMMENT 'NAC result kodu (6/7/8...)',
  `result_label`    VARCHAR(24) NOT NULL DEFAULT 'initiated',
  `dtmf`            TINYINT UNSIGNED DEFAULT NULL,
  `recordfilename`  VARCHAR(255) DEFAULT NULL,
  `initiated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `answered_at`     DATETIME DEFAULT NULL,
  `hangup_at`       DATETIME DEFAULT NULL,
  `duration_sec`    INT DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_uniqueid` (`nac_uniqueid`),
  KEY `idx_appt_pk` (`appointment_pk`),
  KEY `idx_appt_id` (`appointment_id`),
  KEY `idx_result` (`result_label`),
  KEY `idx_initiated` (`initiated_at`),
  KEY `idx_dtmf` (`dtmf`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- sync_runs : Her senkron çalışmasının özeti
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_runs` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `target_date`          DATE DEFAULT NULL,
  `started_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at`          DATETIME DEFAULT NULL,
  `fetched_count`        INT NOT NULL DEFAULT 0,
  `new_count`            INT NOT NULL DEFAULT 0,
  `updated_count`        INT NOT NULL DEFAULT 0,
  `status_changed_count` INT NOT NULL DEFAULT 0,
  `disappeared_count`    INT NOT NULL DEFAULT 0,
  `callable_count`       INT NOT NULL DEFAULT 0,
  `status`               VARCHAR(16) NOT NULL DEFAULT 'running' COMMENT 'running/ok/error',
  `error`                TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_started` (`started_at`),
  KEY `idx_target_date` (`target_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- event_logs : İnsan-okur olay akışı
-- ---------------------------------------------------------------------
-- category: SYNC | CALL | EVENT | DTMF | CONFIRM | CANCEL | SYSTEM
-- level   : info | warn | error
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `event_logs` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `log_time`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `level`          VARCHAR(8) NOT NULL DEFAULT 'info',
  `category`       VARCHAR(16) NOT NULL DEFAULT 'SYSTEM',
  `appointment_id` VARCHAR(64) DEFAULT NULL,
  `nac_uniqueid`   VARCHAR(64) DEFAULT NULL,
  `sync_run_id`    BIGINT UNSIGNED DEFAULT NULL,
  `message`        VARCHAR(1000) NOT NULL,
  `context`        JSON DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_log_time` (`log_time`),
  KEY `idx_category` (`category`),
  KEY `idx_level` (`level`),
  KEY `idx_appt_id` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- report_users : Rapor ekranı kullanıcıları
-- ---------------------------------------------------------------------
-- NOT: Şifreler password_hash() (bcrypt) ile saklanmalı (düz metin DEĞİL).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `report_users` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(64) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name`     VARCHAR(128) DEFAULT NULL,
  `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
  `last_login_at` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

