-- Telefon Destek Sistemi Veritabanı
-- Oluşturma Tarihi: 2025-01-04

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

-- Kullanıcılar Tablosu
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(20),
    role ENUM('admin', 'personel') NOT NULL DEFAULT 'personel',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Kriterler Tablosu
CREATE TABLE IF NOT EXISTS criteria (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    point_value DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Gelen Arama Kayıtları Tablosu
CREATE TABLE IF NOT EXISTS incoming_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    call_date DATE NOT NULL,
    extension VARCHAR(50),
    call_count INT DEFAULT 0,
    answered_count INT DEFAULT 0,
    unanswered_count INT DEFAULT 0,
    answer_percentage DECIMAL(5, 2) DEFAULT 0.00,
    total_duration TIME DEFAULT '00:00:00',
    avg_duration TIME DEFAULT '00:00:00',
    total_wait TIME DEFAULT '00:00:00',
    avg_wait TIME DEFAULT '00:00:00',
    max_wait TIME DEFAULT '00:00: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,
    INDEX idx_user_date (user_id, call_date),
    INDEX idx_call_date (call_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Giden Arama Kayıtları Tablosu
CREATE TABLE IF NOT EXISTS outgoing_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    call_date DATE NOT NULL,
    call_count INT DEFAULT 0,
    total_duration TIME DEFAULT '00:00:00',
    avg_duration TIME DEFAULT '00:00: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,
    INDEX idx_user_date (user_id, call_date),
    INDEX idx_call_date (call_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Eğitim ve Kurulum Kayıtları Tablosu
CREATE TABLE IF NOT EXISTS training_installations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    criteria_id INT NOT NULL,
    record_date DATE NOT NULL,
    count INT DEFAULT 0,
    notes TEXT,
    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 (criteria_id) REFERENCES criteria(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, record_date),
    INDEX idx_criteria (criteria_id),
    INDEX idx_date (record_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Sistem Ayarları Tablosu
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Varsayılan Admin Kullanıcısı (Şifre: admin123)
INSERT INTO users (username, password, full_name, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Sistem Yöneticisi', 'admin');

-- Varsayılan Kriterler
INSERT INTO criteria (name, code, point_value, display_order) VALUES
('Gelen Arama Sayısı', 'gelen_arama_sayisi', 0.15, 1),
('Gelen Arama Süresi', 'gelen_arama_suresi', 0.30, 2),
('Giden Arama Sayısı', 'giden_arama_sayisi', 0.05, 3),
('Giden Arama Süresi', 'giden_arama_suresi', 0.10, 4),
('Eğitim Oranı', 'egitim_orani', 0.40, 5),
('Profesyonel Paket', 'profesyonel_paket', 1.00, 6),
('Gelişmiş Paket', 'gelismis_paket', 1.00, 7),
('Uzman Paket', 'uzman_paket', 1.00, 8),
('Ideasoft ERP', 'ideasoft_erp', 0.50, 9),
('Ideasoft Mini 2', 'ideasoft_mini_2', 1.00, 10),
('Ideasoft Mini 3', 'ideasoft_mini_3', 1.00, 11),
('Ideasoft Maksi', 'ideasoft_maksi', 1.00, 12),
('Sopyo Logo', 'sopyo_logo', 1.00, 13),
('Sopyo Nebim v3', 'sopyo_nebim_v3', 1.00, 14),
('Lisans Yenileme', 'lisans_yenileme', 0.25, 15),
('Google Yorum', 'google_yorum', 1.00, 16);

-- Varsayılan Ayarlar
INSERT INTO settings (setting_key, setting_value, description) VALUES
('site_name', 'Destek Performans Sistemi', 'Site adı'),
('company_name', 'Teknik Destek Firması', 'Firma adı'),
('timezone', 'Europe/Istanbul', 'Saat dilimi');
