-- ============================================================================
-- Banka Borç Takip Sistemi Yeniden Yapılandırma
--
-- Bu migration 3 farklı banka borç tipini ayrı tablolarda takip etmek için:
-- 1. BCH (Banka Cari Hesabı) - Tek seferlik sabit ödemeler
-- 2. KMH (Kredili Mevduat Hesabı) - Değişken bakiyeli kredi limitleri
-- 3. Aylık Eş Taksitli Krediler - Sabit aylık taksitli krediler
-- ============================================================================

-- ============================================================================
-- 1. BCH (Banka Cari Hesabı) Tablosu
-- Pattern: suppliers tablosu gibi (tek borç kaydı)
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_bch (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_name VARCHAR(100) NOT NULL COMMENT 'Banka adı',
    amount DECIMAL(15,2) NOT NULL COMMENT 'Borç tutarı',
    due_date DATE NOT NULL COMMENT 'Vade tarihi',
    status ENUM('BEKLEMEDE', 'ÖDEME') DEFAULT 'BEKLEMEDE' COMMENT 'Ödeme durumu',
    payment_date DATE COMMENT 'Ödenme tarihi',
    reference_number VARCHAR(100) COMMENT 'Referans/Dekont numarası',
    description TEXT COMMENT 'Açıklama',
    created_by INT COMMENT 'Kaydı oluşturan kullanıcı',
    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_bank_name (bank_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci COMMENT='BCH tek seferlik banka ödemeleri';

-- ============================================================================
-- 2. Banks Tablosu Güncelleme (KMH için özelleştirme)
-- Pattern: Mevcut yapı KMH için perfect
-- ============================================================================
ALTER TABLE banks
    MODIFY COLUMN account_type ENUM('kmh') DEFAULT 'kmh' COMMENT 'Artık sadece KMH için kullanılıyor',
    ADD COLUMN IF NOT EXISTS account_subtype VARCHAR(50) COMMENT 'Alt tip: BİREYSEL, TİCARİ vb.' AFTER account_type,
    ADD COLUMN IF NOT EXISTS status ENUM('BEKLEMEDE', 'ÖDENDİ') DEFAULT 'BEKLEMEDE' COMMENT 'Genel durum' AFTER account_subtype;

-- Index ekle (eğer yoksa)
ALTER TABLE banks ADD INDEX IF NOT EXISTS idx_status (status);
ALTER TABLE banks ADD INDEX IF NOT EXISTS idx_account_subtype (account_subtype);

-- ============================================================================
-- 3. Taksitli Krediler Ana Tablosu
-- Pattern: monthly_payments gibi (master table)
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_installment_loans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_name VARCHAR(100) NOT NULL COMMENT 'Banka adı',
    loan_amount DECIMAL(15,2) NOT NULL COMMENT 'Toplam kredi tutarı',
    monthly_payment DECIMAL(15,2) NOT NULL COMMENT 'Aylık taksit tutarı',
    total_installments INT NOT NULL COMMENT 'Toplam taksit sayısı',
    remaining_installments INT NOT NULL COMMENT 'Kalan taksit sayısı',
    start_date DATE NOT NULL COMMENT 'Kredi başlangıç tarihi',
    next_payment_date DATE NOT NULL COMMENT 'Sonraki ödeme tarihi',
    status ENUM('DEVAM EDİYOR', 'KREDİ', 'TAMAMLANDI') DEFAULT 'DEVAM EDİYOR' COMMENT 'Kredi durumu',
    description TEXT COMMENT 'Açıklama',
    created_by INT COMMENT 'Kaydı oluşturan kullanıcı',
    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_next_payment (next_payment_date),
    INDEX idx_status (status),
    INDEX idx_bank_name (bank_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci COMMENT='Aylık eş taksitli krediler';

-- ============================================================================
-- 4. Taksit Ödeme Geçmişi Tablosu
-- Pattern: monthly_payment_details gibi (detail table)
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_installment_payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    loan_id INT NOT NULL COMMENT 'Kredi ID (bank_installment_loans)',
    payment_number INT NOT NULL COMMENT 'Taksit numarası (1, 2, 3...)',
    payment_date DATE NOT NULL COMMENT 'Planlanan ödeme tarihi',
    amount DECIMAL(15,2) NOT NULL COMMENT 'Taksit tutarı',
    status ENUM('BEKLEMEDE', 'ÖDENDİ') DEFAULT 'BEKLEMEDE' COMMENT 'Ödeme durumu',
    paid_date DATE COMMENT 'Gerçek ödenme tarihi',
    description TEXT COMMENT 'Açıklama',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (loan_id) REFERENCES bank_installment_loans(id) ON DELETE CASCADE,
    INDEX idx_loan_payment (loan_id, payment_date),
    INDEX idx_status (status),
    INDEX idx_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci COMMENT='Taksitli kredi ödeme detayları';

-- ============================================================================
-- 5. v_all_payments VIEW Güncelleme (BCH ve Taksit ödemelerini ekle)
-- ============================================================================
DROP VIEW IF EXISTS v_all_payments;

CREATE VIEW v_all_payments AS
-- Tedarikçi ödemeleri
SELECT
    'supplier' as source,
    s.id as source_id,
    s.due_date as payment_date,
    s.supplier_name as payment_name,
    s.invoice_number as detail,
    s.amount,
    s.status,
    'Tedarikçi' as category
FROM suppliers s
WHERE s.status IN ('pending', 'payment_requested')

UNION ALL

-- Aylık ödemeler
SELECT
    'monthly_payment' as source,
    mpd.id as source_id,
    DATE_FORMAT(CONCAT(mpd.year, '-', LPAD(mpd.month, 2, '0'), '-', mp.payment_day), '%Y-%m-%d') as payment_date,
    mp.payment_name,
    CONCAT(mpd.year, '/', mpd.month) as detail,
    mpd.amount,
    mpd.status,
    mp.category
FROM monthly_payment_details mpd
JOIN monthly_payments mp ON mpd.payment_id = mp.id
WHERE mpd.status = 'pending'

UNION ALL

-- Kredi kartı ödemeleri
SELECT
    'credit_card' as source,
    ccp.id as source_id,
    DATE_FORMAT(CONCAT(ccp.year, '-', LPAD(ccp.month, 2, '0'), '-', cc.payment_due_day), '%Y-%m-%d') as payment_date,
    CONCAT(cc.bank_name, ' - ', cc.card_name) as payment_name,
    CONCAT(ccp.year, '/', ccp.month) as detail,
    ccp.minimum_payment as amount,
    ccp.payment_status as status,
    'Kredi Kartı' as category
FROM credit_card_payments ccp
JOIN credit_cards cc ON ccp.card_id = cc.id
WHERE ccp.payment_status IN ('pending', 'partial')

UNION ALL

-- BCH ödemeleri (YENİ!)
SELECT
    'bank_bch' as source,
    id as source_id,
    due_date as payment_date,
    CONCAT('BCH: ', bank_name) as payment_name,
    reference_number as detail,
    amount,
    status,
    'Banka Borcu (BCH)' as category
FROM bank_bch
WHERE status = 'BEKLEMEDE'

UNION ALL

-- Taksitli kredi ödemeleri (YENİ!)
SELECT
    'bank_installment' as source,
    p.id as source_id,
    p.payment_date,
    CONCAT('Kredi Taksit: ', l.bank_name) as payment_name,
    CONCAT('Taksit ', p.payment_number, '/', l.total_installments) as detail,
    p.amount,
    p.status,
    'Kredi Taksiti' as category
FROM bank_installment_payments p
JOIN bank_installment_loans l ON p.loan_id = l.id
WHERE p.status = 'BEKLEMEDE'

ORDER BY payment_date ASC;

-- ============================================================================
-- Migration Tamamlandı!
-- ============================================================================
-- Kullanım:
-- - BCH ödemeleri: bank_bch tablosunda
-- - KMH hesapları: banks tablosunda (account_type='kmh')
-- - Taksitli krediler: bank_installment_loans + bank_installment_payments
-- ============================================================================
