-- Treasurer Management System Database Schema
CREATE DATABASE IF NOT EXISTS treasurer_system;
USE treasurer_system;

-- Users table (Contributors & Treasurer)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('contributor', 'treasurer') DEFAULT 'contributor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Contributions & Payments table
CREATE TABLE IF NOT EXISTS contributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    proof_image VARCHAR(255) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Expenses table
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NULL,
    date_spent DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert Default Accounts (Password is "password123")
-- Hash: $2y$10$4M9PZ0I/6hP3x/B0P2kX4e1 standard password_hash
INSERT INTO users (name, email, password, role) VALUES 
('Treasurer Admin', 'treasurer@example.com', '$2y$10$qR6J8M2u6W4H4p/wOa8cO.eF4nZ3p1s5e8t2Y5u8a9b0c1d2e3f4g', 'treasurer'),
('John Contributor', 'john@example.com', '$2y$10$qR6J8M2u6W4H4p/wOa8cO.eF4nZ3p1s5e8t2Y5u8a9b0c1d2e3f4g', 'contributor');