-- ============================================================
-- JEGRI BEKALA MINING LIMITED - Enterprise Management System
-- Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS jbml_ems CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jbml_ems;

-- ============================================================
-- SYSTEM / AUTH
-- ============================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin','hr_manager','finance_manager','crm_manager','staff') DEFAULT 'staff',
    last_login DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- HR MODULE
-- ============================================================
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    head_id INT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE job_titles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    department_id INT,
    grade VARCHAR(20),
    min_salary DECIMAL(12,2),
    max_salary DECIMAL(12,2)
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_number VARCHAR(30) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    other_names VARCHAR(100),
    gender ENUM('Male','Female','Other'),
    date_of_birth DATE,
    national_id VARCHAR(50),
    passport_number VARCHAR(50),
    phone VARCHAR(30),
    email VARCHAR(150),
    address TEXT,
    emergency_contact_name VARCHAR(150),
    emergency_contact_phone VARCHAR(30),
    department_id INT,
    job_title_id INT,
    employment_type ENUM('Full-Time','Part-Time','Contract','Casual','Expatriate') DEFAULT 'Full-Time',
    employment_status ENUM('Active','On Leave','Suspended','Terminated','Retired') DEFAULT 'Active',
    hire_date DATE,
    probation_end_date DATE,
    termination_date DATE,
    termination_reason TEXT,
    basic_salary DECIMAL(12,2),
    bank_name VARCHAR(100),
    bank_account VARCHAR(50),
    bank_branch VARCHAR(100),
    photo VARCHAR(255),
    documents TEXT COMMENT 'JSON array of document paths',
    site_location VARCHAR(150),
    is_site_worker TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    check_in TIME,
    check_out TIME,
    hours_worked DECIMAL(5,2),
    status ENUM('Present','Absent','Late','Half-Day','On Leave') DEFAULT 'Present',
    remarks TEXT,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE leave_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    days_allowed INT DEFAULT 0,
    is_paid TINYINT(1) DEFAULT 1,
    carry_forward TINYINT(1) DEFAULT 0
);

CREATE TABLE leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested INT,
    reason TEXT,
    status ENUM('Pending','Approved','Rejected','Cancelled') DEFAULT 'Pending',
    approved_by INT,
    approved_at DATETIME,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employee_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    doc_type VARCHAR(100),
    doc_name VARCHAR(255),
    file_path VARCHAR(255),
    expiry_date DATE,
    uploaded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE performance_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    reviewer_id INT NOT NULL,
    review_period VARCHAR(50),
    review_date DATE,
    kpi_score DECIMAL(5,2),
    productivity_score DECIMAL(5,2),
    teamwork_score DECIMAL(5,2),
    safety_compliance_score DECIMAL(5,2),
    overall_score DECIMAL(5,2),
    comments TEXT,
    recommendations TEXT,
    status ENUM('Draft','Submitted','Acknowledged') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE training_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    trainer VARCHAR(150),
    start_date DATE,
    end_date DATE,
    location VARCHAR(200),
    cost DECIMAL(12,2),
    max_participants INT,
    status ENUM('Planned','Ongoing','Completed','Cancelled') DEFAULT 'Planned',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE training_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    training_id INT NOT NULL,
    employee_id INT NOT NULL,
    status ENUM('Enrolled','Completed','Failed','Withdrew') DEFAULT 'Enrolled',
    certificate_issued TINYINT(1) DEFAULT 0
);

-- ============================================================
-- PAYROLL MODULE
-- ============================================================
CREATE TABLE payroll_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('Earning','Deduction') NOT NULL,
    calculation_type ENUM('Fixed','Percentage','Formula') DEFAULT 'Fixed',
    default_value DECIMAL(12,2) DEFAULT 0,
    is_taxable TINYINT(1) DEFAULT 1,
    is_mandatory TINYINT(1) DEFAULT 0,
    description TEXT
);

