-- NEW PORTFOLIO DEMONSTRATION / SYNTHETIC DATA ONLY
-- SQL Server / T-SQL. Read-only, self-contained; no production tables used.
-- Reporting grain: one row per animal, including animals without a match.
-- Assumption: start inclusive, end exclusive; NULL end is open-ended.
-- A unique interval match resolves the owner. Zero or multiple matches
-- withhold the owner for review. This checks point-in-time match cardinality,
-- not whole-history validity or source completeness.
DECLARE @ReportDate date = '20250601';

WITH Animals AS (
    SELECT animal_id
    FROM (VALUES ('DEMO-01'), ('DEMO-02'), ('DEMO-03')) a(animal_id)
), Ownership AS (
    SELECT animal_id, owner_label,
           CONVERT(date, starts_on, 112) AS starts_on,
           CONVERT(date, ends_on, 112) AS ends_on
    FROM (VALUES
        ('DEMO-01', 'Owner A', '20250101', '20250601'),
        ('DEMO-01', 'Owner B', '20250601', NULL),
        ('DEMO-02', 'Owner C', '20250101', NULL),
        ('DEMO-02', 'Owner D', '20250515', '20250901'),
        ('DEMO-03', 'Owner E', '20250101', '20250701')
    ) o(animal_id, owner_label, starts_on, ends_on)
), Matches AS (
    SELECT a.animal_id, COUNT(o.animal_id) AS match_count,
           MAX(o.owner_label) AS candidate_owner
    FROM Animals a
    LEFT JOIN Ownership o ON o.animal_id = a.animal_id
        AND o.starts_on <= @ReportDate
        AND (o.ends_on > @ReportDate OR o.ends_on IS NULL)
    GROUP BY a.animal_id
)
SELECT animal_id, @ReportDate AS reporting_date, match_count,
       CASE WHEN match_count = 1 THEN candidate_owner END AS owner_label,
       CASE WHEN match_count = 1 THEN 'Resolved'
            WHEN match_count = 0 THEN 'Gap - review'
            ELSE 'Overlap - review' END AS decision
FROM Matches
ORDER BY animal_id;

-- Expected checks:
-- 2025-05-31: DEMO-01 Owner A; DEMO-02 overlap; DEMO-03 Owner E.
-- 2025-06-01: DEMO-01 Owner B; DEMO-02 overlap; DEMO-03 Owner E.
-- 2025-07-01: DEMO-01 Owner B; DEMO-02 overlap; DEMO-03 gap.
-- 2025-09-01: DEMO-01 Owner B; DEMO-02 Owner C; DEMO-03 gap.