-- Test paket tanımları
CREATE TABLE IF NOT EXISTS test_packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL COMMENT 'Paket adı: Aylık Diyaliz, 3 Aylık Kontrol vs',
  description TEXT,
  package_type VARCHAR(50) COMMENT 'monthly, quarterly, semi_annual, annual',
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(is_active),
  INDEX(package_type)
);

-- Paketlerin içindeki testler
CREATE TABLE IF NOT EXISTS test_package_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  test_code VARCHAR(50) NOT NULL,
  test_name VARCHAR(255) NOT NULL,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES test_packages(id) ON DELETE CASCADE,
  INDEX(package_id)
);

-- Örnek paketler
INSERT INTO test_packages (name, description, package_type) VALUES
('Aylık Diyaliz Rutin', 'Her ay yapılan standart diyaliz testleri', 'monthly'),
('3 Aylık Diyaliz Kontrol', '3 ayda bir yapılan detaylı kontrol testleri', 'quarterly'),
('6 Aylık Periyodik Kontrol', '6 ayda bir yapılan kapsamlı tarama', 'semi_annual');

-- Aylık paket testleri
INSERT INTO test_package_items (package_id, test_code, test_name, sort_order) VALUES
(1, 'HD02', 'Üre', 1),
(1, 'HD010', 'Kreatinin', 2),
(1, 'HD39', 'Potasyum', 3),
(1, 'B144', 'Sodyum', 4),
(1, 'B98', 'Kalsiyum', 5),
(1, 'C506', 'Hemogram', 6);

-- 3 aylık paket testleri
INSERT INTO test_package_items (package_id, test_code, test_name, sort_order) VALUES
(2, 'HD02', 'Üre', 1),
(2, 'HD010', 'Kreatinin', 2),
(2, 'HD39', 'Potasyum', 3),
(2, 'B144', 'Sodyum', 4),
(2, 'B98', 'Kalsiyum', 5),
(2, 'B89', 'Fosfor', 6),
(2, 'C506', 'Hemogram', 7),
(2, 'B08', 'SGPT (ALT)', 8),
(2, 'B20', 'Albumin', 9),
(2, 'B131', 'Total Protein', 10),
(2, 'B1098', 'Kan Şekeri', 11);

-- 6 aylık paket testleri (en kapsamlı)
INSERT INTO test_package_items (package_id, test_code, test_name, sort_order) VALUES
(3, 'HD02', 'Üre', 1),
(3, 'HD010', 'Kreatinin', 2),
(3, 'HD39', 'Potasyum', 3),
(3, 'B144', 'Sodyum', 4),
(3, 'B98', 'Kalsiyum', 5),
(3, 'B89', 'Fosfor', 6),
(3, 'C506', 'Hemogram', 7),
(3, 'B08', 'SGPT (ALT)', 8),
(3, 'B20', 'Albumin', 9),
(3, 'B131', 'Total Protein', 10),
(3, 'B1098', 'Kan Şekeri', 11);
