Tax Configuration Logic

-- Auto-determine tax rate from product HSN code
SELECT tr.tax_code, tr.tax_name, tr.tax_type, tr.rate
FROM   config.tax_rates tr
WHERE  tr.hsn_sac_code = product.hsn_code
  AND  tr.is_active = TRUE
  AND  tr.effective_from <= CURRENT_DATE
  AND  (tr.effective_until IS NULL
       OR tr.effective_until >= CURRENT_DATE)
ORDER BY tr.effective_from DESC
LIMIT 1;

-- Service SAC code lookup (same pattern)
SELECT tr.tax_code, tr.rate
FROM   config.tax_rates tr
WHERE  tr.hsn_sac_code = service.sac_code
  AND  tr.is_active = TRUE
  AND  tr.effective_from <= invoice_date
  AND  (tr.effective_until IS NULL
       OR tr.effective_until >= invoice_date);

-- TDS computation logic
tds_amount = invoice_amount * tds_rate / 100

-- Lower deduction certificate handling
IF vendor.has_lower_tds_certificate = TRUE
   AND certificate.valid_until >= CURRENT_DATE
THEN
   effective_tds_rate = certificate.reduced_rate
ELSE
   effective_tds_rate = standard_tds_rate_for_section
END IF

-- Net payable to vendor
net_payable = invoice_amount - tds_amount

-- Line-item tax computation
SELECT
    li.id AS line_item_id, li.taxable_amount, tr.tax_type, tr.rate,
    ROUND(li.taxable_amount * tr.rate / 100, 2) AS tax_amount
FROM   invoice_line_items li
JOIN   config.tax_groups  tg ON tg.id = li.tax_group_id
JOIN   config.tax_rates   tr ON tr.id = ANY(tg.tax_ids)
WHERE  li.invoice_id = :invoice_id AND tr.is_active = TRUE;

-- Invoice-level aggregation by tax type
SELECT
    tr.tax_type,
    SUM(ROUND(li.taxable_amount * tr.rate / 100, 2)) AS total_tax
FROM   invoice_line_items li
JOIN   config.tax_groups  tg ON tg.id = li.tax_group_id
JOIN   config.tax_rates   tr ON tr.id = ANY(tg.tax_ids)
WHERE  li.invoice_id = :invoice_id AND tr.is_active = TRUE
GROUP BY tr.tax_type;

-- Round-off adjustment: ROUND(total, 0) - total

-- Check if vendor has valid exemption
SELECT te.exemption_type, te.exempted_taxes, te.status
FROM   config.tax_exemptions te
WHERE  te.entity_id   = :vendor_id
  AND  te.entity_type = 'VENDOR'
  AND  te.status       = 'ACTIVE'
  AND  te.valid_from  <= CURRENT_DATE
  AND  te.valid_until >= CURRENT_DATE;

-- Valid: apply zero-rate for taxes in exempted_taxes JSONB
-- Expired: auto-update status to 'EXPIRED', apply standard rates

-- Mid-year rate change (e.g., GST on HSN 8471 from 18% to 12%)
-- Step 1: Close old rate record
UPDATE config.tax_rates
SET    effective_until = '2025-10-31'
WHERE  hsn_sac_code = '8471' AND rate = 18.00 AND effective_until IS NULL;

-- Step 2: Insert new rate record
INSERT INTO config.tax_rates (tax_code, tax_name, tax_type, rate, hsn_sac_code, effective_from, is_active)
VALUES ('GST12_8471', 'GST 12% - Computers', 'GST', 12.00, '8471', '2025-11-01', TRUE);

-- Historical lookup for a past invoice
SELECT tr.rate FROM config.tax_rates tr
WHERE  tr.hsn_sac_code = '8471'
  AND  tr.effective_from <= :invoice_date
  AND  (tr.effective_until IS NULL OR tr.effective_until >= :invoice_date)
  AND  tr.is_active = TRUE
ORDER BY tr.effective_from DESC LIMIT 1;