CREATE TABLE payroll_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_month INT NOT NULL,
    period_year INT NOT NULL,
    run_date DATE,
    total_gross DECIMAL(15,2),
    total_deductions DECIMAL(15,2),
    total_net DECIMAL(15,2),
    status ENUM('Draft','Processing','Approved','Paid') DEFAULT 'Draft',
    approved_by INT,
    approved_at DATETIME,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE payslips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT NOT NULL,
    employee_id INT NOT NULL,
    period_month INT,
    period_year INT,
    basic_salary DECIMAL(12,2),
    gross_salary DECIMAL(12,2),
    total_deductions DECIMAL(12,2),
    net_salary DECIMAL(12,2),
    earnings_breakdown TEXT COMMENT 'JSON',
    deductions_breakdown TEXT COMMENT 'JSON',
    payment_status ENUM('Pending','Paid') DEFAULT 'Pending',
    payment_date DATE,
    payment_method ENUM('Bank Transfer','Cash','Cheque') DEFAULT 'Bank Transfer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employee_loans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    loan_type VARCHAR(100),
    amount DECIMAL(12,2),
    interest_rate DECIMAL(5,2) DEFAULT 0,
    repayment_months INT,
    monthly_deduction DECIMAL(12,2),
    disbursement_date DATE,
    status ENUM('Active','Completed','Defaulted') DEFAULT 'Active',
    approved_by INT,
    balance DECIMAL(12,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- CRM MODULE
-- ============================================================
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    type ENUM('Customer','Supplier','Partner','Investor','Regulator','Other') DEFAULT 'Customer',
    industry VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    address TEXT,
    phone VARCHAR(50),
    email VARCHAR(150),
    website VARCHAR(200),
    tax_id VARCHAR(100),
    assigned_to INT,
    status ENUM('Active','Inactive','Prospect','Blacklisted') DEFAULT 'Active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100),
    job_title VARCHAR(150),
    phone VARCHAR(50),
    email VARCHAR(150),
    is_primary TINYINT(1) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE deals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    company_id INT,
    contact_id INT,
    assigned_to INT,
    deal_type ENUM('Mining Contract','Ore Supply','Equipment Sale','Service','Investment','Other') DEFAULT 'Mining Contract',
    value DECIMAL(15,2),
    currency VARCHAR(10) DEFAULT 'GHS',
    probability INT DEFAULT 50,
    stage ENUM('Prospecting','Qualification','Proposal','Negotiation','Won','Lost','On Hold') DEFAULT 'Prospecting',
    expected_close_date DATE,
    actual_close_date DATE,
    description TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('Call','Meeting','Email','Site Visit','Demo','Follow-Up','Task') NOT NULL,
    subject VARCHAR(200),
    related_to ENUM('Deal','Company','Contact','Employee') DEFAULT 'Deal',
    related_id INT,
    assigned_to INT,
    due_date DATETIME,
    completed_at DATETIME,
    outcome TEXT,
    status ENUM('Planned','Completed','Cancelled') DEFAULT 'Planned',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_number VARCHAR(50) UNIQUE,
    title VARCHAR(200),
    company_id INT,
    deal_id INT,
    contract_type VARCHAR(100),
    value DECIMAL(15,2),
    currency VARCHAR(10) DEFAULT 'GHS',
    start_date DATE,
    end_date DATE,
    status ENUM('Draft','Active','Expired','Terminated','Renewed') DEFAULT 'Draft',
    document_path VARCHAR(255),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- EXPENSES MODULE
-- ============================================================
CREATE TABLE expense_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type ENUM('Operational','Capital','Administrative','HR','Mining','Transport','Other') DEFAULT 'Operational',
    budget DECIMAL(15,2),
    description TEXT
);

CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_number VARCHAR(50),
    title VARCHAR(200) NOT NULL,
    category_id INT,
    amount DECIMAL(12,2),
    currency VARCHAR(10) DEFAULT 'GHS',
    expense_date DATE,
    vendor VARCHAR(200),
    invoice_number VARCHAR(100),
    payment_method ENUM('Cash','Bank Transfer','Cheque','Credit Card','Mobile Money') DEFAULT 'Cash',
    payment_status ENUM('Pending','Paid','Rejected') DEFAULT 'Pending',
    paid_by INT,
    approved_by INT,
    approved_at DATETIME,
    description TEXT,
    receipt_path VARCHAR(255),
    department_id INT,
    project VARCHAR(200),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- INCOME MODULE
-- ============================================================
CREATE TABLE income_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type ENUM('Mining Revenue','Contract','Investment','Grant','Royalty','Other') DEFAULT 'Mining Revenue'
);

CREATE TABLE income_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_number VARCHAR(50),
    title VARCHAR(200) NOT NULL,
    category_id INT,
    amount DECIMAL(15,2),
    currency VARCHAR(10) DEFAULT 'GHS',
    income_date DATE,
    source VARCHAR(200),
    invoice_number VARCHAR(100),
    payment_method ENUM('Cash','Bank Transfer','Cheque','Mobile Money','Letter of Credit') DEFAULT 'Bank Transfer',
    payment_status ENUM('Expected','Received','Partial','Overdue') DEFAULT 'Expected',
    company_id INT,
    description TEXT,
    receipt_path VARCHAR(255),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE,
    company_id INT,
    issue_date DATE,
    due_date DATE,
    subtotal DECIMAL(15,2),
    tax_amount DECIMAL(12,2),
    total_amount DECIMAL(15,2),
    amount_paid DECIMAL(15,2) DEFAULT 0,
    status ENUM('Draft','Sent','Partial','Paid','Overdue','Cancelled') DEFAULT 'Draft',
    currency VARCHAR(10) DEFAULT 'GHS',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    description VARCHAR(255),
    quantity DECIMAL(10,3),
    unit_price DECIMAL(12,2),
    total DECIMAL(15,2)
);

