-- Apartman Yönetim Sistemi Veritabanı Şeması

CREATE DATABASE IF NOT EXISTS apartman_yonetim CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE apartman_yonetim;

-- Kullanıcılar tablosu (yönetim firması personeli)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tc_no VARCHAR(11) UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    password VARCHAR(255) NOT NULL,
    role INT NOT NULL DEFAULT 3, -- 1: Super Admin, 2: Admin, 3: User, 4: Lawyer, 5: Supplier
    permissions TEXT, -- JSON formatında modül izinleri
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Siteler/Binalar tablosu
CREATE TABLE IF NOT EXISTS buildings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    address TEXT,
    block_count INT DEFAULT 1,
    apartment_count_per_block INT DEFAULT 0,
    shop_count INT DEFAULT 0,
    monthly_fee DECIMAL(10,2) DEFAULT 0.00,
    contract_file VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bloklar tablosu
CREATE TABLE IF NOT EXISTS blocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    block_name VARCHAR(50) NOT NULL,
    apartment_count INT DEFAULT 0,
    shop_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    INDEX idx_building (building_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daireler tablosu
CREATE TABLE IF NOT EXISTS apartments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    block_id INT,
    apartment_number VARCHAR(20) NOT NULL,
    apartment_type ENUM('apartment', 'shop') DEFAULT 'apartment',
    floor_number INT,
    square_meters DECIMAL(10,2),
    room_count VARCHAR(20),
    bathroom_count INT DEFAULT 1,
    has_balcony TINYINT(1) DEFAULT 0,
    has_parking TINYINT(1) DEFAULT 0,
    parking_number VARCHAR(20),
    has_storage TINYINT(1) DEFAULT 0,
    storage_number VARCHAR(20),
    heating_type ENUM('central', 'individual', 'none') DEFAULT 'central',
    furnishing_status ENUM('furnished', 'semi_furnished', 'unfurnished') DEFAULT 'unfurnished',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_block (block_id),
    UNIQUE KEY unique_apartment (building_id, block_id, apartment_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sakinler/Malikler tablosu
CREATE TABLE IF NOT EXISTS residents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    apartment_id INT NOT NULL,
    tc_no VARCHAR(11),
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    relationship_type ENUM('owner', 'tenant', 'former_tenant', 'former_owner') NOT NULL,
    move_in_date DATE,
    move_out_date DATE,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (apartment_id) REFERENCES apartments(id) ON DELETE CASCADE,
    INDEX idx_phone (phone),
    INDEX idx_building (building_id),
    INDEX idx_apartment (apartment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Borçlandırma kategorileri tablosu (debts tablosundan önce oluşturulmalı)
CREATE TABLE IF NOT EXISTS debt_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_name (name),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Borçlar tablosu
CREATE TABLE IF NOT EXISTS debts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    resident_id INT NULL,
    apartment_id INT NOT NULL,
    category_id INT NULL,
    debt_type VARCHAR(50) NOT NULL, -- 'aidat', 'ek_odeme', 'borc', vb.
    amount DECIMAL(10,2) NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('beklemede', 'gecikmis', 'odendi', 'iptal') DEFAULT 'beklemede',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL,
    FOREIGN KEY (apartment_id) REFERENCES apartments(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES debt_categories(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_resident (resident_id),
    INDEX idx_apartment (apartment_id),
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ödemeler tablosu
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    resident_id INT,
    debt_id INT,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('cash', 'credit_card', 'bank_transfer') DEFAULT 'cash',
    transaction_id VARCHAR(100),
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL,
    FOREIGN KEY (debt_id) REFERENCES debts(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_resident (resident_id),
    INDEX idx_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sakin bakiye/avans tablosu
CREATE TABLE IF NOT EXISTS resident_credits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    resident_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    transaction_type ENUM('credit', 'usage') NOT NULL, -- credit: bakiye ekleme, usage: bakiye kullanma
    description TEXT,
    related_payment_id INT NULL, -- Fazla ödeme geldiğinde ilişkili payment
    related_debt_id INT NULL, -- Bakiye kullanıldığında ilişkili borç
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE,
    FOREIGN KEY (related_payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    FOREIGN KEY (related_debt_id) REFERENCES debts(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_resident (resident_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelirler tablosu
CREATE TABLE IF NOT EXISTS income (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NOT NULL, -- 'aidat', 'ek_odeme', 'borc_tahsili', 'kira_tahsili', 'reklam_geliri', 'diger'
    description TEXT,
    income_date DATE NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_category (category),
    INDEX idx_income_date (income_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tedarikçiler tablosu (expenses'ten önce oluşturulmalı)
CREATE TABLE IF NOT EXISTS suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    tax_number VARCHAR(50),
    tax_office VARCHAR(100),
    category VARCHAR(100), -- Hizmet kategorisi
    notes TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_name (company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Giderler tablosu
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    supplier_id INT,
    amount DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    invoice_number VARCHAR(100),
    invoice_date DATE,
    expense_date DATE NOT NULL,
    due_date DATE,
    is_paid TINYINT(1) DEFAULT 0,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_category (category),
    INDEX idx_expense_date (expense_date),
    INDEX idx_is_paid (is_paid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tedarikçi ödemeleri tablosu
CREATE TABLE IF NOT EXISTS supplier_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    supplier_id INT NOT NULL,
    expense_id INT,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'check', 'credit_card') DEFAULT 'bank_transfer',
    bank_account_id INT,
    check_number VARCHAR(50),
    transaction_reference VARCHAR(100),
    description TEXT,
    document_file VARCHAR(255),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_expense (expense_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_bank_account (bank_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- İş emirleri tablosu
CREATE TABLE IF NOT EXISTS work_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('beklemede', 'devam_ediyor', 'kontrol_bekliyor', 'tamamlandi', 'iptal') DEFAULT 'beklemede',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    assigned_to INT, -- user_id veya supplier_id
    assigned_type ENUM('user', 'supplier') DEFAULT 'user',
    due_date DATE,
    completed_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_status (status),
    INDEX idx_assigned (assigned_to),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- İş emri dosyaları tablosu
CREATE TABLE IF NOT EXISTS work_order_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    work_order_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    uploaded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_work_order (work_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mesajlar tablosu
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT,
    sender_id INT, -- user_id veya resident_id
    sender_type ENUM('user', 'resident') NOT NULL,
    receiver_id INT, -- user_id veya resident_id
    receiver_type VARCHAR(30) NOT NULL DEFAULT 'all',
    subject VARCHAR(200),
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    INDEX idx_sender (sender_id, sender_type),
    INDEX idx_receiver (receiver_id, receiver_type),
    INDEX idx_building (building_id),
    INDEX idx_is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mesaj dosyaları tablosu
CREATE TABLE IF NOT EXISTS message_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    INDEX idx_message (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bildirimler tablosu
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    resident_id INT,
    type VARCHAR(50) NOT NULL, -- 'payment', 'work_order', 'message', 'debt', vb.
    title VARCHAR(200) NOT NULL,
    message TEXT,
    is_read TINYINT(1) DEFAULT 0,
    related_id INT, -- İlgili kaydın ID'si
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_resident (resident_id),
    INDEX idx_is_read (is_read),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMS şablonları tablosu
CREATE TABLE IF NOT EXISTS sms_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    template TEXT NOT NULL,
    variables TEXT, -- JSON formatında değişkenler
    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_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMS gönderim geçmişi tablosu
CREATE TABLE IF NOT EXISTS sms_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_phone VARCHAR(20) NOT NULL,
    recipient_name VARCHAR(100),
    message TEXT NOT NULL,
    template_id INT,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    sent_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES sms_templates(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_recipient (recipient_phone),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- İcra işlemleri tablosu
CREATE TABLE IF NOT EXISTS enforcement_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    resident_id INT NOT NULL,
    debt_id INT,
    total_debt DECIMAL(10,2) NOT NULL,
    sms_sent TINYINT(1) DEFAULT 0,
    sms_sent_at TIMESTAMP NULL,
    work_order_id INT, -- Avukata açılan iş emri
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE,
    FOREIGN KEY (debt_id) REFERENCES debts(id) ON DELETE SET NULL,
    FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_resident (resident_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Toplu borçlandırma tablosu
CREATE TABLE IF NOT EXISTS bulk_debts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    block_id INT,
    category_id INT,
    debt_type VARCHAR(50) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    per_unit_amount DECIMAL(10,2) DEFAULT 0,
    affects ENUM('owner', 'tenant_or_owner') DEFAULT 'tenant_or_owner',
    distribution_type VARCHAR(20) DEFAULT 'equal',
    is_individual TINYINT(1) DEFAULT 0,
    description TEXT,
    due_date DATE NOT NULL,
    status ENUM('active', 'cancelled', 'completed') DEFAULT 'active',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_block (block_id),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan borç kategorileri
INSERT INTO debt_categories (name) VALUES 
('Aidat'),
('Yakıt'),
('Asansör Bakım'),
('Temizlik'),
('Güvenlik'),
('Bahçe Bakım'),
('Ortak Alan Elektrik'),
('Su'),
('Tamirat'),
('Diğer');

-- Toplu borçlandırma detayları tablosu
CREATE TABLE IF NOT EXISTS bulk_debt_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bulk_debt_id INT NOT NULL,
    resident_id INT NULL,
    apartment_id INT NOT NULL,
    debt_id INT,
    calculated_amount DECIMAL(10,2) DEFAULT 0,
    final_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bulk_debt_id) REFERENCES bulk_debts(id) ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL,
    FOREIGN KEY (apartment_id) REFERENCES apartments(id) ON DELETE CASCADE,
    FOREIGN KEY (debt_id) REFERENCES debts(id) ON DELETE SET NULL,
    INDEX idx_bulk_debt (bulk_debt_id),
    INDEX idx_resident (resident_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gider kategorileri tablosu
CREATE TABLE IF NOT EXISTS expense_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan gider kategorileri
INSERT INTO expense_categories (name) VALUES 
('Temizlik'),
('Bakım'),
('Onarım'),
('Elektrik'),
('Su'),
('Doğalgaz'),
('Asansör'),
('Güvenlik'),
('Bahçe'),
('Personel'),
('Sigorta'),
('Diğer');

-- Gelir kategorileri tablosu
CREATE TABLE IF NOT EXISTS income_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan gelir kategorileri
INSERT INTO income_categories (name) VALUES 
('Aidat'),
('Ek Ödeme'),
('Borç Tahsili'),
('Kira Tahsili'),
('Reklam Geliri'),
('Devir'),
('Otopark Geliri'),
('Ortak Alan Kirası'),
('Diğer');

-- Otomatik borçlandırma tablosu
CREATE TABLE IF NOT EXISTS auto_debts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    block_id INT,
    category_id INT,
    debt_type VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    affects ENUM('owner', 'tenant_or_owner') DEFAULT 'tenant_or_owner',
    description TEXT,
    frequency ENUM('monthly', 'bimonthly', 'quarterly', 'semiannual', 'annual') DEFAULT 'monthly',
    day_of_month INT DEFAULT 1,
    due_day_offset INT DEFAULT 30,
    is_active TINYINT(1) DEFAULT 1,
    last_run_date DATE,
    next_run_date DATE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES debt_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_is_active (is_active),
    INDEX idx_next_run (next_run_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kasa işlemleri tablosu (detaylı takip için)
CREATE TABLE IF NOT EXISTS cash_register (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    transaction_type ENUM('income', 'expense') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    related_id INT, -- income_id veya expense_id
    related_type VARCHAR(50), -- 'income', 'expense', 'payment'
    transaction_date DATE NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_building (building_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_transaction_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan admin kullanıcı oluştur
INSERT INTO users (phone, first_name, last_name, password, role, is_active) 
VALUES ('5550000000', 'Admin', 'User', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 1);
-- Şifre: password (değiştirilmeli)


-- Banka Hesapları tablosu
CREATE TABLE IF NOT EXISTS bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT NOT NULL,
    bank_name VARCHAR(100) NOT NULL,
    branch_name VARCHAR(100),
    branch_code VARCHAR(20),
    account_number VARCHAR(50),
    iban VARCHAR(34) NOT NULL,
    account_name VARCHAR(200),
    currency VARCHAR(3) DEFAULT 'TRY',
    api_client_id VARCHAR(255),
    api_client_secret VARCHAR(255),
    api_refresh_token TEXT,
    api_access_token TEXT,
    api_token_expires DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    last_sync_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE,
    INDEX idx_building (building_id),
    INDEX idx_iban (iban),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Banka Hareketleri tablosu
CREATE TABLE IF NOT EXISTS bank_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_account_id INT NOT NULL,
    transaction_id VARCHAR(100),
    transaction_date DATETIME NOT NULL,
    value_date DATE,
    amount DECIMAL(12,2) NOT NULL,
    balance_after DECIMAL(12,2),
    transaction_type ENUM('incoming', 'outgoing') NOT NULL,
    sender_name VARCHAR(200),
    sender_iban VARCHAR(34),
    receiver_name VARCHAR(200),
    receiver_iban VARCHAR(34),
    description TEXT,
    reference_number VARCHAR(100),
    is_processed TINYINT(1) DEFAULT 0,
    matched_resident_id INT,
    matched_debt_id INT,
    matched_payment_id INT,
    process_notes TEXT,
    processed_by INT,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (matched_resident_id) REFERENCES residents(id) ON DELETE SET NULL,
    FOREIGN KEY (matched_debt_id) REFERENCES debts(id) ON DELETE SET NULL,
    FOREIGN KEY (matched_payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_bank_account (bank_account_id),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_is_processed (is_processed),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_amount (amount),
    UNIQUE KEY unique_transaction (bank_account_id, transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Banka API Log tablosu
CREATE TABLE IF NOT EXISTS bank_api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_account_id INT NOT NULL,
    request_type VARCHAR(50) NOT NULL,
    request_data TEXT,
    response_data TEXT,
    status_code INT,
    is_success TINYINT(1) DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
    INDEX idx_bank_account (bank_account_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sistem Aktivite Logları tablosu
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    user_type ENUM('user', 'resident') DEFAULT 'user',
    action VARCHAR(100) NOT NULL,
    module VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    old_data JSON,
    new_data JSON,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id, user_type),
    INDEX idx_action (action),
    INDEX idx_module (module),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;