-- ═══════════════════════════════════════════════════════════════════════════
-- PDF Analiz ve Soru-Cevap Cache Tabloları
-- Token tasarrufu için döküman analizleri ve soru-cevaplar saklanır
-- ═══════════════════════════════════════════════════════════════════════════

-- 1. PDF Analiz Cache Tablosu
-- Döküman ilk açıldığında analiz edilir ve sonuç saklanır
CREATE TABLE IF NOT EXISTS `pdf_analysis_cache` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pdf_path` VARCHAR(500) NOT NULL COMMENT 'PDF dosya yolu (relative)',
    `pdf_hash` VARCHAR(64) NOT NULL COMMENT 'PDF içerik hash (değişiklik tespiti)',
    `file_size_mb` DECIMAL(10,2) NOT NULL,
    
    -- Analiz sonuçları
    `document_type` VARCHAR(100) COMMENT 'Servis Kılavuzu, Yedek Parça Kataloğu, vb.',
    `brand` VARCHAR(100),
    `model` VARCHAR(100),
    `machine_type` VARCHAR(100) COMMENT 'Ekskavatör, Forklift, vb.',
    `year_range` VARCHAR(50),
    `summary` TEXT COMMENT 'Döküman özeti',
    `main_topics` JSON COMMENT 'Ana konular listesi',
    `suggested_questions` JSON COMMENT 'Önerilen sorular',
    `warning` TEXT,
    
    -- Meta bilgiler
    `tokens_used` INT UNSIGNED DEFAULT 0,
    `analysis_duration_ms` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- İndeksler
    UNIQUE KEY `idx_pdf_path` (`pdf_path`(255)),
    KEY `idx_pdf_hash` (`pdf_hash`),
    KEY `idx_brand_model` (`brand`, `model`),
    KEY `idx_document_type` (`document_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 2. PDF Soru-Cevap Cache Tablosu
-- Kullanıcıların sorduğu sorular ve cevaplar saklanır
CREATE TABLE IF NOT EXISTS `pdf_questions_cache` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pdf_path` VARCHAR(500) NOT NULL,
    `question_hash` VARCHAR(64) NOT NULL COMMENT 'Soru hash (aynı soru tespiti)',
    `question` TEXT NOT NULL,
    `answer` MEDIUMTEXT NOT NULL,
    
    -- Hangi sayfalar işlendi
    `pages_processed` VARCHAR(100) COMMENT '1,2,3 veya 1-10',
    
    -- AI bilgileri
    `model_used` VARCHAR(50) COMMENT 'gpt-4o, gemini-2.0-flash, vb.',
    `tokens_input` INT UNSIGNED DEFAULT 0,
    `tokens_output` INT UNSIGNED DEFAULT 0,
    `cost_usd` DECIMAL(10,6) DEFAULT 0,
    `duration_ms` INT UNSIGNED,
    
    -- Kullanım istatistikleri
    `view_count` INT UNSIGNED DEFAULT 1 COMMENT 'Kaç kez görüntülendi',
    `helpful_count` INT UNSIGNED DEFAULT 0 COMMENT 'Faydalı bulunma sayısı',
    `last_viewed_at` TIMESTAMP NULL,
    
    -- Meta
    `created_by` INT UNSIGNED COMMENT 'İlk soran kullanıcı ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- İndeksler
    UNIQUE KEY `idx_pdf_question` (`pdf_path`(255), `question_hash`),
    KEY `idx_pdf_path` (`pdf_path`(255)),
    KEY `idx_view_count` (`view_count`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 3. Popüler Sorular View (En çok sorulan sorular)
CREATE OR REPLACE VIEW `v_popular_questions` AS
SELECT 
    pqc.pdf_path,
    pqc.question,
    pqc.answer,
    pqc.view_count,
    pqc.helpful_count,
    pqc.created_at,
    pac.brand,
    pac.model,
    pac.document_type
FROM `pdf_questions_cache` pqc
LEFT JOIN `pdf_analysis_cache` pac ON pqc.pdf_path = pac.pdf_path
ORDER BY pqc.view_count DESC, pqc.helpful_count DESC;


-- 4. Marka bazlı soru istatistikleri
CREATE OR REPLACE VIEW `v_brand_question_stats` AS
SELECT 
    pac.brand,
    COUNT(DISTINCT pac.pdf_path) as document_count,
    COUNT(pqc.id) as total_questions,
    SUM(pqc.view_count) as total_views,
    ROUND(SUM(pqc.cost_usd), 4) as total_cost_usd
FROM `pdf_analysis_cache` pac
LEFT JOIN `pdf_questions_cache` pqc ON pac.pdf_path = pqc.pdf_path
GROUP BY pac.brand
ORDER BY total_questions DESC;


-- ═══════════════════════════════════════════════════════════════════════════
-- Örnek Kullanım
-- ═══════════════════════════════════════════════════════════════════════════

-- Analiz cache kontrol:
-- SELECT * FROM pdf_analysis_cache WHERE pdf_path = 'İŞ MAKİNASI GRUBU/HITACHI/ZX330/manual.pdf';

-- Aynı PDF için daha önce sorulan sorular:
-- SELECT question, answer, view_count FROM pdf_questions_cache 
-- WHERE pdf_path = 'İŞ MAKİNASI GRUBU/HITACHI/ZX330/manual.pdf'
-- ORDER BY view_count DESC LIMIT 10;

-- En popüler sorular (tüm sistem):
-- SELECT * FROM v_popular_questions LIMIT 20;