-- ============================================================
-- VISITORS MODULE
-- ============================================================
CREATE TABLE visitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_number VARCHAR(30) UNIQUE,
    visitor_name VARCHAR(200) NOT NULL,
    visitor_company VARCHAR(200),
    visitor_phone VARCHAR(50),
    visitor_email VARCHAR(150),
    visitor_id_type VARCHAR(50),
    visitor_id_number VARCHAR(100),
    photo VARCHAR(255),
    purpose ENUM('Business Meeting','Site Inspection','Delivery','Interview','Government/Regulatory','Maintenance','Other') DEFAULT 'Business Meeting',
    host_employee_id INT,
    department_id INT,
    badge_number VARCHAR(30),
    vehicle_reg VARCHAR(30),
    check_in DATETIME,
    check_out DATETIME,
    security_clearance ENUM('Standard','Restricted Area','Full Site Access') DEFAULT 'Standard',
    status ENUM('Expected','Checked In','Checked Out','No Show','Denied') DEFAULT 'Expected',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE visitor_blacklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_name VARCHAR(200),
    id_number VARCHAR(100),
    reason TEXT,
    added_by INT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- ASSETS MODULE
-- ============================================================
CREATE TABLE asset_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    depreciation_method ENUM('Straight Line','Declining Balance','Units of Production') DEFAULT 'Straight Line',
    useful_life_years INT,
    salvage_value_percent DECIMAL(5,2)
);

CREATE TABLE assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_tag VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    category_id INT,
    description TEXT,
    serial_number VARCHAR(100),
    model VARCHAR(150),
    manufacturer VARCHAR(150),
    purchase_date DATE,
    purchase_cost DECIMAL(15,2),
    current_value DECIMAL(15,2),
    location VARCHAR(200),
    department_id INT,
    assigned_to INT,
    condition_status ENUM('Excellent','Good','Fair','Poor','Under Repair','Decommissioned') DEFAULT 'Good',
    operational_status ENUM('Active','Idle','Under Maintenance','Disposed') DEFAULT 'Active',
    warranty_expiry DATE,
    insurance_policy VARCHAR(100),
    insurance_expiry DATE,
    last_maintenance DATE,
    next_maintenance DATE,
    notes TEXT,
    photo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE asset_maintenance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_id INT NOT NULL,
    maintenance_type ENUM('Preventive','Corrective','Emergency','Inspection') NOT NULL,
    scheduled_date DATE,
    completed_date DATE,
    performed_by VARCHAR(200),
    cost DECIMAL(12,2),
    description TEXT,
    next_maintenance_date DATE,
    status ENUM('Scheduled','In Progress','Completed','Overdue') DEFAULT 'Scheduled',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE asset_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_id INT NOT NULL,
    from_department INT,
    to_department INT,
    from_employee INT,
    to_employee INT,
    transfer_date DATE,
    reason TEXT,
    approved_by INT,
    status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    type VARCHAR(50),
    title VARCHAR(200),
    message TEXT,
    link VARCHAR(255),
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100),
    module VARCHAR(100),
    record_id INT,
    old_values TEXT,
    new_values TEXT,
    ip_address VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- SETTINGS
-- ============================================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_group VARCHAR(50) DEFAULT 'general',
    label VARCHAR(150),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- SEED DATA
-- ============================================================
INSERT INTO settings (setting_key, setting_value, setting_group, label) VALUES
('company_name', 'Jegri Bekala Mining Limited', 'general', 'Company Name'),
('company_short', 'JBML', 'general', 'Short Name'),
('company_email', 'info@jegribekala.com', 'general', 'Company Email'),
('company_phone', '+233 000 000 000', 'general', 'Phone'),
('company_address', 'Kumasi, Ashanti Region, Ghana', 'general', 'Address'),
('company_country', 'Ghana', 'general', 'Country'),
('currency', 'GHS', 'finance', 'Currency'),
('currency_symbol', '₵', 'finance', 'Currency Symbol'),
('fiscal_year_start', '01', 'finance', 'Fiscal Year Start Month'),
('tax_rate', '15', 'finance', 'Default Tax Rate (%)'),
('ssnit_rate', '5.5', 'payroll', 'SSNIT Employee Rate (%)'),
('ssnit_employer_rate', '13', 'payroll', 'SSNIT Employer Rate (%)');

