SELECT 
    a.account_number,
    a.account_name,
    a.account_type,
    SUM(CASE WHEN je.entry_type = 'DEBIT' THEN je.amount ELSE 0 END) as total_debits,
    SUM(CASE WHEN je.entry_type = 'CREDIT' THEN je.amount ELSE 0 END) as total_credits,
    SUM(CASE WHEN je.entry_type = 'DEBIT' THEN je.amount ELSE -je.amount END) as balance
FROM gl_accounts a
JOIN gl_journal_lines jl ON a.account_id = jl.account_id
JOIN gl_journal_entries je ON jl.journal_id = je.journal_id
WHERE je.fiscal_year = YEAR(CURRENT_DATE)
  AND je.status = 'POSTED'
GROUP BY a.account_number, a.account_name, a.account_type
ORDER BY a.account_number;

SELECT 
    v.vendor_name,
    i.invoice_number,
    i.invoice_date,
    i.due_date,
    i.amount_due,
    DATEDIFF(CURRENT_DATE, i.due_date) as days_overdue,
    CASE 
        WHEN DATEDIFF(CURRENT_DATE, i.due_date) BETWEEN 1 AND 30 THEN '1-30 days'
        WHEN DATEDIFF(CURRENT_DATE, i.due_date) BETWEEN 31 AND 60 THEN '31-60 days'
        WHEN DATEDIFF(CURRENT_DATE, i.due_date) BETWEEN 61 AND 90 THEN '61-90 days'
        WHEN DATEDIFF(CURRENT_DATE, i.due_date) > 90 THEN '90+ days'
        ELSE 'Current'
    END as aging_bucket
FROM ap_invoices i
JOIN vendors v ON i.vendor_id = v.vendor_id
WHERE i.payment_status != 'PAID'
ORDER BY days_overdue DESC;

SELECT 
    fp.period_name,
    SUM(CASE WHEN a.account_type = 'REVENUE' THEN jl.amount ELSE 0 END) as actual_revenue,
    b.budget_amount,
    SUM(CASE WHEN a.account_type = 'REVENUE' THEN jl.amount ELSE 0 END) - b.budget_amount as variance,
    ROUND((SUM(CASE WHEN a.account_type = 'REVENUE' THEN jl.amount ELSE 0 END) / 
        NULLIF(b.budget_amount, 0)) * 100, 1) as pct_of_budget
FROM gl_journal_lines jl
JOIN gl_accounts a ON jl.account_id = a.account_id
JOIN fiscal_periods fp ON jl.period_id = fp.period_id
LEFT JOIN budgets b ON fp.period_id = b.period_id AND b.account_type = 'REVENUE'
WHERE fp.fiscal_year = YEAR(CURRENT_DATE)
GROUP BY fp.period_name, b.budget_amount
ORDER BY fp.period_start;

SELECT 
    i.item_code,
    i.item_name,
    i.category,
    w.warehouse_name,
    iq.quantity_on_hand,
    i.reorder_point,
    i.reorder_quantity,
    iq.quantity_on_hand - i.reorder_point as units_above_reorder
FROM items i
JOIN item_quantities iq ON i.item_id = iq.item_id
JOIN warehouses w ON iq.warehouse_id = w.warehouse_id
WHERE iq.quantity_on_hand <= i.reorder_point
ORDER BY units_above_reorder ASC;

SELECT 
    i.category,
    COUNT(*) as sku_count,
    SUM(iq.quantity_on_hand) as total_units,
    SUM(iq.quantity_on_hand * i.unit_cost) as total_value,
    AVG(i.unit_cost) as avg_unit_cost
FROM items i
JOIN item_quantities iq ON i.item_id = iq.item_id
WHERE i.status = 'ACTIVE'
GROUP BY i.category
ORDER BY total_value DESC;

SELECT 
    c.customer_name,
    c.region,
    COUNT(DISTINCT so.order_id) as total_orders,
    SUM(sol.quantity * sol.unit_price) as total_revenue,
    AVG(sol.quantity * sol.unit_price) as avg_order_value,
    MAX(so.order_date) as last_order_date
FROM customers c
JOIN sales_orders so ON c.customer_id = so.customer_id
JOIN sales_order_lines sol ON so.order_id = sol.order_id
WHERE so.status = 'COMPLETED'
  AND so.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
GROUP BY c.customer_id
ORDER BY total_revenue DESC
LIMIT 20;

SELECT 
    c.region,
    QUARTER(so.order_date) as quarter,
    COUNT(DISTINCT so.order_id) as orders,
    SUM(sol.quantity * sol.unit_price) as revenue,
    COUNT(DISTINCT c.customer_id) as unique_customers
FROM sales_orders so
JOIN sales_order_lines sol ON so.order_id = sol.order_id
JOIN customers c ON so.customer_id = c.customer_id
WHERE YEAR(so.order_date) = YEAR(CURRENT_DATE)
  AND so.status = 'COMPLETED'
GROUP BY c.region, QUARTER(so.order_date)
ORDER BY c.region, quarter;

SELECT 
    d.department_name,
    COUNT(*) as headcount,
    AVG(e.base_salary) as avg_salary,
    MIN(e.base_salary) as min_salary,
    MAX(e.base_salary) as max_salary,
    SUM(e.base_salary) as total_payroll
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.status = 'ACTIVE'
GROUP BY d.department_name
ORDER BY headcount DESC;