258 lines
13 KiB
Transact-SQL
258 lines
13 KiB
Transact-SQL
-- ============================================================
|
|
-- Provider Status Mapping Reference Table
|
|
-- ============================================================
|
|
-- Maps provider-specific campaign statuses to platform statuses.
|
|
-- Runtime normalization is config-driven (appsettings.json),
|
|
-- but this table serves as:
|
|
-- 1. Canonical reference / documentation
|
|
-- 2. Admin-editable override (future phase)
|
|
-- 3. Audit trail for mapping changes
|
|
--
|
|
-- Platform statuses: draft, staged, pending, active, paused,
|
|
-- completed, cancelled, error
|
|
-- ============================================================
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'tbProviderStatusMap')
|
|
BEGIN
|
|
CREATE TABLE dbo.tbProviderStatusMap (
|
|
psmId INT IDENTITY(1,1) PRIMARY KEY,
|
|
psmChannelType VARCHAR(50) NOT NULL, -- google_ads, meta, tiktok
|
|
psmProviderStatus VARCHAR(100) NOT NULL, -- raw provider value (ENABLED, DELIVERY_OK, etc.)
|
|
psmPlatformStatus VARCHAR(20) NOT NULL, -- normalized platform value
|
|
psmDescription NVARCHAR(200) NULL, -- human-readable explanation
|
|
psmIsActive BIT NOT NULL DEFAULT 1,
|
|
psmCreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
|
|
psmUpdatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
|
|
|
|
CONSTRAINT UQ_ProviderStatusMap_Channel_Status
|
|
UNIQUE (psmChannelType, psmProviderStatus),
|
|
|
|
CONSTRAINT CK_ProviderStatusMap_PlatformStatus
|
|
CHECK (psmPlatformStatus IN ('draft','staged','pending','active','paused','completed','cancelled','error'))
|
|
);
|
|
|
|
CREATE NONCLUSTERED INDEX IX_ProviderStatusMap_Channel
|
|
ON dbo.tbProviderStatusMap (psmChannelType)
|
|
INCLUDE (psmProviderStatus, psmPlatformStatus)
|
|
WHERE psmIsActive = 1;
|
|
END
|
|
GO
|
|
|
|
-- ============================================================
|
|
-- Seed: Google Ads
|
|
-- ============================================================
|
|
MERGE dbo.tbProviderStatusMap AS tgt
|
|
USING (VALUES
|
|
('google_ads', 'ENABLED', 'active', 'Campaign is serving ads'),
|
|
('google_ads', 'Enabled', 'active', 'Campaign is serving ads (camelCase variant)'),
|
|
('google_ads', 'PAUSED', 'paused', 'Campaign is paused by advertiser'),
|
|
('google_ads', 'Paused', 'paused', 'Campaign is paused (camelCase variant)'),
|
|
('google_ads', 'REMOVED', 'cancelled', 'Campaign has been removed'),
|
|
('google_ads', 'Removed', 'cancelled', 'Campaign has been removed (camelCase variant)'),
|
|
('google_ads', 'UNKNOWN', 'error', 'Unknown status from Google Ads API'),
|
|
('google_ads', 'UNSPECIFIED', 'error', 'Unspecified status from Google Ads API')
|
|
) AS src (channelType, providerStatus, platformStatus, description)
|
|
ON tgt.psmChannelType = src.channelType AND tgt.psmProviderStatus = src.providerStatus
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (psmChannelType, psmProviderStatus, psmPlatformStatus, psmDescription)
|
|
VALUES (src.channelType, src.providerStatus, src.platformStatus, src.description);
|
|
GO
|
|
|
|
-- ============================================================
|
|
-- Seed: Meta (Facebook / Instagram)
|
|
-- ============================================================
|
|
MERGE dbo.tbProviderStatusMap AS tgt
|
|
USING (VALUES
|
|
('meta', 'ACTIVE', 'active', 'Campaign is delivering'),
|
|
('meta', 'PAUSED', 'paused', 'Campaign paused by advertiser'),
|
|
('meta', 'DELETED', 'cancelled', 'Campaign deleted'),
|
|
('meta', 'ARCHIVED', 'completed', 'Campaign archived after completion'),
|
|
('meta', 'IN_PROCESS', 'pending', 'Campaign is being processed'),
|
|
('meta', 'WITH_ISSUES', 'error', 'Campaign has delivery issues'),
|
|
('meta', 'CAMPAIGN_PAUSED', 'paused', 'Parent campaign is paused'),
|
|
('meta', 'ADSET_PAUSED', 'paused', 'Ad set level pause'),
|
|
('meta', 'DISAPPROVED', 'error', 'Ad/campaign disapproved by review'),
|
|
('meta', 'PREAPPROVED', 'pending', 'Preapproved, awaiting final review'),
|
|
('meta', 'PENDING_REVIEW', 'pending', 'Awaiting Meta ad review'),
|
|
('meta', 'PENDING_BILLING_INFO','error', 'Billing information required')
|
|
) AS src (channelType, providerStatus, platformStatus, description)
|
|
ON tgt.psmChannelType = src.channelType AND tgt.psmProviderStatus = src.providerStatus
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (psmChannelType, psmProviderStatus, psmPlatformStatus, psmDescription)
|
|
VALUES (src.channelType, src.providerStatus, src.platformStatus, src.description);
|
|
GO
|
|
|
|
-- ============================================================
|
|
-- Seed: TikTok
|
|
-- ============================================================
|
|
MERGE dbo.tbProviderStatusMap AS tgt
|
|
USING (VALUES
|
|
('tiktok', 'ENABLE', 'active', 'Campaign is active and delivering'),
|
|
('tiktok', 'CAMPAIGN_STATUS_ENABLE', 'active', 'Campaign enabled (prefixed variant)'),
|
|
('tiktok', 'DISABLE', 'paused', 'Campaign disabled by advertiser'),
|
|
('tiktok', 'CAMPAIGN_STATUS_DISABLE', 'paused', 'Campaign disabled (prefixed variant)'),
|
|
('tiktok', 'DELETE', 'cancelled', 'Campaign deleted'),
|
|
('tiktok', 'CAMPAIGN_STATUS_DELETE', 'cancelled', 'Campaign deleted (prefixed variant)'),
|
|
('tiktok', 'BUDGET_EXCEED', 'paused', 'Budget limit exceeded'),
|
|
('tiktok', 'CAMPAIGN_STATUS_BUDGET_EXCEED', 'paused', 'Budget exceeded (prefixed variant)'),
|
|
('tiktok', 'ADVERTISER_AUDIT_DENY', 'error', 'Advertiser account audit denied'),
|
|
('tiktok', 'CAMPAIGN_STATUS_ADVERTISER_AUDIT_DENY','error', 'Audit denied (prefixed variant)'),
|
|
('tiktok', 'NOT_DELETE', 'active', 'Campaign exists and is not deleted'),
|
|
('tiktok', 'ADVERTISER_AUDIT', 'pending', 'Advertiser account under audit'),
|
|
('tiktok', 'CAMPAIGN_STATUS_ADVERTISER_AUDIT', 'pending', 'Under audit (prefixed variant)'),
|
|
('tiktok', 'REAUDIT', 'pending', 'Campaign under re-audit'),
|
|
('tiktok', 'ALL', 'active', 'TikTok ALL filter status (treat as active)')
|
|
) AS src (channelType, providerStatus, platformStatus, description)
|
|
ON tgt.psmChannelType = src.channelType AND tgt.psmProviderStatus = src.providerStatus
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (psmChannelType, psmProviderStatus, psmPlatformStatus, psmDescription)
|
|
VALUES (src.channelType, src.providerStatus, src.platformStatus, src.description);
|
|
GO
|
|
|
|
-- ============================================================
|
|
-- Seed: Common / Internal (platform-generated statuses)
|
|
-- ============================================================
|
|
MERGE dbo.tbProviderStatusMap AS tgt
|
|
USING (VALUES
|
|
('_common', 'submitted', 'active', 'Successfully dispatched to provider'),
|
|
('_common', 'pending_review', 'pending', 'Awaiting provider review'),
|
|
('_common', 'stub_provider', 'pending', 'Stub provider — no real dispatch yet'),
|
|
('_common', 'approved', 'active', 'Provider approved the campaign'),
|
|
('_common', 'rejected', 'error', 'Provider rejected the campaign'),
|
|
('_common', 'suspended', 'paused', 'Campaign suspended by provider'),
|
|
('_common', 'budget_depleted', 'paused', 'Budget fully consumed'),
|
|
('_common', 'expired', 'completed', 'Campaign reached its end date'),
|
|
('_common', 'archived', 'completed', 'Campaign archived'),
|
|
('_common', 'deleted', 'cancelled', 'Campaign deleted'),
|
|
('_common', 'in_process', 'pending', 'Campaign is being processed'),
|
|
('_common', 'in_review', 'pending', 'Campaign is under review'),
|
|
('_common', 'learning', 'active', 'Campaign in learning/optimization phase'),
|
|
('_common', 'limited', 'active', 'Campaign serving but limited (budget, targeting)')
|
|
) AS src (channelType, providerStatus, platformStatus, description)
|
|
ON tgt.psmChannelType = src.channelType AND tgt.psmProviderStatus = src.providerStatus
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (psmChannelType, psmProviderStatus, psmPlatformStatus, psmDescription)
|
|
VALUES (src.channelType, src.providerStatus, src.platformStatus, src.description);
|
|
GO
|
|
|
|
-- ============================================================
|
|
-- Stored Procedure: spProviderStatusMap
|
|
-- ============================================================
|
|
-- Actions: list, get, upsert, delete
|
|
-- Follows standard JSON request/response pattern.
|
|
-- ============================================================
|
|
CREATE OR ALTER PROCEDURE dbo.spProviderStatusMap
|
|
@Action VARCHAR(20),
|
|
@Rqst NVARCHAR(MAX) = '{}',
|
|
@Resp NVARCHAR(MAX) OUTPUT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- ── LIST ──
|
|
IF @Action = 'list'
|
|
BEGIN
|
|
DECLARE @filterChannel VARCHAR(50) = JSON_VALUE(@Rqst, '$.channelType');
|
|
|
|
SET @Resp = (
|
|
SELECT
|
|
psmId AS id,
|
|
psmChannelType AS channelType,
|
|
psmProviderStatus AS providerStatus,
|
|
psmPlatformStatus AS platformStatus,
|
|
psmDescription AS [description],
|
|
psmIsActive AS isActive
|
|
FROM dbo.tbProviderStatusMap
|
|
WHERE psmIsActive = 1
|
|
AND (@filterChannel IS NULL OR psmChannelType = @filterChannel)
|
|
ORDER BY psmChannelType, psmProviderStatus
|
|
FOR JSON PATH, ROOT('data')
|
|
);
|
|
|
|
IF @Resp IS NULL SET @Resp = '{"data":[]}';
|
|
SET @Resp = '{"ok":true,' + SUBSTRING(@Resp, 2, LEN(@Resp));
|
|
RETURN;
|
|
END
|
|
|
|
-- ── GET ──
|
|
IF @Action = 'get'
|
|
BEGIN
|
|
DECLARE @getId INT = JSON_VALUE(@Rqst, '$.id');
|
|
|
|
SET @Resp = (
|
|
SELECT
|
|
psmId AS id,
|
|
psmChannelType AS channelType,
|
|
psmProviderStatus AS providerStatus,
|
|
psmPlatformStatus AS platformStatus,
|
|
psmDescription AS [description],
|
|
psmIsActive AS isActive
|
|
FROM dbo.tbProviderStatusMap
|
|
WHERE psmId = @getId
|
|
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
|
|
);
|
|
|
|
IF @Resp IS NULL
|
|
BEGIN
|
|
SET @Resp = '{"ok":false,"error":"Mapping not found"}';
|
|
RETURN;
|
|
END
|
|
|
|
SET @Resp = '{"ok":true,"data":' + @Resp + '}';
|
|
RETURN;
|
|
END
|
|
|
|
-- ── UPSERT ──
|
|
IF @Action = 'upsert'
|
|
BEGIN
|
|
DECLARE @uChannelType VARCHAR(50) = JSON_VALUE(@Rqst, '$.channelType');
|
|
DECLARE @uProviderStatus VARCHAR(100) = JSON_VALUE(@Rqst, '$.providerStatus');
|
|
DECLARE @uPlatformStatus VARCHAR(20) = JSON_VALUE(@Rqst, '$.platformStatus');
|
|
DECLARE @uDescription NVARCHAR(200) = JSON_VALUE(@Rqst, '$.description');
|
|
|
|
IF @uChannelType IS NULL OR @uProviderStatus IS NULL OR @uPlatformStatus IS NULL
|
|
BEGIN
|
|
SET @Resp = '{"ok":false,"error":"channelType, providerStatus, and platformStatus are required"}';
|
|
RETURN;
|
|
END
|
|
|
|
IF @uPlatformStatus NOT IN ('draft','staged','pending','active','paused','completed','cancelled','error')
|
|
BEGIN
|
|
SET @Resp = '{"ok":false,"error":"Invalid platformStatus. Must be: draft, staged, pending, active, paused, completed, cancelled, error"}';
|
|
RETURN;
|
|
END
|
|
|
|
MERGE dbo.tbProviderStatusMap AS tgt
|
|
USING (SELECT @uChannelType, @uProviderStatus) AS src (ct, ps)
|
|
ON tgt.psmChannelType = src.ct AND tgt.psmProviderStatus = src.ps
|
|
WHEN MATCHED THEN
|
|
UPDATE SET
|
|
psmPlatformStatus = @uPlatformStatus,
|
|
psmDescription = COALESCE(@uDescription, psmDescription),
|
|
psmIsActive = 1,
|
|
psmUpdatedAt = SYSUTCDATETIME()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (psmChannelType, psmProviderStatus, psmPlatformStatus, psmDescription)
|
|
VALUES (@uChannelType, @uProviderStatus, @uPlatformStatus, @uDescription);
|
|
|
|
SET @Resp = '{"ok":true,"message":"Mapping saved"}';
|
|
RETURN;
|
|
END
|
|
|
|
-- ── DELETE (soft) ──
|
|
IF @Action = 'delete'
|
|
BEGIN
|
|
DECLARE @dId INT = JSON_VALUE(@Rqst, '$.id');
|
|
|
|
UPDATE dbo.tbProviderStatusMap
|
|
SET psmIsActive = 0, psmUpdatedAt = SYSUTCDATETIME()
|
|
WHERE psmId = @dId;
|
|
|
|
SET @Resp = '{"ok":true,"message":"Mapping deactivated"}';
|
|
RETURN;
|
|
END
|
|
|
|
SET @Resp = '{"ok":false,"error":"Unknown action: ' + ISNULL(@Action,'null') + '"}';
|
|
END
|
|
GO
|