-- Rezervasyon sistemi için gerekli güncellemeler
-- Tarih: 2026-03-01

-- 1. Tours tablosuna kontenjan alanları ekle (max_people zaten var)
ALTER TABLE tours
ADD COLUMN IF NOT EXISTS current_bookings INT DEFAULT 0 COMMENT 'Mevcut rezervasyon sayısı',
ADD COLUMN IF NOT EXISTS available_seats INT GENERATED ALWAYS AS (IFNULL(max_people, 40) - current_bookings) STORED COMMENT 'Kalan kontenjan';

-- 2. Umre tours tablosuna kontenjan alanları ekle (max_people zaten var)
ALTER TABLE umre_tours
ADD COLUMN IF NOT EXISTS current_bookings INT DEFAULT 0 COMMENT 'Mevcut rezervasyon sayısı',
ADD COLUMN IF NOT EXISTS available_seats INT GENERATED ALWAYS AS (IFNULL(max_people, 40) - current_bookings) STORED COMMENT 'Kalan kontenjan';

-- 3. Orders tablosunu güncelle - PayTR için gerekli alanlar
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS paytr_token VARCHAR(255) DEFAULT NULL COMMENT 'PayTR ödeme token',
ADD COLUMN IF NOT EXISTS paytr_iframe_token TEXT DEFAULT NULL COMMENT 'PayTR iframe token',
ADD COLUMN IF NOT EXISTS payment_response TEXT DEFAULT NULL COMMENT 'PayTR yanıt detayları',
ADD COLUMN IF NOT EXISTS ip_address VARCHAR(45) DEFAULT NULL COMMENT 'Müşteri IP adresi',
ADD COLUMN IF NOT EXISTS user_basket TEXT DEFAULT NULL COMMENT 'Sepet detayları (JSON)',
MODIFY COLUMN total_amount DECIMAL(10,2) NOT NULL COMMENT 'Toplam tutar (EUR)',
MODIFY COLUMN room_type VARCHAR(100) DEFAULT NULL COMMENT 'Oda tipi';

