<?php
// config.php - Database Configuration
session_start();

define('DB_HOST', 'localhost');
define('DB_USER', 'linearpo_mpesa_agent');
define('DB_PASS', 'qGAUSMk_KKrq');
define('DB_NAME', 'linearpo_mpesa_agent');

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database and tables if not exist
$conn->query("CREATE DATABASE IF NOT EXISTS " . DB_NAME);
$conn->select_db(DB_NAME);

// Users table
$conn->query("CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    role ENUM('admin', 'agent') DEFAULT 'agent',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");

// Float entries table
$conn->query("CREATE TABLE IF NOT EXISTS float_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    entry_type ENUM('cash_to_digital', 'digital_to_cash', 'initial') NOT NULL,
    cash_amount DECIMAL(15,2) DEFAULT 0,
    digital_amount DECIMAL(15,2) DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
)");

// Transactions table
$conn->query("CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    transaction_type ENUM('deposit', 'withdrawal') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee DECIMAL(10,2) DEFAULT 0,
    reference_number VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
)");

// Insert default admin user if not exists (password: admin123)
$check_admin = $conn->query("SELECT id FROM users WHERE username = 'admin'");
if ($check_admin->num_rows == 0) {
    $hashed_password = password_hash('admin123', PASSWORD_DEFAULT);
    $conn->query("INSERT INTO users (username, password, full_name, phone, role) 
                  VALUES ('admin', '$hashed_password', 'System Administrator', '0700000000', 'admin')");
}

// Authentication check function
function checkAuth() {
    if (!isset($_SESSION['user_id'])) {
        header("Location: index.php");
        exit();
    }
}

function checkAdmin() {
    checkAuth();
    if ($_SESSION['role'] != 'admin') {
        header("Location: dashboard.php");
        exit();
    }
}

// Helper functions
function getCurrentFloat($conn, $user_id) {
    // Calculate cash float (from float entries minus withdrawals plus deposits)
    $sql = "SELECT 
        COALESCE(SUM(CASE 
            WHEN entry_type = 'cash_to_digital' THEN -cash_amount 
            WHEN entry_type = 'digital_to_cash' THEN cash_amount 
            WHEN entry_type = 'initial' THEN cash_amount 
            ELSE 0 
        END), 0) as cash_balance,
        COALESCE(SUM(CASE 
            WHEN entry_type = 'cash_to_digital' THEN digital_amount 
            WHEN entry_type = 'digital_to_cash' THEN -digital_amount 
            WHEN entry_type = 'initial' THEN digital_amount 
            ELSE 0 
        END), 0) as digital_balance
    FROM float_entries WHERE user_id = $user_id";
    
    $result = $conn->query($sql);
    $float = $result->fetch_assoc();
    
    // Adjust for customer transactions
    $trans_sql = "SELECT 
        COALESCE(SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE 0 END), 0) as total_deposits,
        COALESCE(SUM(CASE WHEN transaction_type = 'withdrawal' THEN amount ELSE 0 END), 0) as total_withdrawals
    FROM transactions WHERE user_id = $user_id AND DATE(created_at) = CURDATE()";
    
    $trans_result = $conn->query($trans_sql);
    $trans = $trans_result->fetch_assoc();
    
    // Cash increases with withdrawals, decreases with deposits>>Actually Cash Increase with Deposit and Decreae with Withdrawals
    // Digital increases with deposits, decreases with withdrawals>>>Degital Increase with Withdrawals and Decrease with Deposit
    $float['cash_balance'] = $float['cash_balance'] - $trans['total_withdrawals'] + $trans['total_deposits'];
    $float['digital_balance'] = $float['digital_balance'] - $trans['total_deposits'] + $trans['total_withdrawals'];

    
    return $float;
}

function getTodayStats($conn, $user_id) {
    $sql = "SELECT 
        COALESCE(SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE 0 END), 0) as total_deposits,
        COALESCE(SUM(CASE WHEN transaction_type = 'withdrawal' THEN amount ELSE 0 END), 0) as total_withdrawals,
        COUNT(CASE WHEN transaction_type = 'deposit' THEN 1 END) as deposit_count,
        COUNT(CASE WHEN transaction_type = 'withdrawal' THEN 1 END) as withdrawal_count
    FROM transactions 
    WHERE user_id = $user_id AND DATE(created_at) = CURDATE()";
    
    $result = $conn->query($sql);
    return $result->fetch_assoc();
}
?>