

-- Veli bilgileri tablosu (API için)
CREATE TABLE IF NOT EXISTS parents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    phone VARCHAR(20),
    address 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,
    INDEX idx_user_id (user_id)
);

-- Sporcular tablosu
CREATE TABLE IF NOT EXISTS athletes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    age INT,
    parent_id INT,
    phone VARCHAR(20),
    address TEXT,
    emergency_contact VARCHAR(20),
    registration_date DATE DEFAULT CURRENT_DATE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Antrenman programları tablosu
CREATE TABLE IF NOT EXISTS training_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    athlete_id INT NOT NULL,
    day_of_week ENUM('Pazartesi', 'Salı', 'Çarşamba', 'Perşembe', 'Cuma', 'Cumartesi', 'Pazar') NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    location VARCHAR(100),
    coach_name VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (athlete_id) REFERENCES athletes(id) ON DELETE CASCADE
);

-- Aidat tablosu
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    athlete_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_month VARCHAR(7) NOT NULL, -- YYYY-MM formatında
    payment_date DATE,
    status ENUM('paid', 'pending', 'overdue') DEFAULT 'pending',
    payment_method VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (athlete_id) REFERENCES athletes(id) ON DELETE CASCADE,
    INDEX idx_payments_athlete_month (athlete_id, payment_month)
);

-- Yoklama tablosu (yıl/ay alanları eklendi)
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    athlete_id INT NOT NULL,
    training_date DATE NOT NULL,
    year INT NOT NULL,
    month TINYINT NOT NULL, -- 1-12
    status ENUM('present', 'absent', 'excused') DEFAULT 'present',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (athlete_id) REFERENCES athletes(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_attendance_athlete_year_month (athlete_id, year, month),
    INDEX idx_attendance_date (training_date)
);

-- Bildirimler tablosu
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    target_type ENUM('all', 'specific') DEFAULT 'all',
    target_users TEXT, -- JSON formatında kullanıcı ID'leri
    sent_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- API ayarları tablosu
CREATE TABLE IF NOT EXISTS api_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Bildirim şablonları tablosu
CREATE TABLE IF NOT EXISTS notification_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    title_template VARCHAR(200) NOT NULL,
    message_template TEXT NOT NULL,
    category ENUM('general', 'payment', 'training', 'attendance', 'urgent') DEFAULT 'general',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Varsayılan bildirim şablonları
INSERT INTO notification_templates (template_name, title_template, message_template, category) VALUES
('Aidat Hatırlatması', 'Aidat Ödemesi', 'Sayın {parent_name}, {athlete_name} için {amount} TL aidat ödemesi beklenmektedir. Lütfen ödemenizi yapınız.', 'payment'),
('Antrenman İptali', 'Antrenman İptal', 'Sayın {parent_name}, {athlete_name} için {date} tarihli antrenman iptal edilmiştir. Detaylar için uygulamayı kontrol ediniz.', 'training'),
('Yoklama Bilgisi', 'Yoklama Durumu', 'Sayın {parent_name}, {athlete_name} {date} tarihli antrenmana {status} olarak kaydedilmiştir.', 'attendance'),
('Genel Duyuru', 'Önemli Duyuru', 'Sayın {parent_name}, {message}', 'general'),
('Acil Bildirim', 'ACİL', 'Sayın {parent_name}, {message}', 'urgent')
ON DUPLICATE KEY UPDATE template_name=template_name;

-- Varsayılan API ayarları
INSERT INTO api_settings (setting_key, setting_value, description) VALUES
('firebase_server_key', '', 'Firebase Cloud Messaging Server Key'),
('jwt_secret', '', 'JWT Token için gizli anahtar'),
('api_base_url', 'http://localhost/1910/api', 'API temel URL'),
('notification_enabled', '1', 'Bildirim sistemi aktif mi (1: Evet, 0: Hayır)'),
('app_version', '1.0.0', 'Android uygulama versiyonu')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- Kasa işlemleri tablosu
CREATE TABLE IF NOT EXISTS cash_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_type ENUM('income', 'expense') NOT NULL,
    category VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    payment_method ENUM('cash', 'bank_transfer', 'credit_card', 'check') DEFAULT 'cash',
    transaction_date DATE NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_transaction_type (transaction_type)
);

-- Kasa kategorileri tablosu
CREATE TABLE IF NOT EXISTS cash_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    category_type ENUM('income', 'expense') NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Varsayılan kasa kategorileri
INSERT INTO cash_categories (category_name, category_type, description) VALUES
-- Gelir kategorileri
('Aidat Ödemeleri', 'income', 'Sporcu aidat ödemeleri'),
('Turnuva Gelirleri', 'income', 'Turnuva katılım ücretleri'),
('Sponsorluk', 'income', 'Sponsorluk gelirleri'),
('Diğer Gelirler', 'income', 'Diğer gelir kalemleri'),
-- Gider kategorileri
('Antrenör Maaşları', 'expense', 'Antrenör maaş ödemeleri'),
('Tesis Kiralama', 'expense', 'Spor tesis kiralama giderleri'),
('Malzeme Giderleri', 'expense', 'Spor malzemeleri ve ekipman'),
('Ulaşım Giderleri', 'expense', 'Turnuva ve etkinlik ulaşım giderleri'),
('Yemek Giderleri', 'expense', 'Yemek ve ikram giderleri'),
('Diğer Giderler', 'expense', 'Diğer gider kalemleri')
ON DUPLICATE KEY UPDATE category_name=category_name;

-- Yedekleme geçmişi tablosu
CREATE TABLE IF NOT EXISTS backup_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_name VARCHAR(255) NOT NULL,
    backup_path VARCHAR(500) NOT NULL,
    backup_size BIGINT NOT NULL,
    backup_type ENUM('manual', 'automatic') DEFAULT 'manual',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_created_at (created_at)
);

-- Sistem ayarları tablosu
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Varsayılan sistem ayarları
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('auto_backup_enabled', '0', 'Otomatik yedekleme aktif mi (1: Evet, 0: Hayır)'),
('backup_frequency', 'daily', 'Yedekleme sıklığı (hourly, daily, weekly, monthly)'),
('backup_retention', '30', 'Yedekleme saklama süresi (gün)'),
('system_name', 'BAŞKENT 1910 GÜCÜ SK', 'Sistem adı'),
('system_version', '1.0.0', 'Sistem versiyonu'),
('maintenance_mode', '0', 'Bakım modu (1: Aktif, 0: Pasif)')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- API istekleri log tablosu
CREATE TABLE IF NOT EXISTS api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_data TEXT,
    response_status INT,
    response_time INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES api_users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_endpoint (endpoint),
    INDEX idx_created_at (created_at)
);

-- Email geçmişi tablosu
CREATE TABLE IF NOT EXISTS email_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    recipient_count INT NOT NULL DEFAULT 0,
    success_count INT NOT NULL DEFAULT 0,
    error_count INT NOT NULL DEFAULT 0,
    sent_by INT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_sent_at (sent_at),
    INDEX idx_sent_by (sent_by)
);

-- Ders Notları tablosu
CREATE TABLE IF NOT EXISTS lesson_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(100) DEFAULT 'Genel',
    author VARCHAR(100) DEFAULT 'Admin',
    is_published BOOLEAN DEFAULT TRUE,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    tags TEXT,
    image_path VARCHAR(500) NULL,
    image_alt VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_published (is_published),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at)
);