================================================================================
MPUMALANGA PVC - SYSTEM OVERVIEW
================================================================================
Last Updated: 2026-03-26
================================================================================

WHAT IS THIS SYSTEM?
--------------------
This is a custom-built business management system for Mpumalanga PVC Ceilings,
Flooring and Artificial Grass. It handles the full sales and operations workflow
from quoting a client through to invoicing, payments, supplier orders, and
on-site work scheduling.

TECH STACK
----------
- Language:   PHP (server-side rendering, no frontend framework)
- Database:   MariaDB (hosted on ewg.dedicated.co.za)
- Database name: elegaysv_edesigns_pvc
- PDF:        FPDF library (classes/fpdf.php)
- Styling:    Custom CSS utility classes (width_80, background_1, border_radius etc.)
- JavaScript: Vanilla JS only, no jQuery or libraries

HOSTING
-------
Live:  mpumalangapvc.elegantwork.co.za
Dev:   dev.mpumalangapvc.elegantwork.co.za (dev.edesigns.elegantwork.co.za)
DB host: ewg.dedicated.co.za

================================================================================
FOLDER STRUCTURE
================================================================================

/                        Root
/index.php               Redirect to login or app home
/login.php               Login page
/logout.php              Session destroy + redirect
/classes/                All PHP class files (autoloaded)
/app/                    All application modules
/app/home.php            Financial Year Dashboard
/app/clients/
/app/quotes/
/app/invoices/
/app/payments/
/app/c_notes/            Credit Notes
/app/delivery_note/
/app/supplier_orders/
/app/suppliers/
/app/stock/
/app/work_schedule/
/default_app_data/       System settings (users, user types, table views, links)
/assets/                 Logo images used in PDFs
/app/old/                Old/unused supplier order files - ignore these

================================================================================
CORE CLASS SYSTEM (classes/)
================================================================================

Every page starts with:
  include "../../classes/autoload.php";

autoload.php loads all classes and creates two global objects:
  $db        = new db();          (database connection)
  $function  = new app_features(); (helper/utility functions)

--------------------------------------------------------------------------------
CLASS: db  (db.class.php)
--------------------------------------------------------------------------------
Handles all database interaction.

  Constructor
    - Connects to MariaDB.
    - On first run, auto-creates system tables if missing:
      logs, users, user_types, user_table_views, default_table_views,
      default_links, user_type_app_access, excluded_file_identifiers, status
    - Creates a default DEV user and ADMIN user type.

  $db->query($table_name, $sql)
    - Runs any SQL statement.
    - First argument is the table name (used for logging and validation).
    - Checks the table exists before running; prints an error if it doesn't.
    - For INSERT queries: returns mysqli_insert_id() (the new record ID).
      IMPORTANT: Many tables in this app have NO AUTO_INCREMENT on record_id,
      so mysqli_insert_id() returns 0 for those tables. Do not rely on this
      return value for redirect logic on those tables.
    - For all other queries: returns the mysqli result object.
    - On SQL error: prints the error and exits.
    - Destructor automatically logs every non-SELECT query to the logs table.

  $db->select_query($table, $selector, $where_clause)
    - Builds a SELECT query for you. Less commonly used.

  $db->login($username, $password)
    - SHA256 hashes the password and checks against the users table.
    - Sets $_SESSION['user_id'] and $_SESSION['user_type'] on success.

  $db->session_check()
    - Returns the current user_id from session (or 0 if not logged in).

  $db->check_table_exists($table)
    - Returns true/false. Used internally before every query.

KNOWN ISSUE WITH db->query() AND INSERTS:
  Tables without AUTO_INCREMENT always return 0 from query() on INSERT.
  Affected tables: clients, invoices, quotes, orders, credit_notes,
  delivery_note, supplier_orders, etc.
  Workaround: After INSERT, immediately SELECT the record back by a known
  unique value to get the real record_id.

--------------------------------------------------------------------------------
CLASS: inner_app  (app.class.php)
--------------------------------------------------------------------------------
Controls the page shell (sidebar quick bar and content area wrapper).

  $app->quick_bar($folder_path)
    - Renders the left-side navigation bar.
    - Scans the current folder and creates a button for each visible PHP file.
    - Files are hidden from the bar if their name contains words in the
      excluded_file_identifiers table (default: pdf, edit, save, update, delete).
    - Also shows section buttons for all subfolders in /default_app_data/.
    - Pass the folder path string e.g. "/app/clients/" to scope it correctly.

  $app->app_start()
    - Opens the main content div wrapper and search bar overlay.

  $app->__destruct()
    - Closes the wrapper divs automatically when the page finishes rendering.

  quick_bar_button($text, $link)       - Renders a nav button linking to a page.
  quick_bar_section_button($text, $link) - Renders a section heading button.
  search_button()                      - Injects the page-level search overlay JS.

