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

-- Users table (Admin & Partner)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','partner') NOT NULL DEFAULT 'partner',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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)
);

-- Default logins (password: admin123 / partner123)
INSERT INTO users (username, password, role) VALUES
('admin',   '$2y$10$YourHashedPasswordHere', 'admin'),
('partner', '$2y$10$YourHashedPasswordHere', 'partner');
-- NOTE: Pehli baar login.php kholo wahan se register karo ya
-- neeche PHP se hash generate karke update karo (README dekho).