-- Alpi Diş Hastaneleri CRM Database Schema
-- Güvenli ve performanslı veritabanı yapısı


-- Veritabanı karakter seti
ALTER DATABASE alpidisc_Balik CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Şubeler tablosu
CREATE TABLE `branches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` text,
  `phone` varchar(20),
  `manager_name` varchar(100),
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Şube verilerini ekle
INSERT INTO `branches` (`id`, `name`, `address`, `phone`, `manager_name`, `is_active`) VALUES
(1, 'Kaynarca', 'Kaynarca Şubesi Adresi', '0216 xxx xx xx', 'Şube Müdürü', 1),
(2, 'Kartal', 'Kartal Şubesi Adresi', '0216 xxx xx xx', 'Şube Müdürü', 1),
(3, 'Pendik', 'Pendik Şubesi Adresi', '0216 xxx xx xx', 'Şube Müdürü', 1),
(4, 'Tuzla', 'Tuzla Şubesi Adresi', '0216 xxx xx xx', 'Şube Müdürü', 1);

-- Anlaşmama nedenleri tablosu
CREATE TABLE `reasons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) DEFAULT NULL,
  `reason_text` varchar(200) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `branch_id` (`branch_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `reasons_branch_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reasons_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan nedenleri ekle
INSERT INTO `reasons` (`reason_text`, `is_active`) VALUES
('Fiyat yüksek geldi', 1),
('Kredi çıkmadı', 1),
('Düşünecek', 1),
('Rakip kliniğe gitti', 1),
('Başka şehirde yaşıyor', 1),
('Zaman bulamadı', 1),
('Aileden izin alamadı', 1),
('Diğer', 1);

-- Hasta danışmanları tablosu
CREATE TABLE `consultants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `phone` varchar(20),
  `email` varchar(100),
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `branch_id` (`branch_id`),
  CONSTRAINT `consultants_branch_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hastalar tablosu  
CREATE TABLE `patients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `consultant_id` int(11) DEFAULT NULL,
  `source` varchar(100) DEFAULT NULL,
  `application_date` date NOT NULL,
  `patient_type` enum('new','existing') DEFAULT 'new',
  `reason_id` int(11) DEFAULT NULL,
  `status` enum('pending','recovered','lost') DEFAULT 'pending',
  `notes` text,
  `followup_date` date DEFAULT NULL,
  `followup_count` int(11) DEFAULT 0,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `branch_id` (`branch_id`),
  KEY `consultant_id` (`consultant_id`),
  KEY `reason_id` (`reason_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  KEY `status` (`status`),
  KEY `followup_date` (`followup_date`),
  KEY `phone` (`phone`),
  CONSTRAINT `patients_branch_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `patients_consultant_fk` FOREIGN KEY (`consultant_id`) REFERENCES `consultants` (`id`) ON DELETE SET NULL,
  CONSTRAINT `patients_reason_fk` FOREIGN KEY (`reason_id`) REFERENCES `reasons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `patients_created_by_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `patients_updated_by_fk` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Follow-up geçmişi tablosu
CREATE TABLE `followups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL,
  `followup_date` date NOT NULL,
  `contacted_by` int(11) NOT NULL,
  `contact_method` enum('phone','whatsapp','sms','email') DEFAULT 'phone',
  `result` enum('answered','no_answer','busy','wrong_number','recovered','rejected') NOT NULL,
  `notes` text,
  `next_followup_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `contacted_by` (`contacted_by`),
  KEY `followup_date` (`followup_date`),
  CONSTRAINT `followups_patient_fk` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `followups_user_fk` FOREIGN KEY (`contacted_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Aktivite log tablosu
CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action_type` varchar(50) NOT NULL,
  `target_table` varchar(50) NOT NULL,
  `record_id` int(11) DEFAULT NULL,
  `description` text,
  `ip_address` varchar(45),
  `user_agent` text,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `action_type` (`action_type`),
  KEY `target_table` (`target_table`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `activity_log_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login denemesi tablosu
CREATE TABLE `login_attempts` (
  `ip_address` varchar(45) NOT NULL,
  `failed_attempts` int(11) DEFAULT 0,
  `last_attempt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sistem ayarları tablosu
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `description` varchar(255),
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `settings_user_fk` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan ayarları ekle
INSERT INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('followup_days', '3', 'Varsayılan geri arama günü'),
('max_followup_attempts', '3', 'Maksimum arama sayısı'),
('company_name', 'Alpi Diş Hastaneleri', 'Şirket adı'),
('company_logo', '', 'Şirket logosu'),
('email_notifications', '1', 'E-posta bildirimlerini etkinleştir');

-- Indexes for performance
CREATE INDEX idx_patients_search ON patients(first_name, last_name, phone);
CREATE INDEX idx_patients_status_date ON patients(status, created_at);
CREATE INDEX idx_followups_date_user ON followups(followup_date, contacted_by);
CREATE INDEX idx_activity_date_user ON activity_log(created_at, user_id);


-- Create views for reporting
CREATE VIEW view_patient_stats AS
SELECT 
    b.name as branch_name,
    COUNT(p.id) as total_patients,
    COUNT(CASE WHEN p.status = 'pending' THEN 1 END) as pending_patients,
    COUNT(CASE WHEN p.status = 'recovered' THEN 1 END) as recovered_patients,
    COUNT(CASE WHEN p.status = 'lost' THEN 1 END) as lost_patients,
    ROUND(COUNT(CASE WHEN p.status = 'recovered' THEN 1 END) * 100.0 / NULLIF(COUNT(p.id), 0), 2) as recovery_rate
FROM branches b
LEFT JOIN patients p ON b.id = p.branch_id
GROUP BY b.id, b.name;

CREATE VIEW view_consultant_performance AS
SELECT 
    c.name as consultant_name,
    b.name as branch_name,
    COUNT(p.id) as total_patients,
    COUNT(CASE WHEN p.status = 'recovered' THEN 1 END) as recovered_patients,
    ROUND(COUNT(CASE WHEN p.status = 'recovered' THEN 1 END) * 100.0 / NULLIF(COUNT(p.id), 0), 2) as success_rate
FROM consultants c
LEFT JOIN patients p ON c.id = p.consultant_id
LEFT JOIN branches b ON c.branch_id = b.id
GROUP BY c.id, c.name, b.name
ORDER BY success_rate DESC;