-- E-Ticaret Sitesi Veritabanı Yapısı

CREATE DATABASE IF NOT EXISTS eticaret CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;
USE eticaret;

-- Admin tablosu
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Kategoriler tablosu
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    category_description TEXT,
    parent_id INT DEFAULT 0,
    category_image VARCHAR(255),
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status)
);

-- Ürünler tablosu
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    product_description TEXT,
    product_short_description TEXT,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2),
    sku VARCHAR(100) UNIQUE,
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 5,
    weight DECIMAL(8,2),
    dimensions VARCHAR(100),
    product_image VARCHAR(255),
    product_gallery JSON,
    status TINYINT(1) DEFAULT 1,
    featured TINYINT(1) DEFAULT 0,
    seo_title VARCHAR(200),
    seo_description TEXT,
    seo_keywords VARCHAR(500),
    baslik_promptu TEXT,
    aciklama_promptu TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category_id (category_id),
    INDEX idx_status (status),
    INDEX idx_featured (featured),
    INDEX idx_price (price),
    FULLTEXT idx_search (product_name, product_description)
);

-- Kullanıcılar tablosu
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    birth_date DATE,
    gender ENUM('male', 'female', 'other'),
    email_verified TINYINT(1) DEFAULT 0,
    verification_token VARCHAR(255),
    reset_token VARCHAR(255),
    reset_token_expires DATETIME,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status)
);

-- Adresler tablosu
CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    address_type ENUM('billing', 'shipping') DEFAULT 'shipping',
    title VARCHAR(100),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    company VARCHAR(100),
    address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    district VARCHAR(100),
    postal_code VARCHAR(10),
    country VARCHAR(100) DEFAULT 'Türkiye',
    phone VARCHAR(20),
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

-- Siparişler tablosu
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT,
    order_status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    payment_method ENUM('credit_card', 'bank_transfer', 'cash_on_delivery', 'paypal') DEFAULT 'cash_on_delivery',
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    shipping_cost DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'TRY',
    notes TEXT,
    billing_address JSON,
    shipping_address JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at)
);

-- Sipariş detayları tablosu
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    product_price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
);

-- Sepet tablosu
CREATE TABLE cart (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    session_id VARCHAR(255),
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cart_item (user_id, product_id),
    UNIQUE KEY unique_session_item (session_id, product_id),
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id)
);

-- Kuponlar tablosu
CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    discount_type ENUM('fixed', 'percentage') DEFAULT 'fixed',
    discount_value DECIMAL(10,2) NOT NULL,
    minimum_amount DECIMAL(10,2) DEFAULT 0,
    maximum_discount DECIMAL(10,2),
    usage_limit INT,
    usage_count INT DEFAULT 0,
    starts_at DATETIME,
    expires_at DATETIME,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_status (status)
);

-- Slider/Banner tablosu
CREATE TABLE banners (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    description TEXT,
    image VARCHAR(255) NOT NULL,
    link VARCHAR(255),
    position ENUM('home_main', 'home_secondary', 'category') DEFAULT 'home_main',
    sort_order INT DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    starts_at DATETIME,
    expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_position (position),
    INDEX idx_status (status)
);

-- Site ayarları tablosu
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('text', 'textarea', 'number', 'boolean', 'file') DEFAULT 'text',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
);

-- Aktivite logları tablosu
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    user_type ENUM('admin', 'user') DEFAULT 'user',
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- İletişim mesajları tablosu
CREATE TABLE contact_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('unread', 'read', 'replied') DEFAULT 'unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Varsayılan admin kullanıcısı (şifre: admin123)
INSERT INTO admins (username, email, password, full_name) VALUES 
('admin', 'admin@eticaret.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Site Yöneticisi');

-- Varsayılan kategoriler
INSERT INTO categories (category_name, category_description, parent_id, sort_order) VALUES 
('Elektronik', 'Elektronik ürünler', 0, 1),
('Giyim', 'Giyim ürünleri', 0, 2),
('Ev & Yaşam', 'Ev ve yaşam ürünleri', 0, 3),
('Bilgisayar', 'Bilgisayar ve ekipmanları', 1, 1),
('Telefon', 'Cep telefonları ve aksesuarları', 1, 2),
('Erkek Giyim', 'Erkek giyim ürünleri', 2, 1),
('Kadın Giyim', 'Kadın giyim ürünleri', 2, 2),
('Mutfak', 'Mutfak ürünleri', 3, 1),
('Mobilya', 'Mobilya ürünleri', 3, 2);

-- Varsayılan ayarlar
INSERT INTO settings (setting_key, setting_value, setting_type) VALUES 
('site_name', 'E-Ticaret Sitesi', 'text'),
('site_description', 'Kaliteli ürünler uygun fiyatlarla', 'textarea'),
('site_keywords', 'e-ticaret, alışveriş, online satış', 'text'),
('site_email', 'info@eticaret.com', 'text'),
('site_phone', '+90 212 555 55 55', 'text'),
('site_address', 'İstanbul, Türkiye', 'textarea'),
('facebook_url', 'https://facebook.com', 'text'),
('twitter_url', 'https://twitter.com', 'text'),
('instagram_url', 'https://instagram.com', 'text'),
('shipping_cost', '15.00', 'number'),
('free_shipping_threshold', '200.00', 'number'),
('tax_rate', '18', 'number'),
('currency', 'TRY', 'text'),
('maintenance_mode', '0', 'boolean');

-- Örnek ürünler
INSERT INTO products (product_name, product_description, product_short_description, category_id, price, discount_price, sku, stock, product_image, status, featured, seo_title, seo_description) VALUES 
('Laptop Pro 15"', 'Yüksek performanslı laptop, 16GB RAM, 512GB SSD', 'Profesyonel kullanım için ideal laptop', 4, 8500.00, 7999.00, 'LP001', 25, 'laptop.jpg', 1, 1, 'Laptop Pro 15" - Yüksek Performans', 'Yüksek performanslı laptop ile işinizi kolaylaştırın'),
('Akıllı Telefon X', '6.5 inç ekran, 128GB depolama, 48MP kamera', 'En yeni akıllı telefon modeli', 5, 4500.00, NULL, 'SP001', 50, 'phone.jpg', 1, 1, 'Akıllı Telefon X - Yeni Model', 'En yeni özelliklerle dolu akıllı telefon'),
('Erkek Gömlek', '%100 pamuk, slim fit, beyaz renk', 'Kaliteli erkek gömleği', 6, 250.00, 199.00, 'EG001', 100, 'shirt.jpg', 1, 0, 'Erkek Pamuk Gömlek', 'Rahat ve şık erkek gömleği'),
('Kahve Makinesi', 'Otomatik kahve makinesi, 1.5L kapasite', 'Kahve keyfini evinize taşıyın', 7, 1200.00, 999.00, 'KM001', 30, 'coffee.jpg', 1, 1, 'Otomatik Kahve Makinesi', 'Profesyonel kahve deneyimi evinizde');