-- Database Setup for Finance App - UPDATED VERSION
-- Includes: Profile Photo, Help Support, App Info

CREATE DATABASE IF NOT EXISTS finance_app;
USE finance_app;

-- =====================================================
-- TABEL TRANSACTIONS
-- =====================================================
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, date),
    INDEX idx_type (type)
);

-- =====================================================
-- TABEL BUDGETS
-- =====================================================
CREATE TABLE IF NOT EXISTS budgets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    planned_amount DECIMAL(15,2) NOT NULL,
    spent_amount DECIMAL(15,2) DEFAULT 0,
    month VARCHAR(7) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_category_month (user_id, category, month),
    INDEX idx_user_month (user_id, month)
);

-- =====================================================
-- TABEL APP INFO (BARU)
-- =====================================================
CREATE TABLE IF NOT EXISTS app_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(20) NOT NULL,
    app_name VARCHAR(100) NOT NULL,
    description TEXT,
    developer_name VARCHAR(100),
    developer_email VARCHAR(100),
    developer_website VARCHAR(255),
    privacy_policy TEXT,
    terms_of_service TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TABEL HELP SUPPORT (BARU)
-- =====================================================
CREATE TABLE IF NOT EXISTS help_support (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category ENUM('phone', 'email', 'whatsapp', 'website', 'other') NOT NULL,
    value VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- TRIGGERS FOR BUDGET AUTO-UPDATE
-- =====================================================
DELIMITER $$

DROP TRIGGER IF EXISTS update_budget_after_transaction_insert$$
    END IF;
END$$

DROP TRIGGER IF EXISTS update_budget_after_transaction_delete$$
    END IF;
END$$

DROP TRIGGER IF EXISTS update_budget_after_transaction_update$$
    END IF;
END$$

DELIMITER ;

-- App Info
INSERT INTO app_info (version, app_name, description, developer_name, developer_email, developer_website, privacy_policy, terms_of_service) VALUES 
(
    '1.0.0',
    'Finance App',
    'Aplikasi keuangan pribadi yang membantu Anda mengelola pemasukan, pengeluaran, dan budget dengan mudah dan elegan. Dilengkapi dengan fitur monitoring real-time, grafik statistik, dan deteksi pengeluaran berlebih.',
    'Your Company Name',
    'support@financeapp.com',
    'https://www.financeapp.com',
    'Kami menghargai privasi Anda. Data keuangan Anda disimpan dengan aman dan tidak akan dibagikan kepada pihak ketiga. Kami menggunakan enkripsi standar industri untuk melindungi informasi Anda.',
    'Dengan menggunakan aplikasi ini, Anda setuju untuk menggunakan layanan kami sesuai dengan ketentuan yang berlaku. Kami tidak bertanggung jawab atas kerugian finansial yang mungkin timbul dari penggunaan aplikasi ini.'
);

-- Help Support Contacts
INSERT INTO help_support (title, category, value, description, is_active, display_order) VALUES 
('Customer Service', 'phone', '081234567890', 'Hubungi kami untuk bantuan umum (Senin-Jumat, 09:00-17:00)', TRUE, 1),
('WhatsApp Support', 'whatsapp', '6281234567890', 'Chat dengan kami via WhatsApp (24/7)', TRUE, 2),
('Email Support', 'email', 'support@financeapp.com', 'Kirim email untuk pertanyaan detail', TRUE, 3),
('Technical Support', 'phone', '081234567891', 'Bantuan teknis dan troubleshooting', TRUE, 4),
('Website', 'website', 'https://www.financeapp.com/help', 'Kunjungi pusat bantuan online kami', TRUE, 5);

-- Sample Transactions for demo user
INSERT INTO transactions (user_id, type, amount, category, description, date) VALUES
(1, 'income', 5000000, 'Gaji', 'Gaji bulan Januari', '2024-01-01'),
(1, 'expense', 500000, 'Makanan', 'Groceries bulanan', '2024-01-05'),
(1, 'expense', 200000, 'Transport', 'Bensin', '2024-01-07'),
(1, 'expense', 1500000, 'Belanja', 'Shopping mall', '2024-01-10'),
(1, 'income', 5200000, 'Gaji', 'Gaji bulan Februari', '2024-02-01'),
(1, 'expense', 550000, 'Makanan', 'Groceries bulanan', '2024-02-05');

-- Sample Budgets for demo user
INSERT INTO budgets (user_id, category, planned_amount, month) VALUES
(1, 'Makanan', 1000000, '2024-01'),
(1, 'Transport', 500000, '2024-01'),
(1, 'Belanja', 1000000, '2024-01'),
(1, 'Hiburan', 500000, '2024-01'),
(1, 'Makanan', 1000000, '2024-02'),
(1, 'Transport', 500000, '2024-02');

-- =====================================================
-- NOTES
-- =====================================================
-- 1. Photo URL akan menyimpan path foto profile user
-- 2. Help Support bisa dikelola dari admin panel
-- 3. App Info bisa diupdate untuk versi baru
-- 4. Triggers otomatis update budget saat ada transaksi
