-- ============================================
-- Calculation Website - Database Schema
-- ============================================
CREATE DATABASE IF NOT EXISTS calc_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE calc_db;

-- Income entries (Admin entry karega)
CREATE TABLE IF NOT EXISTS income (
    id INT AUTO_INCREMENT PRIMARY KEY,
    income_type ENUM('B','N','D','N2') NOT NULL,   -- 4 types: B, N, D, N (adjust as needed)
    quantity INT NOT NULL DEFAULT 1,
    amount DECIMAL(12,2) NOT NULL,                  -- per unit amount
    total_amount DECIMAL(14,2) NOT NULL,            -- quantity * amount
    remark VARCHAR(255) DEFAULT NULL,
    entry_date DATE NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Expense entries
CREATE TABLE IF NOT EXISTS expense (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(12,2) NOT NULL,
    expense_type VARCHAR(100) NOT NULL,
    remark VARCHAR(255) DEFAULT NULL,
    entry_date DATE NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);
-- NOTE: Pehli baar login.php kholo wahan se register karo ya
-- neeche PHP se hash generate karke update karo (README dekho).
