-- Müşteri (Cari) Takibi Tablosu
-- Musterilerden alınan cekler için musteri takibi

CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(150),
    phone VARCHAR(50),
    email VARCHAR(100),
    address TEXT,
    tax_office VARCHAR(100),
    tax_number VARCHAR(50),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_customer_name (customer_name),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Müşteri İşlem Takibi Tablosu
-- Her müşteri için satış, tahsilat, iade ve düzeltme işlemlerini takip eder

CREATE TABLE IF NOT EXISTS customer_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    transaction_type ENUM('sale', 'collection', 'refund', 'adjustment') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    transaction_date DATE NOT NULL,
    reference_number VARCHAR(100),
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_customer_trans (customer_id, transaction_date),
    INDEX idx_trans_type (transaction_type),
    INDEX idx_trans_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Alınan Çekler Tablosu
-- Müşterilerden alınan çekler burada takip edilir.

CREATE TABLE IF NOT EXISTS received_checks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(200) NOT NULL,
    bank_name VARCHAR(100),
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    check_number VARCHAR(100),
    status ENUM('BEKLEMEDE', 'TAHSIL_EDILDI', 'IADE', 'CIRO_EDILDI') DEFAULT 'BEKLEMEDE',DESCRIPTION TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_due_date (due_date),
    INDEX idx_status (status),
    INDEX idx_customer_name (customer_name),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Verilen Çekler Tablosu Genişletilmiş
-- Tedarikcilere verilen cekler (supplier_checks zaten var, ama genel bir cek tablosu da olabilir)
-- supplier_checks tablosu zaten var, bu yüzden başka bir tablo oluşturmaya gerek yok
