-- Order Management Database Schema
CREATE DATABASE IF NOT EXISTS order_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE order_management;

-- Orders Table - Tüm sipariş bilgileri tek tabloda
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- Sipariş Temel Bilgileri
    order_id INT NOT NULL,
    order_code VARCHAR(50) NOT NULL UNIQUE,
    order_date DATE NOT NULL,
    order_time TIME NOT NULL,
    order_total_quantity INT NOT NULL,
    unit_price DECIMAL(15, 2) NOT NULL,
    total_price DECIMAL(15, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) NOT NULL,
    payment_type VARCHAR(50) DEFAULT NULL,
    bim_invoice_number VARCHAR(100) DEFAULT NULL,
    -- Depo ve Mağaza Bilgileri
    bim_warehouse_code VARCHAR(50) NOT NULL,
    bim_warehouse_name VARCHAR(255) NOT NULL,
    bim_store_code VARCHAR(50) NOT NULL,
    bim_store_name VARCHAR(255) NOT NULL,
    -- Ürün Bilgileri (BIM)
    bim_product_code VARCHAR(50) NOT NULL,
    bim_ean_code VARCHAR(50) NOT NULL,
    bim_product_name VARCHAR(255) NOT NULL,
    bim_invoice_description TEXT DEFAULT NULL,
    -- Firma Bilgileri
    company_code VARCHAR(50) NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    company_product_code VARCHAR(50) NOT NULL,
    company_ean_code VARCHAR(50) NOT NULL,
    company_product_name VARCHAR(255) NOT NULL,
    -- Müşteri Bilgileri
    customer_tckn VARCHAR(11) NOT NULL,
    customer_name1 VARCHAR(100) NOT NULL,
    customer_surname1 VARCHAR(100) NOT NULL,
    customer_phone1 VARCHAR(20) NOT NULL,
    customer_name2 VARCHAR(100) DEFAULT NULL,
    customer_surname2 VARCHAR(100) DEFAULT NULL,
    customer_phone2 VARCHAR(20) DEFAULT NULL,
    -- Teslimat Adresi Bilgileri
    delivery_city_name VARCHAR(100) NOT NULL,
    delivery_district_name VARCHAR(100) NOT NULL,
    delivery_neighbourhood_name VARCHAR(100) NOT NULL,
    delivery_address1 VARCHAR(255) NOT NULL,
    delivery_address2 VARCHAR(255) DEFAULT NULL,
    -- Sistem Alanları
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- İndeksler
    INDEX idx_order_code (order_code),
    INDEX idx_order_date (order_date),
    INDEX idx_bim_product_code (bim_product_code),
    INDEX idx_customer_tckn (customer_tckn),
    INDEX idx_city (delivery_city_name),
    INDEX idx_district (delivery_district_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

