Contract Management System

-- Query to find applicable contract for a vendor-item combination
SELECT
    c.contract_number,
    c.contract_name,
    ci.unit_price,
    ci.discount_percent,
    (c.contract_value - c.utilized_value) AS remaining_value
FROM contract.contracts c
JOIN contract.contract_items ci ON c.id = ci.contract_id
WHERE
    c.vendor_id = :vendor_id
    AND ci.item_id = :item_id
    AND c.status = 'ACTIVE'
    AND CURRENT_DATE BETWEEN c.start_date AND c.end_date
    AND CURRENT_DATE BETWEEN ci.effective_from AND ci.effective_until
    AND ci.is_active = true
ORDER BY ci.unit_price ASC
LIMIT 1;

-- Scheduled job: Check contracts expiring within notice period
SELECT
    c.id,
    c.contract_number,
    c.end_date,
    c.renewal_notice_days,
    c.owner_id
FROM contract.contracts c
WHERE
    c.status = 'ACTIVE'
    AND c.end_date BETWEEN CURRENT_DATE
        AND CURRENT_DATE + (c.renewal_notice_days || ' days')::INTERVAL
    AND NOT EXISTS (
        -- Avoid duplicate notifications
        SELECT 1 FROM config.notifications n
        WHERE n.entity_type = 'CONTRACT'
        AND n.entity_id = c.id
        AND n.notification_type = 'EXPIRY_REMINDER'
        AND n.created_at > CURRENT_DATE - INTERVAL '7 days'
    );

-- Query non-compliant contracts with severity
SELECT
    c.contract_number,
    c.vendor_id,
    v.vendor_name,
    cc.compliance_type,
    cc.compliance_item,
    cc.severity,
    cc.action_due_date,
    CASE
        WHEN cc.action_due_date < CURRENT_DATE THEN 'OVERDUE'
        WHEN cc.action_due_date <= CURRENT_DATE + 7 THEN 'DUE_SOON'
        ELSE 'ON_TRACK'
    END AS urgency
FROM contract.contract_compliance cc
JOIN contract.contracts c ON cc.contract_id = c.id
JOIN vendor.vendors v ON c.vendor_id = v.id
WHERE
    cc.is_compliant = false
    AND cc.action_completed_date IS NULL
    AND c.status = 'ACTIVE'
ORDER BY
    CASE cc.severity
        WHEN 'CRITICAL' THEN 1
        WHEN 'HIGH' THEN 2
        WHEN 'MEDIUM' THEN 3
        ELSE 4
    END,
    cc.action_due_date;