-- ============================================================================ -- NBO tenant schema — Migration 001: initial (consolidated from v2 database.sql) -- ============================================================================ -- Applied against a newly-provisioned nbo_t_ database (e.g. nbo_t_acme). -- Run via: php bin/migrate.php tenant --slug= -- -- This is v2's full schema (all v2.1–v2.8 ALTERs rolled into the CREATE TABLE -- statements so every tenant starts at the latest shape). Two deltas vs v2: -- -- 1. `users` gets a `global_user_id` column. In NBO, a tenant user row is a -- pointer to a `nbo_master.global_users` row; the password lives there, -- not here. `password` stays nullable for backward compatibility with -- imported v2 data (Netpoa tenant's mysqldump will populate it), but new -- v3 code never reads it. -- -- 2. Netpoa-specific seed data (Jacob's employees, Netpoa service providers, -- Netpoa's company settings) is NOT here. A fresh tenant starts empty; -- the tenant admin fills company settings via the first-run wizard. -- Generic defaults only (rates, role catalogue, category dropdowns). -- -- The WHMCS tables stay in every tenant's schema so the shared v2 API code -- continues to work against all tenants uniformly — only Netpoa will ever have -- `tenants.whmcs_enabled=1` and actually populate them. -- ============================================================================ -- -- USERS (local to this tenant; authoritative password lives in nbo_master.global_users) CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, global_user_id INT UNSIGNED DEFAULT NULL, -- link to nbo_master.global_users.id name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL, password VARCHAR(255) DEFAULT NULL, -- legacy; NULL for v3-native users role VARCHAR(50) NOT NULL DEFAULT 'accountant', -- matches custom_roles.role_key employee_id INT DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, last_login DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_users_email (email), UNIQUE KEY uniq_users_global (global_user_id), INDEX idx_users_role (role) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- CUSTOM ROLES (dynamic RBAC editable from the admin UI) CREATE TABLE IF NOT EXISTS custom_roles ( id INT AUTO_INCREMENT PRIMARY KEY, role_key VARCHAR(50) UNIQUE NOT NULL, role_label VARCHAR(100) NOT NULL, permissions TEXT DEFAULT NULL, -- JSON array of permission keys is_system TINYINT(1) DEFAULT 0, -- 1 = cannot be deleted created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- USER ROLES (many-to-many; one user may hold multiple roles) CREATE TABLE IF NOT EXISTS user_roles ( user_id INT NOT NULL, role_key VARCHAR(50) NOT NULL, is_primary TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_key), INDEX idx_user_roles_user (user_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- CLIENTS (used by invoicing autosave + browse) CREATE TABLE IF NOT EXISTS clients ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, email VARCHAR(150) DEFAULT NULL, phone VARCHAR(60) DEFAULT NULL, address TEXT DEFAULT NULL, tin_number VARCHAR(50) DEFAULT NULL, vrn_number VARCHAR(50) DEFAULT NULL, notes TEXT DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_client_name (name), INDEX idx_client_name (name), INDEX idx_client_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- INVOICES (table historically named whmcs_invoices; used for ALL invoices, WHMCS or not) CREATE TABLE IF NOT EXISTS whmcs_invoices ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_number VARCHAR(50) NOT NULL, client_name VARCHAR(150) NOT NULL, client_email VARCHAR(150) DEFAULT NULL, client_id INT DEFAULT NULL, service_name VARCHAR(200) DEFAULT NULL, product_id INT DEFAULT NULL, quantity INT NOT NULL DEFAULT 1, subtotal DECIMAL(14,2) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(14,2) NOT NULL DEFAULT 0, vat_mode VARCHAR(10) NOT NULL DEFAULT 'exclusive', category VARCHAR(200) DEFAULT 'Other', amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, -- grand total (subtotal + vat) currency VARCHAR(10) DEFAULT 'TZS', payment_method VARCHAR(80) DEFAULT NULL, status ENUM('Paid','Pending','Overdue','Cancelled') DEFAULT 'Pending', invoice_date DATE NOT NULL, due_date DATE DEFAULT NULL, paid_date DATE DEFAULT NULL, whmcs_id VARCHAR(50) DEFAULT NULL, notes TEXT DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_inv_status (status), INDEX idx_inv_date (invoice_date), INDEX idx_inv_product (product_id), INDEX idx_inv_client_id (client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- INVOICE LINE ITEMS CREATE TABLE IF NOT EXISTS invoice_items ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, item_type ENUM('service','product') NOT NULL DEFAULT 'service', product_id INT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(12,2) NOT NULL DEFAULT 1, unit_price DECIMAL(14,2) NOT NULL DEFAULT 0, line_total DECIMAL(14,2) NOT NULL DEFAULT 0, position INT NOT NULL DEFAULT 0, INDEX idx_inv_items_invoice (invoice_id), INDEX idx_inv_items_product (product_id), FOREIGN KEY (invoice_id) REFERENCES whmcs_invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EXPENSES (COGS + Operating + Payroll) CREATE TABLE IF NOT EXISTS expenses ( id INT AUTO_INCREMENT PRIMARY KEY, vendor VARCHAR(150) NOT NULL, category VARCHAR(200) DEFAULT 'Other', expense_type ENUM('COGS','Operating','Payroll') DEFAULT 'Operating', amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, currency VARCHAR(10) DEFAULT 'TZS', exchange_rate DECIMAL(10,4) DEFAULT 1.0000, amount_tzs DECIMAL(14,2) DEFAULT 0.00, payment_method ENUM('Bank Transfer','Credit Card','PayPal','Mobile Money - MPESA','Mobile Money - Tigopesa','Cash','Other') DEFAULT 'Bank Transfer', expense_date DATE NOT NULL, description TEXT DEFAULT NULL, receipt_number VARCHAR(100) DEFAULT NULL, transaction_ref VARCHAR(200) DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_exp_date (expense_date), INDEX idx_exp_cat (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- OTHER INCOME CREATE TABLE IF NOT EXISTS other_income ( id INT AUTO_INCREMENT PRIMARY KEY, source VARCHAR(150) NOT NULL, category ENUM('Consulting','Affiliate','Refund','Grant','Investment','Other') DEFAULT 'Other', amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, currency VARCHAR(10) DEFAULT 'TZS', income_date DATE NOT NULL, notes TEXT DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EMPLOYEES CREATE TABLE IF NOT EXISTS employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(150) DEFAULT NULL, phone VARCHAR(30) DEFAULT NULL, department VARCHAR(100) DEFAULT NULL, position VARCHAR(100) DEFAULT NULL, employment_type VARCHAR(50) DEFAULT 'Full-time', basic_salary DECIMAL(14,2) DEFAULT 0.00, currency VARCHAR(10) DEFAULT 'TZS', bank_name VARCHAR(100) DEFAULT NULL, bank_account VARCHAR(50) DEFAULT NULL, mobile_money VARCHAR(50) DEFAULT NULL, tin_number VARCHAR(50) DEFAULT NULL, nssf_number VARCHAR(50) DEFAULT NULL, wcf_number VARCHAR(50) DEFAULT NULL, national_id VARCHAR(50) DEFAULT NULL, physical_address VARCHAR(255) DEFAULT NULL, profile_photo VARCHAR(255) DEFAULT NULL, kin_name VARCHAR(150) DEFAULT NULL, kin_phone VARCHAR(40) DEFAULT NULL, kin_relationship VARCHAR(60) DEFAULT NULL, contract_file VARCHAR(255) DEFAULT NULL, job_description TEXT DEFAULT NULL, contract_start DATE DEFAULT NULL, contract_end DATE DEFAULT NULL, contract_type VARCHAR(50) DEFAULT NULL, contract_notes TEXT DEFAULT NULL, exempt_paye TINYINT(1) DEFAULT 0, exempt_nssf TINYINT(1) DEFAULT 0, exempt_wcf TINYINT(1) DEFAULT 0, start_date DATE DEFAULT NULL, status ENUM('Active','On Leave','Terminated') DEFAULT 'Active', notes TEXT DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_emp_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- PAYROLL CREATE TABLE IF NOT EXISTS payroll ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, payroll_month VARCHAR(7) NOT NULL, basic_salary DECIMAL(14,2) DEFAULT 0.00, allowances DECIMAL(14,2) DEFAULT 0.00, overtime DECIMAL(14,2) DEFAULT 0.00, deductions DECIMAL(14,2) DEFAULT 0.00, loan_deduction DECIMAL(14,2) DEFAULT 0.00, nssf_employee DECIMAL(14,2) DEFAULT 0.00, nssf_employer DECIMAL(14,2) DEFAULT 0.00, wcf DECIMAL(14,2) DEFAULT 0.00, paye DECIMAL(14,2) DEFAULT 0.00, gross_salary DECIMAL(14,2) DEFAULT 0.00, net_salary DECIMAL(14,2) DEFAULT 0.00, status ENUM('Pending','Paid','Cancelled') DEFAULT 'Pending', payment_date DATE DEFAULT NULL, payment_method VARCHAR(50) DEFAULT 'Bank Transfer', notes TEXT DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_payroll (employee_id, payroll_month), INDEX idx_payroll_month (payroll_month), FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- PETTY CASH (actual disbursements) CREATE TABLE IF NOT EXISTS petty_cash ( id INT AUTO_INCREMENT PRIMARY KEY, category VARCHAR(100) DEFAULT 'Other', description VARCHAR(200) DEFAULT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, cash_date DATE NOT NULL, approved_by VARCHAR(100) DEFAULT NULL, received_by VARCHAR(100) DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- PETTY CASH REQUESTS (approval workflow) CREATE TABLE IF NOT EXISTS petty_cash_requests ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, category VARCHAR(100) DEFAULT 'Other', description VARCHAR(200) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, reason TEXT DEFAULT NULL, status ENUM('Pending','Approved','Rejected','Paid') DEFAULT 'Pending', requested_by INT DEFAULT NULL, approved_by INT DEFAULT NULL, approved_at DATETIME DEFAULT NULL, rejection_reason VARCHAR(200) DEFAULT NULL, paid_by INT DEFAULT NULL, paid_at DATETIME DEFAULT NULL, petty_cash_id INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- SERVICE PROVIDERS (recurring vendor bills) CREATE TABLE IF NOT EXISTS service_providers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, service_description TEXT DEFAULT NULL, website VARCHAR(255) DEFAULT NULL, payment_day VARCHAR(50) DEFAULT NULL, monthly_cost DECIMAL(14,2) DEFAULT 0.00, annual_cost DECIMAL(14,2) DEFAULT 0.00, currency VARCHAR(10) DEFAULT 'TZS', category VARCHAR(100) DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, notes TEXT DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- AUDIT LOG (tenant-scoped; master-level actions go in nbo_master.master_audit_log) CREATE TABLE IF NOT EXISTS audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT DEFAULT NULL, action VARCHAR(100) NOT NULL, table_name VARCHAR(50) DEFAULT NULL, record_id INT DEFAULT NULL, ip_address VARCHAR(45) DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_user (user_id), INDEX idx_audit_action (action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- SETTINGS (key/value; per-tenant) CREATE TABLE IF NOT EXISTS settings ( id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(100) UNIQUE NOT NULL, setting_value TEXT DEFAULT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- WHMCS SYNC LOG (only Netpoa tenant uses this; harmless empty table elsewhere) CREATE TABLE IF NOT EXISTS whmcs_sync_log ( id INT AUTO_INCREMENT PRIMARY KEY, sync_type VARCHAR(50) DEFAULT 'direct_db', records_synced INT DEFAULT 0, status ENUM('success','error') DEFAULT 'success', message TEXT DEFAULT NULL, synced_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EMPLOYEE LEAVES CREATE TABLE IF NOT EXISTS employee_leaves ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, leave_type ENUM('Annual','Sick','Maternity','Paternity','Emergency','Unpaid','Other') DEFAULT 'Annual', start_date DATE NOT NULL, end_date DATE NOT NULL, days_requested INT DEFAULT 1, reason TEXT DEFAULT NULL, status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending', reviewed_by INT DEFAULT NULL, reviewed_at DATETIME DEFAULT NULL, manager_notes TEXT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- ATTENDANCE CREATE TABLE IF NOT EXISTS attendance ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, attendance_date DATE NOT NULL, check_in TIME DEFAULT NULL, check_out TIME DEFAULT NULL, status ENUM('Present','Absent','Late','Half-day','Leave','Holiday') DEFAULT 'Present', notes VARCHAR(200) DEFAULT NULL, recorded_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_att (employee_id, attendance_date), INDEX idx_att_date (attendance_date), INDEX idx_att_emp (employee_id), FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- DAILY REPORTS CREATE TABLE IF NOT EXISTS daily_reports ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, report_date DATE NOT NULL, department VARCHAR(100) DEFAULT NULL, tasks_completed TEXT DEFAULT NULL, tasks_in_progress TEXT DEFAULT NULL, challenges TEXT DEFAULT NULL, plan_tomorrow TEXT DEFAULT NULL, time_spent VARCHAR(50) DEFAULT NULL, status ENUM('Draft','Submitted','Approved','Rejected') DEFAULT 'Draft', manager_comment TEXT DEFAULT NULL, reviewed_by INT DEFAULT NULL, reviewed_at DATETIME DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_daily_report (employee_id, report_date), INDEX idx_report_date (report_date), INDEX idx_report_emp (employee_id), INDEX idx_report_status (status), FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EMPLOYEE LOANS & SALARY ADVANCES CREATE TABLE IF NOT EXISTS employee_loans ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, loan_type ENUM('Loan','Salary Advance') DEFAULT 'Loan', amount_requested DECIMAL(14,2) NOT NULL DEFAULT 0.00, amount_approved DECIMAL(14,2) DEFAULT 0.00, repayment_months INT DEFAULT 1, monthly_deduction DECIMAL(14,2) DEFAULT 0.00, amount_paid DECIMAL(14,2) DEFAULT 0.00, balance DECIMAL(14,2) DEFAULT 0.00, purpose VARCHAR(255) DEFAULT NULL, notes TEXT DEFAULT NULL, hr_notes TEXT DEFAULT NULL, rejection_reason VARCHAR(255) DEFAULT NULL, status ENUM('Pending','Active','Completed','Rejected','Cancelled') DEFAULT 'Pending', approved_by INT DEFAULT NULL, approved_at DATETIME DEFAULT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_loan_emp (employee_id), INDEX idx_loan_status (status), FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- LOAN DEDUCTION LEDGER (one row per payroll deduction against a loan) CREATE TABLE IF NOT EXISTS loan_deductions ( id INT AUTO_INCREMENT PRIMARY KEY, loan_id INT NOT NULL, payroll_id INT DEFAULT NULL, employee_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, deduction_month VARCHAR(7) DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (loan_id) REFERENCES employee_loans(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- PRODUCTS (inventory catalogue — tenants without inventory simply leave empty) CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(64) UNIQUE, name VARCHAR(200) NOT NULL, category VARCHAR(100) DEFAULT NULL, description TEXT DEFAULT NULL, unit VARCHAR(30) DEFAULT 'pcs', cost_price DECIMAL(14,2) DEFAULT 0.00, sell_price DECIMAL(14,2) DEFAULT 0.00, currency VARCHAR(10) DEFAULT 'TZS', reorder_level INT DEFAULT 0, stock_qty INT DEFAULT 0, is_active TINYINT(1) DEFAULT 1, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- INVENTORY MOVEMENTS (in/out/adjust) CREATE TABLE IF NOT EXISTS inventory_movements ( id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, movement_type ENUM('IN','OUT','ADJUST') DEFAULT 'IN', quantity INT NOT NULL DEFAULT 0, unit_cost DECIMAL(14,2) DEFAULT 0.00, reference VARCHAR(100) DEFAULT NULL, notes VARCHAR(255) DEFAULT NULL, movement_date DATE NOT NULL, created_by INT DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_inv_prod (product_id), INDEX idx_inv_move_date (movement_date), FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EMPLOYEE TASKS (goals/KPIs) CREATE TABLE IF NOT EXISTS employee_tasks ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, description TEXT DEFAULT NULL, task_type ENUM('checkbox','numeric') NOT NULL DEFAULT 'checkbox', target_value DECIMAL(14,2) DEFAULT NULL, unit VARCHAR(30) DEFAULT NULL, frequency ENUM('Daily','Weekly','Monthly','One-off') NOT NULL DEFAULT 'Daily', department VARCHAR(100) DEFAULT NULL, assigned_to INT DEFAULT NULL, priority ENUM('Low','Medium','High') NOT NULL DEFAULT 'Medium', weight TINYINT UNSIGNED NOT NULL DEFAULT 1, start_date DATE NOT NULL, due_date DATE DEFAULT NULL, status ENUM('Active','Paused','Archived') NOT NULL DEFAULT 'Active', created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_task_dept (department), INDEX idx_task_assignee (assigned_to), FOREIGN KEY (assigned_to) REFERENCES employees(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- EMPLOYEE TASK LOG (per-period completion) CREATE TABLE IF NOT EXISTS employee_task_log ( id INT AUTO_INCREMENT PRIMARY KEY, task_id INT NOT NULL, employee_id INT NOT NULL, period_date DATE NOT NULL, status ENUM('Done','Missed','In Progress') NOT NULL DEFAULT 'Done', actual_value DECIMAL(14,2) DEFAULT NULL, score DECIMAL(5,2) DEFAULT NULL, note TEXT DEFAULT NULL, logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_task_period (task_id, employee_id, period_date), INDEX idx_tasklog_emp (employee_id), INDEX idx_tasklog_period_emp (period_date, employee_id), INDEX idx_tasklog_score (score), FOREIGN KEY (task_id) REFERENCES employee_tasks(id) ON DELETE CASCADE, FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- PASSWORD RESET TOKENS (tenant-local; used ONLY for legacy v2 password flow on the Netpoa tenant's imported data — NBO-native reset flow lives in nbo_master.password_resets) CREATE TABLE IF NOT EXISTS password_resets ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, token_hash VARCHAR(80) NOT NULL, expires_at TIMESTAMP NOT NULL, used_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_pwreset_user (user_id), INDEX idx_pwreset_token (token_hash), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Migration-runner bookkeeping for the TENANT stream. CREATE TABLE IF NOT EXISTS schema_migrations ( version VARCHAR(20) PRIMARY KEY, applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, checksum CHAR(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================================ -- GENERIC DEFAULT DATA -- (No Netpoa-specific data here — fresh tenants set their own company settings -- via the first-run wizard. Only defaults that make sense for ANY company.) -- ============================================================================ -- Built-in role catalogue (mirrors v2's custom_roles; tenant admin can edit via UI) INSERT INTO custom_roles (role_key, role_label, permissions, is_system) VALUES ('admin', 'Administrator', '["view","create","edit","delete","settings","users","hr","finance","operations","inventory","sales","reports"]', 1), ('director', 'Director', '["view","create","edit","finance","hr","operations","inventory","sales","reports"]', 1), ('accountant', 'Accountant', '["view","create","edit","finance","inventory","reports"]', 1), ('hr', 'HR', '["view","create","edit","hr"]', 1), ('om', 'Operations Manager', '["view","create","edit","operations","inventory","reports"]', 1), ('sales', 'Sales', '["view","create","edit","sales","inventory"]', 0), ('employee', 'Staff', '["view"]', 1) ON DUPLICATE KEY UPDATE role_key = role_key; -- Tanzania-standard statutory defaults + dropdown category lists INSERT INTO settings (setting_key, setting_value) VALUES -- Company identity — intentionally blank, tenant fills via first-run wizard ('company_name', ''), ('company_address', ''), ('company_phone', ''), ('company_website', ''), ('company_tin', ''), ('company_vrn', ''), -- Tax & payroll defaults (Tanzania) ('currency', 'TZS'), ('tax_rate', '30'), ('nssf_rate_employee', '10'), ('nssf_rate_employer', '10'), ('wcf_rate', '0.5'), ('financial_year_start', '01-01'), -- Invoice VAT ('vat_rate_default', '0'), ('vat_mode', 'exclusive'), -- USD display (off by default) ('usd_display_enabled', '0'), ('usd_rate', '2500'), -- Loan + salary advance windows ('salary_advance_enabled', '1'), ('salary_advance_day_from', '10'), ('salary_advance_day_to', '13'), ('loan_enabled', '1'), ('loan_day_from', '10'), ('loan_day_to', '13'), ('loan_advance_closed_msg', 'You cannot apply to Loan or Salary advance this time'), -- WHMCS (disabled by default — only the Netpoa tenant will fill these) ('whmcs_url', ''), ('whmcs_api_identifier', ''), ('whmcs_api_secret', ''), -- Invoice payment details (tenant admin fills via Settings page) ('pay_bank_name', ''), ('pay_bank_account', ''), ('pay_bank_branch', ''), ('pay_swift_code', ''), ('pay_lipa_number', ''), ('pay_mobile_money', ''), ('pay_instructions', ''), -- Dropdown category lists (tenant admin can edit from Settings) ('expense_categories', '["Server - Hosting","Domain Registration","Licenses","Salaries","Director Remuneration","Office Rent","Internet & Utilities","Transport & Fuel","Marketing","Petty Cash","Tax","Banking Fees","Other"]'), ('invoice_categories', '["Consulting","Services","Training","Sales","Other"]'), ('petty_categories', '["Parking","Breakfast/Lunch","Water","Electricity","Phone Bill","Internet","Stationery","Transport","Office Supplies","Other"]'), ('departments', '["General","Finance","Technical","Operations","HR","Management","Sales","Other"]') ON DUPLICATE KEY UPDATE setting_key = setting_key;