Budget Management & Commitment Tracking

-- Check budget availability before PO approval
WITH budget_check AS (
    SELECT
        b.id AS budget_id,
        b.budget_amount,
        b.committed_amount,
        b.spent_amount,
        b.budget_amount - b.committed_amount - b.spent_amount AS available,
        :po_amount AS requested_amount
    FROM planning.budgets b
    WHERE b.company_id = :company_id
      AND b.fiscal_year = :fiscal_year
      AND b.department_id = :department_id
      AND b.cost_center_id = :cost_center_id
      AND b.status = 'APPROVED'
)
SELECT
    budget_id,
    available,
    requested_amount,
    CASE
        WHEN available >= requested_amount THEN 'SUFFICIENT'
        WHEN available > 0 THEN 'PARTIAL'
        ELSE 'INSUFFICIENT'
    END AS budget_status
FROM budget_check;

-- Create commitment on PO approval
BEGIN;

-- Record commitment transaction
INSERT INTO planning.budget_line_items (
    budget_id, transaction_type, transaction_date,
    reference_type, reference_id, reference_number, amount, description
) VALUES (
    :budget_id,
    'COMMITMENT',
    CURRENT_DATE,
    'PO',
    :po_id,
    :po_number,
    :po_amount,
    'PO commitment for ' || :po_number
);

-- Update budget committed amount
UPDATE planning.budgets
SET
    committed_amount = committed_amount + :po_amount,
    available_amount = budget_amount - committed_amount - :po_amount - spent_amount
WHERE id = :budget_id;

COMMIT;

-- Convert commitment to spend on invoice posting
BEGIN;

-- Release commitment
INSERT INTO planning.budget_line_items (
    budget_id, transaction_type, reference_type, reference_id, amount, description
) VALUES (
    :budget_id, 'RELEASE', 'INVOICE', :invoice_id,
    -:committed_amount, 'Release commitment for invoice ' || :invoice_number
);

-- Record spend
INSERT INTO planning.budget_line_items (
    budget_id, transaction_type, reference_type, reference_id, amount, description
) VALUES (
    :budget_id, 'SPEND', 'INVOICE', :invoice_id,
    :invoice_amount, 'Invoice spend ' || :invoice_number
);

-- Update budget amounts
UPDATE planning.budgets
SET
    committed_amount = committed_amount - :committed_amount,
    spent_amount = spent_amount + :invoice_amount,
    available_amount = budget_amount - committed_amount + :committed_amount - spent_amount - :invoice_amount
WHERE id = :budget_id;

COMMIT;

SELECT
    d.department_name,
    b.budget_type,
    b.fiscal_year,
    b.budget_amount,
    b.committed_amount,
    b.spent_amount,
    b.available_amount,
    ROUND((b.committed_amount + b.spent_amount) / b.budget_amount * 100, 2) AS utilization_pct,
    ROUND(b.spent_amount / b.budget_amount * 100, 2) AS spent_pct
FROM planning.budgets b
JOIN organization.departments d ON b.department_id = d.id
WHERE b.company_id = :company_id
  AND b.fiscal_year = :fiscal_year
  AND b.status = 'APPROVED'
ORDER BY utilization_pct DESC;

SELECT
    bt.transaction_date,
    bt.transaction_type,
    bt.reference_type,
    bt.reference_number,
    bt.amount,
    bt.description,
    SUM(bt.amount) OVER (
        PARTITION BY bt.budget_id
        ORDER BY bt.transaction_date, bt.created_at
    ) AS running_balance
FROM planning.budget_line_items bt
WHERE bt.budget_id = :budget_id
ORDER BY bt.transaction_date DESC, bt.created_at DESC;

SELECT
    d.department_name,
    cc.cost_center_name,
    b.budget_amount AS budgeted,
    b.spent_amount AS actual,
    b.budget_amount - b.spent_amount AS variance,
    CASE
        WHEN b.spent_amount > b.budget_amount THEN 'OVER'
        WHEN b.spent_amount < b.budget_amount * 0.9 THEN 'UNDER'
        ELSE 'ON_TRACK'
    END AS variance_status
FROM planning.budgets b
JOIN organization.departments d ON b.department_id = d.id
LEFT JOIN organization.cost_centers cc ON b.cost_center_id = cc.id
WHERE b.fiscal_year = :fiscal_year
  AND b.period_type = 'QUARTERLY'
  AND b.period_number = :quarter
ORDER BY variance;