-- SP-FF-01: Feature flag state
SELECT SettingName, SettingValue FROM [System].[Settings] WHERE SettingName = 'ehailing.providers.enabled';

-- SP-PAY-01: Payment gate states for a given application detail
-- Replace :appDetailID
SELECT Ard.ApplicationDetailID, Ard.StatusID, Ard.SubStatusID, Ard.CreatedDate
FROM App.ApplicationDetails Ard WHERE Ard.ApplicationDetailID = :appDetailID;

-- SP-AUD-01..02..ALL: Audit entries for an application
-- Replace :appDetailID
SELECT TOP 100 * FROM Audits.AuditTrail WHERE ItemID = :appDetailID ORDER BY DateCreated DESC;

-- SP-NOTIFY-01..ALL: Last 50 notifications created
SELECT TOP 50 * FROM Crm.Messages ORDER BY DateCreated DESC;

-- SP-CERT-01: Latest certificate for provider by application
-- Replace :appDetailID
SELECT pc.*
FROM Verified.ProviderCertificates pc
INNER JOIN Verified.PlatformProviders pp ON pp.PlatformProviderID = pc.PlatformProviderID
WHERE pp.ApplicationDetailID = :appDetailID
ORDER BY pc.ProviderCertificateID DESC;

-- SP-CERT-02: Detect duplicates by number
-- Replace :certNo
SELECT COUNT(*) AS Cnt FROM Verified.ProviderCertificates WHERE CertificateNumber = :certNo;

-- SP-CERT-03: Reissue chain for provider
-- Replace :platformProviderID
WITH chain AS (
  SELECT ProviderCertificateID, ReissuedFromID, CertificateNumber, IssuedAt, ExpiresAt
  FROM Verified.ProviderCertificates WHERE PlatformProviderID = :platformProviderID
)
SELECT * FROM chain ORDER BY ProviderCertificateID;

-- SP-REG-01: Registry query by name fragment
-- Replace :q
SELECT * FROM Verified.Vw_ProviderRegistry WHERE LOWER(ISNULL(LegalName,'')) LIKE '%' + LOWER(:q) + '%';

-- SP-REG-02: Active status
SELECT * FROM Verified.Vw_ProviderRegistry WHERE Status = 'Active';

-- SP-REG-03: Expiring within N days
-- Replace :days
DECLARE @threshold DATETIME = DATEADD(DAY, :days, GETUTCDATE());
SELECT * FROM Verified.Vw_ProviderRegistry WHERE ExpiresAt IS NOT NULL AND ExpiresAt >= GETUTCDATE() AND ExpiresAt <= @threshold;

-- SP-REG-COUNT: Cardinality check
SELECT COUNT(*) AS ProviderCount FROM Verified.PlatformProviders;


