-- ============================================
-- SİPARİŞ TAKİP SİSTEMİ - VERİTABANI ŞEMASI
-- MySQL 5.7+ / MariaDB 10.3+
-- ============================================


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

