-- Gelir Belgesi Doğrulama Sistemi Veritabanı Yapısı

-- Gelir belgesi dokümanları tablosu
CREATE TABLE IF NOT EXISTS income_verification_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    -- Belge bilgileri
    document_type ENUM('salary_slip', 'tax_return', 'bank_statement', 'employment_certificate', 'business_license', 'pension_statement', 'investment_statement', 'other') NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    file_type VARCHAR(50) NOT NULL,
    
    -- Gelir bilgileri
    monthly_income DECIMAL(15,2) DEFAULT NULL,
    annual_income DECIMAL(15,2) DEFAULT NULL,
    income_currency VARCHAR(3) DEFAULT 'TRY',
    income_source VARCHAR(255),
    employment_status ENUM('employed', 'self_employed', 'unemployed', 'retired', 'student', 'other') DEFAULT 'employed',
    employer_name VARCHAR(255),
    
    -- Belge tarihi ve geçerlilik
    document_date DATE,
    document_period_start DATE,
    document_period_end DATE,
    document_issuer VARCHAR(255),
    
    -- Doğrulama durumu
    status ENUM('pending', 'under_review', 'approved', 'rejected', 'expired', 'additional_required') DEFAULT 'pending',
    verification_level ENUM('basic', 'enhanced', 'premium') DEFAULT 'basic',
    
    -- İnceleme bilgileri
    reviewed_by INT DEFAULT NULL,
    reviewed_at TIMESTAMP NULL,
    review_notes TEXT,
    rejection_reason TEXT,
    additional_requirements TEXT,
    
    -- Otomatik kontrol sonuçları
    auto_check_results JSON,
    confidence_score DECIMAL(5,2) DEFAULT 0.00,
    risk_score DECIMAL(5,2) DEFAULT 0.00,
    
    -- Finansal analiz
    income_consistency_score DECIMAL(5,2) DEFAULT 0.00,
    spending_pattern_match BOOLEAN DEFAULT FALSE,
    income_verification_method ENUM('document_analysis', 'bank_integration', 'employer_verification', 'manual_review') DEFAULT 'document_analysis',
    
    -- Zaman bilgileri
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES kullanicilar(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES kullanicilar(id),
    
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_document_type (document_type),
    INDEX idx_employment_status (employment_status),
    INDEX idx_uploaded_at (uploaded_at),
    INDEX idx_expires_at (expires_at),
    INDEX idx_monthly_income (monthly_income),
    INDEX idx_verification_level (verification_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelir doğrulama kuralları tablosu
CREATE TABLE IF NOT EXISTS income_verification_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    rule_type ENUM('income_threshold', 'document_age', 'consistency_check', 'risk_assessment', 'employment_verification') NOT NULL,
    rule_config JSON NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 1,
    applies_to_verification_level ENUM('basic', 'enhanced', 'premium', 'all') DEFAULT 'all',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_rule_type (rule_type),
    INDEX idx_active (is_active),
    INDEX idx_priority (priority),
    INDEX idx_verification_level (applies_to_verification_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kullanıcı gelir profili tablosu
CREATE TABLE IF NOT EXISTS user_income_profile (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    -- Mevcut gelir bilgileri
    current_monthly_income DECIMAL(15,2) DEFAULT NULL,
    current_annual_income DECIMAL(15,2) DEFAULT NULL,
    verified_monthly_income DECIMAL(15,2) DEFAULT NULL,
    verified_annual_income DECIMAL(15,2) DEFAULT NULL,
    income_currency VARCHAR(3) DEFAULT 'TRY',
    
    -- İstihdam bilgileri
    employment_status ENUM('employed', 'self_employed', 'unemployed', 'retired', 'student', 'other') DEFAULT 'employed',
    employer_name VARCHAR(255),
    job_title VARCHAR(255),
    employment_start_date DATE,
    industry VARCHAR(100),
    
    -- Gelir kaynakları
    primary_income_source VARCHAR(255),
    secondary_income_sources JSON,
    has_multiple_income_sources BOOLEAN DEFAULT FALSE,
    
    -- Doğrulama durumu
    income_verification_status ENUM('unverified', 'partially_verified', 'fully_verified', 'expired') DEFAULT 'unverified',
    last_verification_date TIMESTAMP NULL,
    next_verification_due TIMESTAMP NULL,
    verification_frequency_months INT DEFAULT 12,
    
    -- Risk değerlendirmesi
    income_risk_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    income_consistency_rating DECIMAL(5,2) DEFAULT 0.00,
    spending_income_ratio DECIMAL(5,2) DEFAULT 0.00,
    
    -- Zaman bilgileri
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES kullanicilar(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_profile (user_id),
    
    INDEX idx_employment_status (employment_status),
    INDEX idx_verification_status (income_verification_status),
    INDEX idx_risk_level (income_risk_level),
    INDEX idx_next_verification (next_verification_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelir doğrulama logları tablosu
CREATE TABLE IF NOT EXISTS income_verification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    document_id INT,
    
    -- Log bilgileri
    action_type ENUM('upload', 'review', 'approve', 'reject', 'auto_check', 'expire', 'profile_update', 'risk_assessment') NOT NULL,
    action_by INT,
    action_details JSON,
    
    -- Değişiklik bilgileri
    old_values JSON,
    new_values JSON,
    
    -- IP ve cihaz bilgileri
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    -- Zaman bilgisi
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES kullanicilar(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES income_verification_documents(id) ON DELETE CASCADE,
    FOREIGN KEY (action_by) REFERENCES kullanicilar(id),
    
    INDEX idx_user_id (user_id),
    INDEX idx_document_id (document_id),
    INDEX idx_action_type (action_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelir doğrulama istatistikleri tablosu
CREATE TABLE IF NOT EXISTS income_verification_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    -- Belge istatistikleri
    total_documents_uploaded INT DEFAULT 0,
    approved_documents INT DEFAULT 0,
    rejected_documents INT DEFAULT 0,
    pending_documents INT DEFAULT 0,
    
    -- Doğrulama seviyeleri
    basic_verifications INT DEFAULT 0,
    enhanced_verifications INT DEFAULT 0,
    premium_verifications INT DEFAULT 0,
    
    -- Gelir trendi
    average_monthly_income DECIMAL(15,2) DEFAULT 0.00,
    income_trend ENUM('increasing', 'stable', 'decreasing', 'volatile') DEFAULT 'stable',
    income_volatility_score DECIMAL(5,2) DEFAULT 0.00,
    
    -- Zaman bilgileri
    first_upload_date TIMESTAMP NULL,
    last_upload_date TIMESTAMP NULL,
    last_approval_date TIMESTAMP NULL,
    
    -- Güncelleme zamanı
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES kullanicilar(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_stats (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelir karşılaştırma tablosu (harcama vs gelir analizi)
CREATE TABLE IF NOT EXISTS income_spending_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    analysis_period_start DATE NOT NULL,
    analysis_period_end DATE NOT NULL,
    
    -- Gelir bilgileri
    total_income DECIMAL(15,2) DEFAULT 0.00,
    verified_income DECIMAL(15,2) DEFAULT 0.00,
    
    -- Harcama bilgileri (bahis sitesindeki aktiviteler)
    total_deposits DECIMAL(15,2) DEFAULT 0.00,
    total_withdrawals DECIMAL(15,2) DEFAULT 0.00,
    total_bets DECIMAL(15,2) DEFAULT 0.00,
    net_gambling_spend DECIMAL(15,2) DEFAULT 0.00,
    
    -- Analiz sonuçları
    income_spending_ratio DECIMAL(5,2) DEFAULT 0.00,
    risk_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    affordability_score DECIMAL(5,2) DEFAULT 0.00,
    
    -- Uyarılar ve öneriler
    warnings JSON,
    recommendations JSON,
    
    -- Zaman bilgileri
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES kullanicilar(id) ON DELETE CASCADE,
    
    INDEX idx_user_id (user_id),
    INDEX idx_analysis_period (analysis_period_start, analysis_period_end),
    INDEX idx_risk_level (risk_level),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan doğrulama kurallarını ekle
INSERT INTO income_verification_rules (rule_name, rule_type, rule_config, applies_to_verification_level, priority) VALUES
('Minimum Gelir Eşiği', 'income_threshold', '{"min_monthly_income": 5000, "currency": "TRY", "warning_threshold": 3000}', 'basic', 1),
('Belge Yaşı Kontrolü', 'document_age', '{"max_age_months": 6, "warning_age_months": 3, "salary_slip_max_months": 3}', 'all', 2),
('Gelir Tutarlılık Kontrolü', 'consistency_check', '{"variance_threshold": 0.3, "min_documents_for_check": 2, "check_period_months": 6}', 'enhanced', 3),
('Risk Değerlendirmesi', 'risk_assessment', '{"high_risk_ratio": 0.5, "critical_risk_ratio": 0.8, "gambling_income_ratio_threshold": 0.3}', 'all', 4),
('İstihdam Doğrulaması', 'employment_verification', '{"require_employment_certificate": true, "verify_employer_details": true, "check_employment_duration": true}', 'premium', 5);

-- Trigger: Belge yüklendiğinde profil ve istatistikleri güncelle
DELIMITER //
CREATE TRIGGER update_income_stats_on_upload
AFTER INSERT ON income_verification_documents
FOR EACH ROW
BEGIN
    -- İstatistikleri güncelle
    INSERT INTO income_verification_stats (user_id, total_documents_uploaded, first_upload_date, last_upload_date)
    VALUES (NEW.user_id, 1, NEW.uploaded_at, NEW.uploaded_at)
    ON DUPLICATE KEY UPDATE 
    total_documents_uploaded = total_documents_uploaded + 1,
    last_upload_date = NEW.uploaded_at,
    first_upload_date = COALESCE(first_upload_date, NEW.uploaded_at);
    
    -- Profili güncelle (eğer gelir bilgisi varsa)
    IF NEW.monthly_income IS NOT NULL THEN
        INSERT INTO user_income_profile (user_id, current_monthly_income, current_annual_income, employment_status, employer_name)
        VALUES (NEW.user_id, NEW.monthly_income, NEW.annual_income, NEW.employment_status, NEW.employer_name)
        ON DUPLICATE KEY UPDATE 
        current_monthly_income = COALESCE(NEW.monthly_income, current_monthly_income),
        current_annual_income = COALESCE(NEW.annual_income, current_annual_income),
        employment_status = COALESCE(NEW.employment_status, employment_status),
        employer_name = COALESCE(NEW.employer_name, employer_name),
        updated_at = NOW();
    END IF;
END//
DELIMITER ;

-- Trigger: Belge durumu değiştiğinde istatistikleri güncelle
DELIMITER //
CREATE TRIGGER update_income_stats_on_status_change
AFTER UPDATE ON income_verification_documents
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        -- Eski durumu azalt
        CASE OLD.status
            WHEN 'pending' THEN
                UPDATE income_verification_stats SET pending_documents = pending_documents - 1 WHERE user_id = NEW.user_id;
            WHEN 'approved' THEN
                UPDATE income_verification_stats SET approved_documents = approved_documents - 1 WHERE user_id = NEW.user_id;
            WHEN 'rejected' THEN
                UPDATE income_verification_stats SET rejected_documents = rejected_documents - 1 WHERE user_id = NEW.user_id;
        END CASE;
        
        -- Yeni durumu artır
        CASE NEW.status
            WHEN 'pending' THEN
                UPDATE income_verification_stats SET pending_documents = pending_documents + 1 WHERE user_id = NEW.user_id;
            WHEN 'approved' THEN
                UPDATE income_verification_stats SET approved_documents = approved_documents + 1, last_approval_date = NEW.reviewed_at WHERE user_id = NEW.user_id;
                -- Doğrulanmış geliri güncelle
                UPDATE user_income_profile SET 
                    verified_monthly_income = NEW.monthly_income,
                    verified_annual_income = NEW.annual_income,
                    income_verification_status = 'fully_verified',
                    last_verification_date = NEW.reviewed_at,
                    next_verification_due = DATE_ADD(NEW.reviewed_at, INTERVAL verification_frequency_months MONTH)
                WHERE user_id = NEW.user_id;
            WHEN 'rejected' THEN
                UPDATE income_verification_stats SET rejected_documents = rejected_documents + 1 WHERE user_id = NEW.user_id;
        END CASE;
    END IF;
END//
DELIMITER ;

-- Trigger: Log kaydı oluştur
DELIMITER //
CREATE TRIGGER create_income_verification_log
AFTER INSERT ON income_verification_documents
FOR EACH ROW
BEGIN
    INSERT INTO income_verification_logs (user_id, document_id, action_type, action_details, ip_address)
    VALUES (NEW.user_id, NEW.id, 'upload', 
        JSON_OBJECT(
            'document_type', NEW.document_type, 
            'file_name', NEW.document_name,
            'monthly_income', NEW.monthly_income,
            'employment_status', NEW.employment_status
        ), 
        INET_ATON(CONNECTION_ID())
    );
END//
DELIMITER ;