--------------------------------------------------------------------------------
CLASS: app_features  (app.class.php)  -- accessed via $function
--------------------------------------------------------------------------------
Reusable helper functions used across all modules.

  get_clients_datalist($list_id)
    - Echoes a <datalist> of all clients.
    - Format: "Client Name : record_id"
    - Used in quotes, invoices, etc. for client selection.

  get_suppliers_datalist($list_id)
    - Echoes a <datalist> of all suppliers.
    - Format: "Supplier Name : record_id : CODE"

  get_stock_datalist($list_id)
    - Echoes a <datalist> of all active stock items (status != 0).
    - Format: "CODE~Name~UOM~retail_price"
    - Tilde (~) separated so JS can split and populate multiple fields at once.

  get_stock_datalist_cost($list_id)
    - Same as get_stock_datalist but uses cost price instead of retail.

  get_invoices_list_as_record_id($list_id)
    - Echoes a <datalist> of all invoices.
    - Each option has value=invoice_number and data-id=record_id attribute.
    - Used anywhere an invoice needs to be selected.

  get_unpaid_invoices_list()
    - Echoes plain <option> tags for invoices that are not fully paid.
    - Used in payments add/edit for the invoice selector.

  get_invoices(), get_clients(), get_suppliers(), get_stock()
    - Echo <option> tags for use inside <select> dropdowns.

  get_users(), get_users_list($status), get_user_types()
    - Echo <option> tags for user-related dropdowns.

  get_status()
    - Echoes <option> tags from the status table (ACTIVE / INACTIVE).

  number_to_save($number)
    - Strips R, commas, spaces, and non-breaking spaces from a currency string.
    - Returns a clean decimal number string safe to save to DB.
    - Used in all save/update handlers for price fields.

  get_username($user_id) / get_initials($user_id)
    - Returns the username or initials for a given user_id.

  get_supplier_name($supplier_id)
    - Returns the supplier name for a given supplier_id.

  get_invoice_no($invoice_id)
    - Returns the invoice_number string for a given invoice record_id.

  explainSQL($query)
    - Debug helper. Takes a SQL string and returns a plain-English description.

--------------------------------------------------------------------------------
CLASS: table  (table.class.php)
--------------------------------------------------------------------------------
Renders a configurable data table from a database table name.

  new table("invoices")
    - Queries the named table and renders it as an HTML table.
    - Columns are configured per user via user_table_views in the DB.

  $table->add_action_button("edit_invoices.php")
    - Adds an "Edit" button column that links to the given file with ?record_id=X.

--------------------------------------------------------------------------------
CLASS: html  (html.class.php)
--------------------------------------------------------------------------------
Sets the HTML page title and renders the <head> and opening tags.
  new html("PAGE TITLE")

================================================================================
DATABASE - KEY TABLES
================================================================================

clients           - Client records (name, address, email, contact, vat, reg)
suppliers         - Supplier records (name, code, address, email, contact)
stock             - Stock items (code, name, cost, retail, UOM, supplier_id)

quotes            - Quote header (client_id, order_type, quote_number, status...)
quote_list        - Quote line items (quote_id, stock_id, qty, price, size_m, pannels)

invoices          - Invoice header (client_id, invoice_number, quote_id, status...)
invoice_list      - Invoice line items (invoice_id, stock_id, qty, price, size_m, pannels)

payments          - Payments against invoices (invoice_id, amount, date_time, type)

credit_notes      - Credit note header (invoice_id, client_banking_details)
credit_notes_list - Credit note line items (credit_note_id, name, amount)

delivery_note     - Delivery note header (quote_id stores invoice_id, note)
delivery_note_list - Delivery note lines (delivery_note_id, stock_code, description,
                     size_m, pannels)
                  NOTE: delivery_note.quote_id actually stores an invoice_id
                  (misleading column name - this is a known naming issue).
                  NOTE: delivery_note_list requires an ALTER to add the description
                  column if missing:
                  ALTER TABLE delivery_note_list ADD description varchar(2550)
                  NOT NULL AFTER stock_code;

supplier_orders   - Supplier order header (supplier_order_no, supplier_id, invoice_id)
supplier_order_list - Supplier order lines (supplier_order_id, stock_id, qty, price,
                      size_m, pannels)

orders            - Work schedule/installation order (invoice_id, order_date,
                    description, clause, user_id)
order_list        - Items from invoice to be installed (order_id, stock_id, qty, size_m)
order_checklist   - Checklist items for a work schedule (order_id, text)

logs              - Auto-populated. Every INSERT/UPDATE/DELETE is logged here
                    with table_name, user_id, query, date_time.

company_info      - Company details used in PDFs (name, tel, email, bank, address...)

================================================================================
FINANCIAL YEAR DEFINITION
================================================================================
South African financial year: 1 March to last day of February.
Example: FY2025/26 = 1 March 2025 to 28 Feb 2026.
The dashboard (app/home.php) uses this to filter all KPI data.

================================================================================
HOW SAVE FORMS WORK (all modules use the same pattern)
================================================================================
No HTML <form> tags are used. Instead, a JavaScript save() function:
1. Collects all input, select, and textarea values into a payload object.
2. Creates a hidden <form> dynamically via createElement.
3. Appends hidden inputs for each payload value (including arrays for [] fields).
4. Submits the form via POST to the given handler URL.

Array fields (e.g. stock_code[], qty[], price[]) are used for line item tables.
The save handlers loop over these arrays with a matching $index counter.

================================================================================
STOCK DATALIST FORMAT (tilde-separated)
================================================================================
Stock items in datalists use ~ as a separator so one input can carry all data:
  CODE~Description~UOM~Price

JavaScript splits on ~ to populate:
  data[0] = stock code
  data[1] = stock description
  data[2] = unit of measure
  data[3] = price

================================================================================
SQL INJECTION WARNING
================================================================================
All $_POST and $_GET values are inserted directly into SQL strings throughout
the codebase with no escaping or prepared statements. This is a known security
issue. When fixing bugs, be careful not to introduce additional unescaped user
input into queries.

================================================================================