-- ============================================ -- SİPARİŞ TAKİP SİSTEMİ - VERİTABANI ŞEMASI -- MySQL 5.7+ / MariaDB 10.3+ -- ============================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- -------------------------------------------- -- Restoranlar -- -------------------------------------------- CREATE TABLE IF NOT EXISTS restaurants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, phone VARCHAR(50), address TEXT, is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_active (is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Platformlar (migros, yemeksepeti, getir, trendyol) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS platforms ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, -- 'migros', 'yemeksepeti', vs. name VARCHAR(100) NOT NULL, is_active TINYINT(1) DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO platforms (code, name) VALUES ('migros', 'Migros Yemek'), ('yemeksepeti', 'Yemeksepeti'), ('getir', 'Getir Yemek'), ('trendyol', 'Trendyol Yemek') ON DUPLICATE KEY UPDATE name = VALUES(name); -- -------------------------------------------- -- Restoran-Platform eşleştirmesi (her restoranın her platformdaki api_key'i) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS restaurant_platforms ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id INT UNSIGNED NOT NULL, platform_id INT UNSIGNED NOT NULL, external_restaurant_id VARCHAR(255), -- platformun bizim restorana verdiği ID api_key VARCHAR(500), -- işletme bazlı api key (şifreli saklanabilir) is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_restaurant_platform (restaurant_id, platform_id), FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, FOREIGN KEY (platform_id) REFERENCES platforms(id), INDEX idx_external (platform_id, external_restaurant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Webhook'tan gelen HAM veriler (önce buraya kaydedip 200 OK döneceğiz) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS webhook_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, platform_id INT UNSIGNED NOT NULL, event_type VARCHAR(100), -- 'new_order', 'order_cancel', vs. raw_payload LONGTEXT, -- gelen tam JSON (şifrelenmiş haliyle) decrypted_payload LONGTEXT, -- çözülmüş hali headers TEXT, -- gelen header'lar ip_address VARCHAR(45), is_processed TINYINT(1) DEFAULT 0, process_error TEXT, received_at DATETIME DEFAULT CURRENT_TIMESTAMP, processed_at DATETIME NULL, FOREIGN KEY (platform_id) REFERENCES platforms(id), INDEX idx_unprocessed (is_processed, received_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Normalize edilmiş siparişler (4 platform için tek tip) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id INT UNSIGNED NOT NULL, platform_id INT UNSIGNED NOT NULL, external_order_id VARCHAR(100) NOT NULL, -- platformun sipariş ID'si order_code VARCHAR(50), -- müşteriye gösterilen kısa kod status VARCHAR(50) NOT NULL, -- 'new', 'accepted', 'preparing', 'ready', 'delivered', 'cancelled' customer_name VARCHAR(255), customer_phone VARCHAR(50), customer_address TEXT, customer_note TEXT, payment_method VARCHAR(50), -- 'online', 'cash', 'creditcard_on_delivery' is_paid TINYINT(1) DEFAULT 0, subtotal DECIMAL(10, 2) DEFAULT 0, discount DECIMAL(10, 2) DEFAULT 0, delivery_fee DECIMAL(10, 2) DEFAULT 0, total DECIMAL(10, 2) DEFAULT 0, delivery_type VARCHAR(50), -- 'platform', 'restaurant', 'takeaway' estimated_delivery_time DATETIME NULL, raw_data LONGTEXT, -- platformdan gelen orjinal sipariş verisi created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_platform_order (platform_id, external_order_id), FOREIGN KEY (restaurant_id) REFERENCES restaurants(id), FOREIGN KEY (platform_id) REFERENCES platforms(id), INDEX idx_restaurant_status (restaurant_id, status), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Sipariş kalemleri (ürünler) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, external_product_id VARCHAR(100), product_name VARCHAR(500) NOT NULL, quantity INT NOT NULL DEFAULT 1, unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0, total_price DECIMAL(10, 2) NOT NULL DEFAULT 0, note TEXT, -- müşterinin ürüne özel notu options_json TEXT, -- ekstra/seçenekler JSON olarak FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Sipariş durum geçmişi -- -------------------------------------------- CREATE TABLE IF NOT EXISTS order_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(50), new_status VARCHAR(50) NOT NULL, changed_by VARCHAR(100), -- 'webhook', 'panel:user_id', 'system' note TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------- -- Panel kullanıcıları (restoran personeli + admin) -- -------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id INT UNSIGNED NULL, -- NULL ise admin (tüm restoranları görür) username VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(255), role ENUM('admin', 'restaurant_owner', 'restaurant_staff') DEFAULT 'restaurant_staff', is_active TINYINT(1) DEFAULT 1, last_login DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, INDEX idx_restaurant (restaurant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;