-- 4. Payment transactions tablosu oluştur (ödeme geçmişi takibi)
CREATE TABLE IF NOT EXISTS payment_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  transaction_type ENUM('payment', 'refund', 'partial_refund') DEFAULT 'payment',
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'TRY',
  paytr_merchant_oid VARCHAR(100) DEFAULT NULL,
  paytr_payment_status VARCHAR(50) DEFAULT NULL,
  paytr_response TEXT DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_order_id (order_id),
  INDEX idx_merchant_oid (paytr_merchant_oid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Orders tablosuna indeksler ekle (performans)
ALTER TABLE orders
ADD INDEX IF NOT EXISTS idx_tour_id (tour_id),
ADD INDEX IF NOT EXISTS idx_customer_email (customer_email),
ADD INDEX IF NOT EXISTS idx_booking_status (booking_status),
ADD INDEX IF NOT EXISTS idx_payment_status (payment_status),
ADD INDEX IF NOT EXISTS idx_tour_date (tour_date),
ADD INDEX IF NOT EXISTS idx_created_at (created_at);

-- 6. Customers tablosuna indeksler ekle
ALTER TABLE customers
ADD INDEX IF NOT EXISTS idx_email (email),
ADD INDEX IF NOT EXISTS idx_phone (phone);

-- 7. Order status değerlerini güncelle
ALTER TABLE orders
MODIFY COLUMN booking_status ENUM('pending', 'confirmed', 'cancelled', 'completed') DEFAULT 'pending',
MODIFY COLUMN payment_status ENUM('pending', 'partial', 'paid', 'refunded', 'failed') DEFAULT 'pending';

-- 8. Rezervasyon istatistikleri için view oluştur
CREATE OR REPLACE VIEW v_tour_statistics AS
SELECT
  t.id,
  t.title,
  t.tour_date,
  t.max_people,
  t.current_bookings,
  t.available_seats,
  COUNT(DISTINCT o.id) as total_orders,
  SUM(CASE WHEN o.booking_status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_orders,
  SUM(CASE WHEN o.payment_status = 'paid' THEN o.total_amount ELSE 0 END) as total_revenue
FROM tours t
LEFT JOIN orders o ON t.id = o.tour_id
GROUP BY t.id, t.title, t.tour_date, t.max_people, t.current_bookings, t.available_seats;

-- 9. Umre turları için istatistik view
CREATE OR REPLACE VIEW v_umre_statistics AS
SELECT
  u.id,
  u.title,
  u.tour_date,
  u.max_people,
  u.current_bookings,
  u.available_seats,
  COUNT(DISTINCT o.id) as total_orders,
  SUM(CASE WHEN o.booking_status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_orders,
  SUM(CASE WHEN o.payment_status = 'paid' THEN o.total_amount ELSE 0 END) as total_revenue
FROM umre_tours u
LEFT JOIN orders o ON u.id = o.tour_id
GROUP BY u.id, u.title, u.tour_date, u.max_people, u.current_bookings, u.available_seats;

-- 10. Trigger: Rezervasyon oluşturulduğunda kontenjanı güncelle
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  DECLARE total_people INT;

  -- JSON'dan toplam kişi sayısını çıkar
  IF NEW.number_of_people IS NOT NULL THEN
    SET total_people = JSON_EXTRACT(NEW.number_of_people, '$.adults') +
                       JSON_EXTRACT(NEW.number_of_people, '$.children') +
                       JSON_EXTRACT(NEW.number_of_people, '$.infants');

    -- Normal turlar için kontenjan güncelle
    UPDATE tours
    SET current_bookings = current_bookings + total_people
    WHERE id = NEW.tour_id AND EXISTS (SELECT 1 FROM tours WHERE id = NEW.tour_id);

    -- Umre turları için kontenjan güncelle
    UPDATE umre_tours
    SET current_bookings = current_bookings + total_people
    WHERE id = NEW.tour_id AND EXISTS (SELECT 1 FROM umre_tours WHERE id = NEW.tour_id);
  END IF;
END$$

-- 11. Trigger: Rezervasyon iptal edildiğinde kontenjanı geri al
CREATE TRIGGER IF NOT EXISTS after_order_cancel
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  DECLARE total_people INT;

  -- Eğer durum cancelled'a çekildiyse
  IF OLD.booking_status != 'cancelled' AND NEW.booking_status = 'cancelled' THEN
    IF NEW.number_of_people IS NOT NULL THEN
      SET total_people = JSON_EXTRACT(NEW.number_of_people, '$.adults') +
                         JSON_EXTRACT(NEW.number_of_people, '$.children') +
                         JSON_EXTRACT(NEW.number_of_people, '$.infants');

      -- Normal turlar için kontenjan geri al
      UPDATE tours
      SET current_bookings = GREATEST(0, current_bookings - total_people)
      WHERE id = NEW.tour_id AND EXISTS (SELECT 1 FROM tours WHERE id = NEW.tour_id);

      -- Umre turları için kontenjan geri al
      UPDATE umre_tours
      SET current_bookings = GREATEST(0, current_bookings - total_people)
      WHERE id = NEW.tour_id AND EXISTS (SELECT 1 FROM umre_tours WHERE id = NEW.tour_id);
    END IF;
  END IF;
END$$

DELIMITER ;

-- 12. Site ayarları için PayTR bilgilerini ekle
INSERT INTO site_settings (setting_key, setting_value, setting_group, created_at, updated_at)
VALUES
  ('paytr_merchant_id', '', 'payment', NOW(), NOW()),
  ('paytr_merchant_key', '', 'payment', NOW(), NOW()),
  ('paytr_merchant_salt', '', 'payment', NOW(), NOW()),
  ('paytr_test_mode', '1', 'payment', NOW(), NOW()),
  ('currency_code', 'EUR', 'payment', NOW(), NOW()),
  ('currency_symbol', '€', 'payment', NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- Tamamlandı
SELECT 'Rezervasyon sistemi başarıyla kuruldu!' as message;