-- ============================================================
-- OTEL YÖNETİM SİSTEMİ - VERİTABANI ŞEMASI
-- Engine: InnoDB | Charset: utf8mb4_turkish_ci
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- 1. YÖNETİCİ / KULLANICI TABLOLARI (Admin Panel)
-- ------------------------------------------------------------

CREATE TABLE admin_roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,               -- Super Admin, Resepsiyon, İçerik Editörü...
    permissions JSON NULL,                    -- {"rooms":"rw","blog":"rw","reservations":"rw"}
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,      -- argon2id ile hashlenecek
    avatar VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_login_at DATETIME NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES admin_roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE admin_activity_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,             -- "reservation.approve", "room.update" vb.
    target_table VARCHAR(50) NULL,
    target_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 2. MÜŞTERİ (CRM)
-- ------------------------------------------------------------

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(30) NOT NULL,
    id_number VARCHAR(20) NULL,               -- TC Kimlik / Pasaport No
    id_type ENUM('tc','passport') DEFAULT 'tc',
    nationality VARCHAR(60) NULL,
    address TEXT NULL,
    password_hash VARCHAR(255) NULL,          -- müşteri girişi opsiyonel ise
    notes TEXT NULL,                          -- resepsiyon özel notları
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 3. ODA YÖNETİMİ
-- ------------------------------------------------------------

CREATE TABLE room_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,               -- "Deluxe Deniz Manzaralı"
    slug VARCHAR(120) NOT NULL UNIQUE,
    description TEXT NULL,
    size_m2 DECIMAL(6,2) NULL,
    max_capacity TINYINT UNSIGNED NOT NULL DEFAULT 2,
    bed_type VARCHAR(60) NULL,                -- "1 Çift Kişilik + 1 Tek"
    base_price DECIMAL(10,2) NOT NULL,
    cover_image VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE room_features (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,               -- "Deniz Manzarası", "Jakuzi"
    icon VARCHAR(60) NULL                     -- FontAwesome class
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE room_type_features (
    room_type_id INT UNSIGNED NOT NULL,
    feature_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (room_type_id, feature_id),
    FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id) REFERENCES room_features(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Fiziksel odalar (fiyatlandırma room_type'ta, fiziksel durum burada)
CREATE TABLE rooms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_type_id INT UNSIGNED NOT NULL,
    room_number VARCHAR(20) NOT NULL UNIQUE,  -- "204", "A-12"
    floor VARCHAR(20) NULL,
    status ENUM('empty','occupied','cleaning','maintenance') DEFAULT 'empty',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 4. GALERİ (Foto/Video) - Kategori bazlı, sıralanabilir
-- ------------------------------------------------------------

CREATE TABLE gallery_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,               -- "Odalar", "Restoran", "Havuz/Spa"
    slug VARCHAR(120) NOT NULL UNIQUE,
    display_order INT UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE gallery_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    room_type_id INT UNSIGNED NULL,           -- odayla ilişkilendirme (opsiyonel)
    type ENUM('image','video') DEFAULT 'image',
    file_path VARCHAR(255) NULL,              -- resim: /uploads/gallery/xxx.webp
    video_url VARCHAR(255) NULL,              -- YouTube/Vimeo embed linki
    title VARCHAR(150) NULL,
    is_cover TINYINT(1) DEFAULT 0,            -- vitrin/kapak görseli mi
    display_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES gallery_categories(id) ON DELETE CASCADE,
    FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE SET NULL,
    INDEX idx_category_order (category_id, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 5. REZERVASYON
-- ------------------------------------------------------------

CREATE TABLE reservations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_code VARCHAR(20) NOT NULL UNIQUE,  -- "RSV-2026-000123"
    customer_id INT UNSIGNED NOT NULL,
    room_type_id INT UNSIGNED NOT NULL,
    room_id INT UNSIGNED NULL,                -- manuel atama sonrası dolar
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    adults TINYINT UNSIGNED DEFAULT 1,
    children TINYINT UNSIGNED DEFAULT 0,
    total_price DECIMAL(10,2) NULL,
    source ENUM('online','manual','phone') DEFAULT 'online',
    status ENUM('pending','confirmed','cancelled','checked_in','checked_out') DEFAULT 'pending',
    special_requests TEXT NULL,
    created_by_admin_id INT UNSIGNED NULL,    -- manuel girişte resepsiyon kullanıcısı
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
    FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_dates (check_in, check_out),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE reservation_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NOT NULL,
    old_status VARCHAR(30) NULL,
    new_status VARCHAR(30) NOT NULL,
    changed_by_admin_id INT UNSIGNED NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 6. WHATSAPP ŞABLONLARI & MESAJ GEÇMİŞİ
-- ------------------------------------------------------------

CREATE TABLE whatsapp_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,              -- "Rezervasyon Onaylandı"
    message TEXT NOT NULL,                    -- {{ad_soyad}}, {{tarih}} gibi placeholder destekli
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE whatsapp_message_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NULL,
    customer_id INT UNSIGNED NULL,
    template_id INT UNSIGNED NULL,
    sent_by_admin_id INT UNSIGNED NULL,
    final_message TEXT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES whatsapp_templates(id) ON DELETE SET NULL,
    FOREIGN KEY (sent_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 7. BLOG / İÇERİK (CMS)
-- ------------------------------------------------------------

CREATE TABLE blog_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE blog_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    admin_id INT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    excerpt VARCHAR(500) NULL,
    content LONGTEXT NOT NULL,
    cover_image VARCHAR(255) NULL,
    meta_title VARCHAR(200) NULL,
    meta_description VARCHAR(300) NULL,
    status ENUM('draft','published') DEFAULT 'draft',
    published_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- ------------------------------------------------------------
-- 8. İLETİŞİM & GENEL AYARLAR
-- ------------------------------------------------------------

CREATE TABLE contact_messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NULL,
    subject VARCHAR(200) NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- key-value ayar tablosu: hotel_name, hotel_phone, whatsapp_number, hotel_address,
-- social_facebook, social_instagram, logo_path, google_maps_embed vb.
CREATE TABLE hotel_settings (
    setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- ÖRNEK BAŞLANGIÇ VERİSİ (opsiyonel)
-- ------------------------------------------------------------
INSERT INTO admin_roles (name, permissions) VALUES
('Süper Admin', '{"*":"rw"}');

INSERT INTO gallery_categories (name, slug, display_order) VALUES
('Odalar', 'odalar', 1),
('Restoran', 'restoran', 2),
('Havuz & Spa', 'havuz-spa', 3),
('Genel Alanlar', 'genel-alanlar', 4);
