-- PEPC V2 - Katalog Sistemi Veritabanı Şeması
-- Oluşturulma Tarihi: 2025-11-29

USE pepcvson;

-- =====================================================
-- Kategoriler (klasör yapısını cache'le)
-- =====================================================
CREATE TABLE IF NOT EXISTS catalog_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(255) NOT NULL,
    path VARCHAR(1000) NOT NULL,
    level INT NOT NULL DEFAULT 0,
    icon VARCHAR(50) DEFAULT 'folder',
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES catalog_categories(id) ON DELETE CASCADE,
    INDEX idx_parent (parent_id),
    INDEX idx_path (path(255)),
    INDEX idx_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PDF Kataloglar
-- =====================================================
CREATE TABLE IF NOT EXISTS catalog_files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    filename VARCHAR(500) NOT NULL,
    filepath VARCHAR(1000) NOT NULL,
    file_size BIGINT DEFAULT 0,
    page_count INT DEFAULT 0,
    catalog_type ENUM('parts', 'repair', 'training', 'other') DEFAULT 'other',
    download_price DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES catalog_categories(id) ON DELETE CASCADE,
    INDEX idx_category (category_id),
    INDEX idx_type (catalog_type),
    INDEX idx_filename (filename(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Kullanıcı bakiyeleri
-- =====================================================
CREATE TABLE IF NOT EXISTS user_balances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    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,
    UNIQUE KEY unique_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Bakiye hareketleri
-- =====================================================
CREATE TABLE IF NOT EXISTS balance_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('deposit', 'download', 'translation', 'refund') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    balance_before DECIMAL(10,2) NOT NULL,
    balance_after DECIMAL(10,2) NOT NULL,
    reference_id INT NULL,
    reference_type VARCHAR(50) NULL,
    description VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, created_at),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- İndirme geçmişi
-- =====================================================
CREATE TABLE IF NOT EXISTS download_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    file_id INT NOT NULL,
    price_paid DECIMAL(10,2) NOT NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id) REFERENCES catalog_files(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_file (file_id),
    INDEX idx_date (downloaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Çeviri geçmişi
-- =====================================================
CREATE TABLE IF NOT EXISTS translation_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    file_id INT NOT NULL,
    page_numbers VARCHAR(500) NOT NULL,
    source_lang VARCHAR(10) DEFAULT 'EN',
    target_lang VARCHAR(10) DEFAULT 'TR',
    total_pages INT NOT NULL,
    price_per_page DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id) REFERENCES catalog_files(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_file (file_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Admin fiyat ayarları
-- =====================================================
CREATE TABLE IF NOT EXISTS pricing_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value DECIMAL(10,4) NOT NULL,
    description VARCHAR(255),
    updated_by INT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Varsayılan fiyat ayarları
-- =====================================================
INSERT INTO pricing_settings (setting_key, setting_value, description) VALUES
('download_price_per_mb', 0.50, 'MB başına indirme ücreti (TL)'),
('download_price_fixed', 5.00, 'Sabit indirme ücreti (TL)'),
('translation_price_per_page', 2.00, 'Sayfa başına çeviri ücreti (TL)'),
('deepl_api_cost_per_char', 0.00002, 'DeepL API karakter maliyeti (USD)'),
('translation_markup_percent', 50, 'Çeviri kar marjı (%)')
ON DUPLICATE KEY UPDATE 
    setting_value = VALUES(setting_value),
    description = VALUES(description);

-- =====================================================
-- Test kullanıcılarına başlangıç bakiyesi
-- =====================================================
INSERT INTO user_balances (user_id, balance) 
SELECT id, 100.00 FROM users WHERE username IN ('admin', 'osman')
ON DUPLICATE KEY UPDATE user_balances.balance = user_balances.balance + 0;

-- =====================================================
-- Tamamlandı!
-- =====================================================
SELECT 'Katalog veritabanı tabloları başarıyla oluşturuldu!' AS Status;

