-- Belediye El Emeği Pazaryeri SQL Şeması (V2 - Gelişmiş Sepet & Ödeme Altyapısı)
-- Hazırlayan: Feriha 💩

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Satıcılar (Ev Hanımları)
CREATE TABLE IF NOT EXISTS `sellers` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `phone` varchar(20) NOT NULL UNIQUE COMMENT 'WhatsApp/OTP giriş anahtarı',
    `first_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,
    `profile_photo` varchar(255) DEFAULT NULL,
    `bio` text DEFAULT NULL,
    `address` text DEFAULT NULL,
    `iban` varchar(34) DEFAULT NULL,
    `password` varchar(255) NOT NULL,
    `status` enum('pending', 'active', 'suspended') DEFAULT 'pending',
    `is_verified` tinyint(1) DEFAULT 0,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2. Kategoriler
CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parent_id` int(11) DEFAULT NULL,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL UNIQUE,
    `sort_order` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 3. Ürünler
CREATE TABLE IF NOT EXISTS `products` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `seller_id` bigint(20) UNSIGNED NOT NULL,
    `category_id` int(11) UNSIGNED NOT NULL,
    `title` varchar(255) NOT NULL,
    `description` text NOT NULL,
    `price` decimal(10,2) NOT NULL,
    `stock` int(11) DEFAULT 1,
    `cover_image` varchar(255) DEFAULT NULL,
    `status` enum('pending_approval', 'active', 'sold_out', 'inactive') DEFAULT 'pending_approval',
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`seller_id`) REFERENCES `sellers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 4. Siparişler (Ana Sepet Kabuğu)
CREATE TABLE IF NOT EXISTS `orders` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_no` varchar(20) NOT NULL UNIQUE COMMENT 'Örn: TNTO-2026-0001',
    `total_price` decimal(10,2) NOT NULL COMMENT 'Tüm sepet toplamı',
    `status` enum('pending_payment', 'received', 'preparing', 'shipped', 'completed', 'cancelled') DEFAULT 'pending_payment',
    `customer_name` varchar(255) DEFAULT NULL,
    `customer_phone` varchar(20) DEFAULT NULL,
    `customer_email` varchar(255) DEFAULT NULL,
    `shipping_address` text DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Sipariş Kalemleri (Farklı satıcılardan ürünler burada ayrışır)
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` bigint(20) UNSIGNED NOT NULL,
    `seller_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Kolay raporlama için satıcı ID kopyalanır',
    `product_id` bigint(20) UNSIGNED NOT NULL,
    `product_title` varchar(255) NOT NULL COMMENT 'Ürün o anki adı (geçmiş takibi için)',
    `quantity` int(11) NOT NULL,
    `price` decimal(10,2) NOT NULL COMMENT 'İşlem anındaki birim fiyat',
    `sub_status` enum('pending', 'preparing', 'shipped', 'delivered') DEFAULT 'pending' COMMENT 'Satıcı bazlı durum takibi',
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`seller_id`) REFERENCES `sellers`(`id`),
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 6. Ödemeler (Banka / PayTR Detaylı Log)
CREATE TABLE IF NOT EXISTS `payments` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` bigint(20) UNSIGNED NOT NULL,
    `provider` varchar(50) NOT NULL COMMENT 'Örn: paytr, iyzico, ziraat',
    `transaction_id` varchar(255) DEFAULT NULL COMMENT 'Kurumdan dönen referans no',
    `amount` decimal(10,2) NOT NULL,
    `status` varchar(50) NOT NULL COMMENT 'success, failed, callback_pending',
    `raw_response` json DEFAULT NULL COMMENT 'Kurumdan gelen tüm JSON/Data objesi',
    `error_message` text DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 7. Belediye Lojistik (Satıcı Bazlı Toplama Talepleri)
CREATE TABLE IF NOT EXISTS `collection_requests` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` bigint(20) UNSIGNED NOT NULL,
    `seller_id` bigint(20) UNSIGNED NOT NULL,
    `status` enum('pending', 'assigned_to_courier', 'picked_up', 'delivered_to_center') DEFAULT 'pending',
    `courier_notes` text DEFAULT NULL,
    `request_date` timestamp DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`),
    FOREIGN KEY (`seller_id`) REFERENCES `sellers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 8. Hakedişler (Satıcı bazlı para transfer takibi)
CREATE TABLE IF NOT EXISTS `payouts` (
    `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` bigint(20) UNSIGNED NOT NULL,
    `seller_id` bigint(20) UNSIGNED NOT NULL,
    `amount` decimal(10,2) NOT NULL COMMENT 'Komisyon düşülmüş net tutar',
    `status` enum('pending', 'processing', 'paid', 'cancelled') DEFAULT 'pending',
    `transaction_reference` varchar(100) DEFAULT NULL,
    `paid_at` datetime DEFAULT NULL,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`),
    FOREIGN KEY (`seller_id`) REFERENCES `sellers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;
