-- ============================================================ -- 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);