-- ===================================================================
-- Internal Invoice Management & Approval System — Database Schema
-- ===================================================================
-- This script only CREATES TABLES. It does not create or select a
-- database, because shared/cPanel hosting accounts are not permitted
-- to run CREATE DATABASE or USE from SQL — the database must already
-- exist and be selected before you import this file.
--
--   Shared hosting (cPanel):
--     1. cPanel → MySQL Databases → create a database (it will be
--        auto-prefixed with your account name, e.g. myacct_invoice_system).
--     2. Open phpMyAdmin, click that database in the left sidebar to
--        select it, then Import → this file → Go.
--
--   VPS / local MySQL with root access:
--     1. mysql -u root -p -e "CREATE DATABASE invoice_system CHARACTER SET utf8mb4;"
--     2. mysql -u root -p invoice_system < schema.sql
--
-- Either way, set DB_NAME in config.php to whatever the database is
-- actually named on your server (it will NOT be plain "invoice_system"
-- on shared hosting).
-- ===================================================================
-- Users & roles
--   requester      = regular employee who creates invoices
--   manager        = line manager, approves their direct reports' invoices
--   finance_admin  = second-level approver + full reporting access
--   admin          = manages users, roles, and line-manager assignments
--   super_admin    = full access — can approve any invoice at either
--                     stage regardless of assignment, see every report,
--                     and manage users. Use sparingly.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(120) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('requester','manager','finance_admin','admin','super_admin') NOT NULL DEFAULT 'requester',
    department      VARCHAR(120),
    line_manager_id INT NULL,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (line_manager_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Customers — a shared directory any Creator can pick from (or add
-- to) when raising an invoice, so a customer only needs to be
-- entered once.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    phone       VARCHAR(50) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Invoices
-- The old single "description" field is now an optional overall
-- note/reference (e.g. PO number, project name) — the actual line
-- items with their own descriptions and prices live in invoice_items
-- below. `amount` is the sum of all line items' totals, kept on the
-- invoice row itself so existing reporting queries don't need to change.
-- customer_id is optional — an invoice can stay purely internal, or
-- be linked to a customer so it can be emailed to them once approved.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS invoices (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number       VARCHAR(30) NOT NULL UNIQUE,
    requester_id         INT NOT NULL,
    line_manager_id      INT NULL,
    customer_id          INT NULL,
    amount               DECIMAL(12,2) NOT NULL,
    currency             VARCHAR(10) NOT NULL DEFAULT 'AED',
    description          TEXT NULL,
    category             VARCHAR(120),
    department           VARCHAR(120),
    status               ENUM('draft','pending_manager','pending_finance','approved','rejected','changes_requested','paid')
                             NOT NULL DEFAULT 'draft',
    rejection_reason     TEXT NULL,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at         DATETIME NULL,
    approved_at          DATETIME NULL,
    customer_emailed_at  DATETIME NULL,
    FOREIGN KEY (requester_id) REFERENCES users(id),
    FOREIGN KEY (line_manager_id) REFERENCES users(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_approved_at (approved_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Invoice line items — one row per item: description + quantity + unit price.
-- line_total = quantity * unit_price, and the sum of these across an
-- invoice is stored as invoices.amount.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS invoice_items (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id   INT NOT NULL,
    description  VARCHAR(255) NOT NULL,
    quantity     DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price   DECIMAL(12,2) NOT NULL,
    line_total   DECIMAL(12,2) NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Approval / audit log — one row per action taken on an invoice
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS approval_logs (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id   INT NOT NULL,
    approver_id  INT NOT NULL,
    action       ENUM('approved','rejected','changes_requested') NOT NULL,
    comment      TEXT,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (approver_id) REFERENCES users(id)
) ENGINE=InnoDB;