Rate Contracts Logic

-- Identify awarded RFQ items for rate contract creation
SELECT
    q.id AS quotation_id,
    qi.id AS quotation_item_id,
    q.vendor_id,
    qi.item_id,
    qi.item_description,
    qi.unit_price,
    qi.uom_id,
    qi.tax_code_id,
    r.id AS rfq_id,
    r.company_id
FROM rfq.quotations q
JOIN rfq.quotation_items qi ON qi.quotation_id = q.id
JOIN rfq.rfqs r ON r.id = q.rfq_id
WHERE q.is_selected = true
  AND r.status = 'AWARDED'
  AND r.id = @rfqId;

-- Insert rate contract from awarded quotation item
INSERT INTO rfq.rate_contracts (
    rate_contract_number,
    vendor_id,
    item_id,
    item_description,
    rfq_id,
    quotation_id,
    company_id,
    supply_rate,
    installation_rate,
    total_rate,
    uom_id,
    tax_code_id,
    tax_percent,
    currency_code,
    valid_from,
    valid_until,
    min_order_quantity,
    max_order_quantity,
    delivery_days,
    status,
    approval_status,
    created_by,
    metadata
) VALUES (
    generate_rate_contract_number(@companyId),  -- e.g., RC-2026-001
    @vendorId,
    @itemId,
    @itemDescription,
    @rfqId,
    @quotationId,
    @companyId,
    @supplyRate,
    @installationRate,
    @supplyRate + @installationRate,
    @uomId,
    @taxCodeId,
    @taxPercent,
    @currencyCode,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '1 year',  -- Default 1 year validity
    @minQty,
    @maxQty,
    @deliveryDays,
    'PENDING_APPROVAL',
    'PENDING',
    @userId,
    '{}'::jsonb
);

-- Insert rate contract terms
INSERT INTO rfq.rate_contract_terms (
    rate_contract_id,
    term_type,
    term_category,
    term_text,
    is_mandatory,
    display_order
) VALUES
(@rateContractId, 'PAYMENT', 'PAYMENT_TERMS', 'Net 30 days from invoice date', true, 1),
(@rateContractId, 'DELIVERY', 'LEAD_TIME', '15 working days from PO date', true, 2),
(@rateContractId, 'WARRANTY', 'WARRANTY_PERIOD', '12 months warranty from delivery', false, 3),
(@rateContractId, 'PENALTY', 'LATE_DELIVERY', '0.5% penalty per week for delayed delivery', true, 4);

-- Approve rate contract
UPDATE rfq.rate_contracts
SET
    approval_status = 'APPROVED',
    status = 'ACTIVE',
    approved_by = @approverId,
    approved_at = NOW(),
    updated_at = NOW()
WHERE id = @rateContractId;

-- Search active rate contracts for a given item
SELECT
    rc.id,
    rc.rate_contract_number,
    v.vendor_name,
    i.item_code,
    i.item_name,
    rc.total_rate,
    u.uom_code,
    rc.valid_from,
    rc.valid_until,
    rc.delivery_days,
    rc.total_quantity_allowed,
    rc.total_quantity_consumed,
    (rc.total_quantity_allowed - rc.total_quantity_consumed) AS available_quantity
FROM rfq.rate_contracts rc
JOIN vendor.vendors v ON v.id = rc.vendor_id
JOIN inventory.items i ON i.id = rc.item_id
JOIN master.units_of_measure u ON u.id = rc.uom_id
WHERE rc.company_id = @companyId
  AND rc.status = 'ACTIVE'
  AND rc.valid_from <= CURRENT_DATE
  AND rc.valid_until >= CURRENT_DATE
  AND (rc.total_quantity_allowed IS NULL OR rc.total_quantity_consumed < rc.total_quantity_allowed)
  AND i.id = @itemId
ORDER BY rc.total_rate ASC;

