-- Database Schema for Tour Management System

CREATE DATABASE IF NOT EXISTS tour_management;
USE tour_management;

-- Users Table (Admin users)
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'super_admin') DEFAULT 'admin',
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Airlines Table (Havayolu Şirketleri)
CREATE TABLE IF NOT EXISTS airlines (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(10) NOT NULL UNIQUE,
    logo_url VARCHAR(500),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categories Table (Ana Kategoriler: YURTDIŞI TURLARI, YURTIÇI TURLARI vb.)
CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    image_url VARCHAR(500),
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sub Categories Table (Alt Kategoriler: Bosna Hersek Turları, Balkan Turları vb.)
CREATE TABLE IF NOT EXISTS sub_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    image_url VARCHAR(500),
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Tours Table (Yeniden yapılandırılmış - Kategori tabanlı)
CREATE TABLE IF NOT EXISTS tours (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sub_category_id INT NOT NULL,
    airline_id INT, -- Havayolu şirketi
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description LONGTEXT,
    short_description TEXT,

    -- Görseller
    main_image VARCHAR(500),
    gallery JSON, -- Array of image URLs: ["url1", "url2", ...]

    -- Tur Detayları
    duration VARCHAR(100), -- "5 Gün 4 Gece"
    tour_date DATE, -- Ana tur tarihi
    return_date DATE, -- Dönüş tarihi
    accommodation_type VARCHAR(100), -- "4-5 Yıldızlı Oteller"
    route TEXT, -- "İstanbul - Dubai - İstanbul"

    -- Tarihler ve Kayıt
    tour_dates JSON, -- [{date: "2024-05-15", available_seats: 40, status: "open"}]
    registration_status ENUM('open', 'closed', 'full') DEFAULT 'open',
    important_dates JSON, -- [{type: "registration_deadline", date: "2024-05-01", note: "Erken kayıt indirimi"}]

    -- Otel Bilgileri
    hotel_info JSON, -- [{name: "Luxury Hotel", stars: 5, location: "Dubai Marina", maps_iframe: "...", features: ["WiFi", "Pool"]}]

    -- Ücretler
    pricing JSON, -- {double_room: 5000, triple_room: 4500, single_room: 6000, child_6_12: 3000, child_2_6: 1500, baby_0_2: 0}

    -- Hizmetler
    included_services JSON, -- ["Uçak bileti", "Konaklama", "Rehberlik"]
    excluded_services JSON, -- ["Öğle yemeği", "Ekstra turlar"]

    -- Tur Programı
    itinerary JSON, -- [{day: 1, title: "Varış", description: "...", activities: ["Activity 1"], meals: {breakfast: true, lunch: false, dinner: true}}]

    -- Diğer
    max_people INT DEFAULT 40,
    featured BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'inactive', 'draft') DEFAULT 'draft',
    views INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (sub_category_id) REFERENCES sub_categories(id) ON DELETE CASCADE,
    FOREIGN KEY (airline_id) REFERENCES airlines(id) ON DELETE SET NULL
);

-- Customers Table
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table (Rezervasyonlar)
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT,
    customer_id INT,
    tour_id INT NOT NULL,

    -- Müşteri Bilgileri
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,

    -- Rezervasyon Detayları
    tour_date DATE NOT NULL,
    number_of_people JSON, -- {adults: 2, children_6_12: 1, children_2_6: 0, babies: 0}
    room_type VARCHAR(50), -- 'double', 'triple', 'single'

    -- Ödeme
    total_amount DECIMAL(10, 2) NOT NULL,
    payment_status ENUM('pending', 'partial', 'paid', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),

    -- Notlar
    special_requests TEXT,
    admin_notes TEXT,

    booking_status ENUM('confirmed', 'pending', 'cancelled') DEFAULT 'pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (tour_id) REFERENCES tours(id) ON DELETE CASCADE
);

-- Indexes (Performans için)
CREATE INDEX idx_tours_sub_category ON tours(sub_category_id);
CREATE INDEX idx_tours_status ON tours(status);
CREATE INDEX idx_tours_featured ON tours(featured);
CREATE INDEX idx_sub_categories_category ON sub_categories(category_id);
CREATE INDEX idx_orders_tour ON orders(tour_id);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Örnek Kategoriler
INSERT INTO categories (name, slug, description, status, display_order) VALUES
('YURTDIŞI TURLARI', 'yurtdisi-turlari', 'Yurtdışı tur paketleri', 'active', 1),
('YURTIÇI TURLARI', 'yurtici-turlari', 'Yurtiçi tur paketleri', 'active', 2);

-- Örnek Alt Kategoriler
INSERT INTO sub_categories (category_id, name, slug, description, status, display_order) VALUES
(1, 'Bosna Hersek Turları', 'bosna-hersek-turlari', 'Bosna Hersek tur paketleri', 'active', 1),
(1, 'Balkan Turları', 'balkan-turlari', 'Balkan tur paketleri', 'active', 2),
(1, 'Dubai Turları', 'dubai-turlari', 'Dubai tur paketleri', 'active', 3),
(1, 'Mısır Turları', 'misir-turlari', 'Mısır tur paketleri', 'active', 4),
(1, 'Fas Turları', 'fas-turlari', 'Fas tur paketleri', 'active', 5);

-- Örnek Havayolları
INSERT INTO airlines (name, code, logo_url, status) VALUES
('Türk Hava Yolları', 'THY', 'https://image.ninovaturizm.com.tr/yonetim/images/thumbs/logo/airways/THY-logo-png_h_24px.png', 'active'),
('Pegasus', 'PGS', 'https://image.ninovaturizm.com.tr/yonetim/images/thumbs/logo/airways/pegasus-logo_h_24px.png', 'active'),
('AnadoluJet', 'AJT', 'https://image.ninovaturizm.com.tr/yonetim/images/thumbs/logo/airways/anadolujet-logo_h_24px.png', 'active');

-- Insert default admin user (password: admin123)
INSERT INTO users (username, email, password, role) VALUES
('admin', 'admin@example.com', '$2a$10$vYPeUnUC2uTfQcIqAwfKJORSIr1qxA5XQdqNXdnNZJTXO8pHc8s6a', 'super_admin');