

-- جدول حساب‌ها (accounts)
CREATE TABLE IF NOT EXISTS accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL,
    type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    level INT NOT NULL CHECK (level IN (1, 2, 3)),
    parent_id INT,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES accounts(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_accounts_user_id (user_id),
    INDEX idx_accounts_type (type),
    INDEX idx_accounts_level (level),
    INDEX idx_accounts_parent_id (parent_id)
);

-- جدول وام‌ها (loans)
CREATE TABLE IF NOT EXISTS loans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL,
    installments_count INT NOT NULL,
    start_date DATE NOT NULL,
    type ENUM('loan', 'asset', 'debt') NOT NULL,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    user_id INT NOT NULL,
    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_loans_user_id (user_id),
    INDEX idx_loans_status (status),
    INDEX idx_loans_type (type)
);

-- جدول اقساط وام (loan_installments)
CREATE TABLE IF NOT EXISTS loan_installments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    loan_id INT NOT NULL,
    installment_number INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    interest_amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    paid_date DATE,
    status ENUM('pending', 'paid', 'overdue') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE,
    INDEX idx_loan_installments_loan_id (loan_id),
    INDEX idx_loan_installments_due_date (due_date),
    INDEX idx_loan_installments_status (status)
);

-- جدول اعضای خانواده (family_members)
CREATE TABLE IF NOT EXISTS family_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    relationship VARCHAR(50),
    user_id INT NOT NULL,
    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_family_members_user_id (user_id)
);

-- جدول تراکنش‌ها (transactions)
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    type ENUM('income', 'expense', 'transfer') NOT NULL,
    transaction_date DATE NOT NULL,
    description TEXT,
    account_id INT,
    bank_id INT,
    family_member_id INT,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
    FOREIGN KEY (bank_id) REFERENCES banks(id) ON DELETE SET NULL,
    FOREIGN KEY (family_member_id) REFERENCES family_members(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_transactions_user_id (user_id),
    INDEX idx_transactions_date (transaction_date DESC),
    INDEX idx_transactions_type (type),
    INDEX idx_transactions_account_id (account_id)
);

-- جدول بودجه‌ها (budgets)
CREATE TABLE IF NOT EXISTS budgets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    spent DECIMAL(15,2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    account_id INT,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_budgets_user_id (user_id),
    INDEX idx_budgets_account_id (account_id),
    INDEX idx_budgets_dates (start_date, end_date)
);

-- جدول چک‌ها (checks)
CREATE TABLE IF NOT EXISTS checks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    check_number VARCHAR(50) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('in_safe', 'in_collection', 'spent', 'collected', 'bounced') DEFAULT 'in_safe',
    description TEXT,
    bank_id INT,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_id) REFERENCES banks(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_checks_user_id (user_id),
    INDEX idx_checks_due_date (due_date),
    INDEX idx_checks_status (status),
    INDEX idx_checks_bank_id (bank_id)
);

-- جدول گفتگوهای هوش مصنوعی (ai_conversations)
CREATE TABLE IF NOT EXISTS ai_conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    response TEXT NOT NULL,
    context JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_ai_conversations_user_id (user_id),
    INDEX idx_ai_conversations_created_at (created_at DESC)
);

-- جدول تنظیمات سیستم (system_settings)
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value JSON NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- جدول فایل‌های خروجی (export_files)
CREATE TABLE IF NOT EXISTS export_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_type ENUM('pdf', 'excel', 'csv') NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_export_files_user_id (user_id),
    INDEX idx_export_files_created_at (created_at DESC)
);

-- حساب‌های پیش‌فرض
INSERT IGNORE INTO accounts (name, code, type, level, user_id) VALUES 
('دارایی‌ها', '1', 'asset', 1, 1),
('بدهی‌ها', '2', 'liability', 1, 1),
('حقوق صاحبان سهام', '3', 'equity', 1, 1),
('درآمدها', '4', 'revenue', 1, 1),
('هزینه‌ها', '5', 'expense', 1, 1),
('نقد و بانک', '11', 'asset', 2, 1),
('حساب‌های دریافتنی', '12', 'asset', 2, 1),
('موجودی کالا', '13', 'asset', 2, 1),
('حساب‌های پرداختنی', '21', 'liability', 2, 1),
('وام‌های کوتاه مدت', '22', 'liability', 2, 1);

-- تنظیمات سیستم پیش‌فرض
INSERT IGNORE INTO system_settings (setting_key, setting_value, description) VALUES 
('app_name', '"سیستم حسابداری هوکو"', 'نام برنامه'),
('default_currency', '"ریال"', 'واحد پول پیش‌فرض'),
('default_calendar', '"persian"', 'تقویم پیش‌فرض'),
('rtl_support', 'true', 'پشتیبانی از راست به چپ'),
('ai_enabled', 'true', 'فعال‌سازی دستیار هوش مصنوعی');