INSERT INTO departments (name, description) VALUES
('Executive','Board and C-Suite'),
('Mining Operations','Core mining activities and site management'),
('Geology & Exploration','Geological surveys and mineral exploration'),
('Engineering & Maintenance','Equipment and infrastructure maintenance'),
('Health, Safety & Environment','HSE compliance and management'),
('Human Resources','HR management and administration'),
('Finance & Accounts','Financial management and accounting'),
('Procurement & Logistics','Supply chain and procurement'),
('Information Technology','IT infrastructure and support'),
('Security','Site and office security management'),
('Community Relations','Community engagement and CSR');

INSERT INTO leave_types (name, days_allowed, is_paid, carry_forward) VALUES
('Annual Leave', 21, 1, 1),
('Sick Leave', 14, 1, 0),
('Maternity Leave', 84, 1, 0),
('Paternity Leave', 5, 1, 0),
('Emergency Leave', 5, 1, 0),
('Study Leave', 10, 0, 0),
('Unpaid Leave', 30, 0, 0);

INSERT INTO payroll_components (name, type, calculation_type, default_value, is_taxable, is_mandatory) VALUES
('Basic Salary', 'Earning', 'Fixed', 0, 1, 1),
('Transport Allowance', 'Earning', 'Fixed', 200, 0, 0),
('Housing Allowance', 'Earning', 'Percentage', 20, 1, 0),
('Risk/Danger Allowance', 'Earning', 'Fixed', 500, 0, 0),
('Site Allowance', 'Earning', 'Fixed', 300, 0, 0),
('Overtime Pay', 'Earning', 'Formula', 0, 1, 0),
('SSNIT (Employee 5.5%)', 'Deduction', 'Percentage', 5.5, 0, 1),
('Income Tax (PAYE)', 'Deduction', 'Formula', 0, 0, 1),
('Loan Deduction', 'Deduction', 'Fixed', 0, 0, 0),
('Advance Deduction', 'Deduction', 'Fixed', 0, 0, 0);

INSERT INTO expense_categories (name, type, description) VALUES
('Explosives & Blasting', 'Mining', 'Mining explosives and blasting materials'),
('Fuel & Lubricants', 'Operational', 'Diesel, petrol, engine oils'),
('Equipment Spare Parts', 'Operational', 'Replacement parts for machinery'),
('Safety Equipment (PPE)', 'Mining', 'Personal protective equipment'),
('Office Supplies', 'Administrative', 'Stationery and office materials'),
('Utilities', 'Operational', 'Electricity, water, communications'),
('Transport & Logistics', 'Transport', 'Vehicle maintenance, freight'),
('Professional Services', 'Administrative', 'Legal, audit, consulting'),
('Staff Welfare', 'HR', 'Staff meals, medical, welfare'),
('Marketing & Relations', 'Administrative', 'PR and community relations');

INSERT INTO income_categories (name, type) VALUES
('Gold Sales', 'Mining Revenue'),
('Other Mineral Sales', 'Mining Revenue'),
('Mining Contracts', 'Contract'),
('Equipment Hire', 'Contract'),
('Royalties', 'Royalty'),
('Investment Returns', 'Investment'),
('Government Grants', 'Grant');

INSERT INTO asset_categories (name, depreciation_method, useful_life_years, salvage_value_percent) VALUES
('Heavy Mining Equipment', 'Declining Balance', 10, 10),
('Vehicles & Transport', 'Straight Line', 5, 10),
('Office Equipment', 'Straight Line', 5, 5),
('IT & Communications', 'Straight Line', 3, 5),
('Buildings & Structures', 'Straight Line', 25, 20),
('Safety & PPE Equipment', 'Straight Line', 2, 0),
('Laboratory Equipment', 'Straight Line', 7, 5),
('Furniture & Fittings', 'Straight Line', 10, 5);

-- Admin user (password: Admin@123)
INSERT INTO users (username, password, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Indexes for performance
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_status ON employees(employment_status);
CREATE INDEX idx_attendance_emp_date ON attendance(employee_id, date);
CREATE INDEX idx_expenses_date ON expenses(expense_date);
CREATE INDEX idx_income_date ON income_records(income_date);
CREATE INDEX idx_visitors_date ON visitors(check_in);
CREATE INDEX idx_assets_status ON assets(operational_status);