-- KESKA Mali Takip Sistemi - Veritabanı Şeması
-- Veritabanı: keska_db

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

-- Tedarikçiler ve Çek Takibi
CREATE TABLE suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_name VARCHAR(200) NOT NULL,
    invoice_number VARCHAR(100),
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE,
    description TEXT,
    status ENUM('pending', 'paid', 'overdue', 'payment_requested') DEFAULT 'pending',
    payment_date DATE,
    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_due_date (due_date),
    INDEX idx_status (status),
    INDEX idx_supplier_name (supplier_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Banka Takibi
CREATE TABLE banks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_name VARCHAR(100) NOT NULL,
    limit_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    used_amount DECIMAL(15,2) DEFAULT 0,
    available_amount DECIMAL(15,2) GENERATED ALWAYS AS (limit_amount - used_amount) STORED,
    account_type ENUM('kmh', 'current', 'deposit', 'bcl', 'installment') DEFAULT 'kmh',
    last_update_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_bank_name (bank_name),
    INDEX idx_account_type (account_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Banka İşlem Geçmişi
CREATE TABLE bank_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_id INT NOT NULL,
    transaction_type ENUM('borrow', 'payment', 'adjustment') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_id) REFERENCES banks(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_bank_trans (bank_id, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Kredi Kartı Ödemeleri
CREATE TABLE credit_card_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    card_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL CHECK (month BETWEEN 1 AND 12),
    minimum_payment DECIMAL(15,2),
    total_amount DECIMAL(15,2),
    payment_status ENUM('pending', 'paid', 'partial') DEFAULT 'pending',
    paid_amount DECIMAL(15,2) DEFAULT 0,
    payment_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (card_id) REFERENCES credit_cards(id) ON DELETE CASCADE,
    UNIQUE KEY unique_card_month (card_id, year, month),
    INDEX idx_payment_date (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Aylık Ödemeler (Tekrarlayan)
CREATE TABLE monthly_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment_name VARCHAR(100) NOT NULL,
    payment_day INT CHECK (payment_day BETWEEN 1 AND 31),
    category ENUM('kdv', 'sgk', 'rent', 'electricity', 'water', 'gas', 'phone', 'internet', 'salary', 'other') DEFAULT 'other',
    is_recurring BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payment_name (payment_name),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Aylık Ödeme Detayları
CREATE TABLE monthly_payment_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL CHECK (month BETWEEN 1 AND 12),
    amount DECIMAL(15,2),
    status ENUM('pending', 'paid', 'uncertain') DEFAULT 'pending',
    payment_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES monthly_payments(id) ON DELETE CASCADE,
    UNIQUE KEY unique_payment_month (payment_id, year, month),
    INDEX idx_month_year (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Tüm Ödemeler Konsolide Tablo
CREATE TABLE all_payments_consolidated (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment_date DATE NOT NULL,
    payment_name VARCHAR(200) NOT NULL,
    detail TEXT,
    amount DECIMAL(15,2) NOT NULL,
    source VARCHAR(50) NOT NULL,
    source_id INT NOT NULL,
    status ENUM('pending', 'paid', 'overdue', 'uncertain') DEFAULT 'pending',
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payment_date (payment_date),
    INDEX idx_source (source, source_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Bildirimler
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('warning', 'info', 'danger', 'success') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    related_payment_source VARCHAR(50),
    related_payment_id INT,
    notify_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read),
    INDEX idx_notify_date (notify_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Excel İçe Aktarma Geçmişi
CREATE TABLE import_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500),
    imported_by INT,
    import_type VARCHAR(50),
    records_imported INT DEFAULT 0,
    status ENUM('success', 'partial', 'failed') DEFAULT 'success',
    error_log TEXT,
    imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_imported_at (imported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Sistem Ayarları
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Aktivite Logları
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_action (user_id, action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Bazı Sistem Ayarları
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('app_name', 'KESKA Mali Takip Sistemi', 'string', 'Uygulama adı'),
('currency', 'TL', 'string', 'Para birimi'),
('date_format', 'd.m.Y', 'string', 'Tarih formatı'),
('notification_days', '7,3,1', 'string', 'Bildirim günleri (virgülle ayır)'),
('enable_notifications', '1', 'boolean', 'Bildirimleri aktif et');