-- Create PO against rate contract
INSERT INTO procurement.purchase_orders (
    po_number,
    company_id,
    vendor_id,
    po_type,
    rate_contract_id,
    buyer_id,
    po_date,
    status,
    currency_code,
    payment_terms_id,
    delivery_terms_id,
    created_by
) VALUES (
    generate_po_number(@companyId),
    @companyId,
    @vendorId,  -- From rate contract
    'CONTRACT_RELEASE',
    @rateContractId,
    @buyerId,
    CURRENT_DATE,
    'DRAFT',
    @currencyCode,  -- From rate contract
    @paymentTermsId,  -- From rate contract terms
    @deliveryTermsId,  -- From rate contract terms
    @userId
);

-- Add PO line item referencing rate contract
INSERT INTO procurement.purchase_order_items (
    po_id,
    line_number,
    item_id,
    item_description,
    quantity,
    uom_id,
    unit_price,
    tax_code_id,
    tax_amount,
    line_total,
    rate_contract_id,
    delivery_date
) VALUES (
    @poId,
    1,
    @itemId,  -- From rate contract
    @itemDescription,
    @quantity,  -- User input (within min/max limits)
    @uomId,  -- From rate contract
    @totalRate,  -- From rate contract (supply + installation)
    @taxCodeId,  -- From rate contract
    @quantity * @totalRate * (@taxPercent / 100),
    @quantity * @totalRate * (1 + @taxPercent / 100),
    @rateContractId,
    CURRENT_DATE + (@deliveryDays || ' days')::interval  -- From rate contract
);

-- Update rate contract utilization after PO approval
UPDATE rfq.rate_contracts
SET
    total_quantity_consumed = total_quantity_consumed + @poQuantity,
    total_value_consumed = total_value_consumed + @poLineTotal,
    status = CASE
        WHEN total_quantity_allowed IS NOT NULL
             AND (total_quantity_consumed + @poQuantity) >= total_quantity_allowed
        THEN 'EXHAUSTED'
        ELSE status
    END,
    updated_at = NOW()
WHERE id = @rateContractId;

-- Rate contract utilization summary
SELECT
    rc.rate_contract_number,
    v.vendor_name,
    i.item_name,
    rc.total_quantity_allowed,
    rc.total_quantity_consumed,
    ROUND((rc.total_quantity_consumed::numeric / NULLIF(rc.total_quantity_allowed, 0)) * 100, 2) AS utilization_percent,
    rc.total_value_allowed,
    rc.total_value_consumed,
    rc.valid_from,
    rc.valid_until,
    (rc.valid_until - CURRENT_DATE) AS days_remaining,
    rc.status
FROM rfq.rate_contracts rc
JOIN vendor.vendors v ON v.id = rc.vendor_id
JOIN inventory.items i ON i.id = rc.item_id
WHERE rc.company_id = @companyId
  AND rc.status IN ('ACTIVE', 'APPROVED')
ORDER BY utilization_percent DESC;

-- Cost savings via rate contracts (vs. spot pricing)
SELECT
    rc.rate_contract_number,
    i.item_name,
    rc.total_rate AS contract_rate,
    i.standard_cost AS current_market_rate,
    (i.standard_cost - rc.total_rate) AS savings_per_unit,
    rc.total_quantity_consumed,
    ((i.standard_cost - rc.total_rate) * rc.total_quantity_consumed) AS total_savings
FROM rfq.rate_contracts rc
JOIN inventory.items i ON i.id = rc.item_id
WHERE rc.company_id = @companyId
  AND rc.status = 'ACTIVE'
  AND i.standard_cost > rc.total_rate
ORDER BY total_savings DESC;

-- Vendor performance against rate contracts
SELECT
    v.vendor_name,
    COUNT(DISTINCT rc.id) AS total_contracts,
    COUNT(DISTINCT po.id) AS total_pos_released,
    AVG(vs.on_time_delivery_pct) AS avg_otd,
    AVG(vs.quality_score) AS avg_quality,
    SUM(rc.total_value_consumed) AS total_business_value
FROM rfq.rate_contracts rc
JOIN vendor.vendors v ON v.id = rc.vendor_id
LEFT JOIN procurement.purchase_orders po ON po.rate_contract_id = rc.id
LEFT JOIN vendor.vendor_scores vs ON vs.vendor_id = v.id AND vs.is_latest = true
WHERE rc.company_id = @companyId
GROUP BY v.id, v.vendor_name
ORDER BY total_business_value DESC;