-- Stage 5: Partner API Key & Webhook Tables
-- TPARTNERAPIKEY — M2M long-lived credentials for headless ERP integrations
-- TPARTNERWEBHOOK — Partner callback registration for Dapr business events
--
-- Run on the central platform DB.
-- These tables are referenced by roadmap endpoints:
--   POST /Partner/SavePartnerApiKey
--   POST /Partner/SavePartnerWebhook

-- ─── 1. TPARTNERAPIKEY ────────────────────────────────────────────────────────
-- Stores hashed API keys for server-to-server (M2M) authentication.
-- HashedKey stores SHA-256(key) — the raw key is shown to the partner only once at creation.
-- Scopes is a comma-separated list of allowed endpoint prefixes (e.g. "Accounts,Purchase").
-- RateLimitPerMinute = 0 means no limit (inherits platform default).
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TPARTNERAPIKEY')
BEGIN
    CREATE TABLE TPARTNERAPIKEY (
        APIKEYID            INT            NOT NULL IDENTITY(1,1) CONSTRAINT PK_TPARTNERAPIKEY PRIMARY KEY,
        PARTNERPRODUCTID    INT            NOT NULL,
        KEYNAME             NVARCHAR(100)  NOT NULL,
        HASHEDKEY           NVARCHAR(64)   NOT NULL,   -- SHA-256 hex of the raw key
        KEYHINT             NVARCHAR(8)    NOT NULL,   -- last 4 chars of raw key shown in UI
        SCOPES              NVARCHAR(2000) NULL,        -- NULL = all scopes allowed
        RATELIMITPERMINUTE  INT            NOT NULL CONSTRAINT DF_TPARTNERAPIKEY_RATE    DEFAULT 0,
        EXPIRESON           DATETIME       NULL,        -- NULL = never expires
        LASTUSEDUTC         DATETIME       NULL,
        STATUS              TINYINT        NOT NULL CONSTRAINT DF_TPARTNERAPIKEY_STATUS  DEFAULT 1,
        VERSION             SMALLINT       NOT NULL CONSTRAINT DF_TPARTNERAPIKEY_VER     DEFAULT 0,
        SORTORDER           SMALLINT       NOT NULL CONSTRAINT DF_TPARTNERAPIKEY_SORT    DEFAULT 9999,
        SOURCETYPE          TINYINT        NOT NULL CONSTRAINT DF_TPARTNERAPIKEY_SRC     DEFAULT 1,
        CREATEDBYID         INT            NULL,
        CREATEDON           DATETIME       NULL,
        MODIFIEDBYID        INT            NULL,
        MODIFIEDON          DATETIME       NULL,
        CONSTRAINT UQ_TPARTNERAPIKEY_HASH UNIQUE (HASHEDKEY),
        CONSTRAINT FK_TPARTNERAPIKEY_PRODUCT FOREIGN KEY (PARTNERPRODUCTID)
            REFERENCES TPARTNERPRODUCT (PARTNERPRODUCTID)
    );

    -- Fast lookup by hashed key on every authenticated request
    CREATE UNIQUE INDEX IX_TPARTNERAPIKEY_HASH
        ON TPARTNERAPIKEY (HASHEDKEY)
        WHERE STATUS = 1;

    -- List keys by partner product (admin UI, key management)
    CREATE INDEX IX_TPARTNERAPIKEY_PRODUCT
        ON TPARTNERAPIKEY (PARTNERPRODUCTID)
        INCLUDE (KEYNAME, KEYHINT, SCOPES, EXPIRESON, STATUS, LASTUSEDUTC);
END;
GO

-- ─── 2. TPARTNERWEBHOOK ───────────────────────────────────────────────────────
-- Partners register callback URLs for specific Dapr business event types.
-- When GB5 publishes EventTypeId N, the webhook dispatcher POSTs to CallbackUrl
-- with an HMAC-SHA256 signature header using Secret.
-- MaxRetries controls retry depth; RetryIntervalSeconds is the base backoff.
-- LastDeliveryStatus records the HTTP response code from the last delivery attempt.
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TPARTNERWEBHOOK')
BEGIN
    CREATE TABLE TPARTNERWEBHOOK (
        WEBHOOKID               INT            NOT NULL IDENTITY(1,1) CONSTRAINT PK_TPARTNERWEBHOOK PRIMARY KEY,
        PARTNERPRODUCTID        INT            NOT NULL,
        EVENTTYPEID             INT            NOT NULL,   -- references MEVENTTYPE.EVENTTYPEID
        CALLBACKURL             NVARCHAR(500)  NOT NULL,
        SECRET                  NVARCHAR(128)  NOT NULL,   -- HMAC signing secret (stored encrypted)
        DESCRIPTION             NVARCHAR(200)  NULL,
        MAXRETRIES              TINYINT        NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_RETRY   DEFAULT 3,
        RETRYINTERVALSECONDS    INT            NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_INTV    DEFAULT 60,
        LASTDELIVERYSTATUS      SMALLINT       NULL,        -- HTTP response code of last attempt
        LASTDELIVEREDUTC        DATETIME       NULL,
        STATUS                  TINYINT        NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_STATUS  DEFAULT 1,
        VERSION                 SMALLINT       NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_VER     DEFAULT 0,
        SORTORDER               SMALLINT       NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_SORT    DEFAULT 9999,
        SOURCETYPE              TINYINT        NOT NULL CONSTRAINT DF_TPARTNERWEBHOOK_SRC     DEFAULT 1,
        CREATEDBYID             INT            NULL,
        CREATEDON               DATETIME       NULL,
        MODIFIEDBYID            INT            NULL,
        MODIFIEDON              DATETIME       NULL,
        CONSTRAINT UQ_TPARTNERWEBHOOK UNIQUE (PARTNERPRODUCTID, EVENTTYPEID, CALLBACKURL),
        CONSTRAINT FK_TPARTNERWEBHOOK_PRODUCT FOREIGN KEY (PARTNERPRODUCTID)
            REFERENCES TPARTNERPRODUCT (PARTNERPRODUCTID)
    );

    -- Dispatch lookup: given an event type, find all active webhook targets
    CREATE INDEX IX_TPARTNERWEBHOOK_EVENTTYPE
        ON TPARTNERWEBHOOK (EVENTTYPEID, STATUS)
        INCLUDE (PARTNERPRODUCTID, CALLBACKURL, SECRET, MAXRETRIES, RETRYINTERVALSECONDS);

    -- Admin list by partner product
    CREATE INDEX IX_TPARTNERWEBHOOK_PRODUCT
        ON TPARTNERWEBHOOK (PARTNERPRODUCTID)
        INCLUDE (EVENTTYPEID, CALLBACKURL, STATUS, LASTDELIVERYSTATUS, LASTDELIVEREDUTC);
END;
GO