-- =============================================================================
-- Migration: Certificate verification token/signatory + signing metadata
--
-- TTRAININGCERTIFICATE gets a VERIFICATIONTOKEN (opaque, high-entropy — never the raw
-- sequential id, so a QR code encoding a verify URL can't be brute-forced/enumerated)
-- and a SIGNATORYID (which MSIGNATORY row signs this certificate).
--
-- TTRAININGCERTIFICATESIGNATURE is a 1:1 child holding signing metadata only — the
-- actual PDF bytes go through the existing TATTACHMENT + IAttachmentUploadService
-- mechanism (already used by ClaimBLL/DXP), not a bespoke document table. TATTACHMENT
-- has no signing-specific columns (only CONTENTHASH), so this table is the small gap-filler.
--
-- SIGNATUREBASE64 holds a DETACHED signature (SHA256(pdf) signed via RSA), not an embedded
-- PDF/PAdES signature — iText7's embedded-signature route (PdfSigner) requires
-- itext7.bouncy-castle-adapter, which hard-conflicts with this codebase's existing
-- Portable.BouncyCastle dependency (confirmed via a real build failure). A generic PDF viewer
-- won't show this certificate as "signed"; only GB5's own VerifyCertificate path checks it.
-- =============================================================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TTRAININGCERTIFICATE' AND COLUMN_NAME = 'VERIFICATIONTOKEN')
BEGIN
    ALTER TABLE DBO.TTRAININGCERTIFICATE ADD VERIFICATIONTOKEN NVARCHAR(64) NULL;
    CREATE UNIQUE INDEX UQ_TTRAININGCERTIFICATE_VERIFICATIONTOKEN
        ON DBO.TTRAININGCERTIFICATE (VERIFICATIONTOKEN)
        WHERE VERIFICATIONTOKEN IS NOT NULL;
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TTRAININGCERTIFICATE' AND COLUMN_NAME = 'SIGNATORYID')
BEGIN
    -- FK intentionally omitted: -1 is the sentinel for "no signatory configured yet"
    -- (same convention as MTRAINERPROFILE.EMPLOYEEID/EXTERNALVENDORID) — MSIGNATORY
    -- lives in GB5Framework, a different schema-ownership boundary than TMS anyway.
    ALTER TABLE DBO.TTRAININGCERTIFICATE ADD SIGNATORYID INT NOT NULL CONSTRAINT DF_TTRAININGCERTIFICATE_SIGNATORYID DEFAULT -1;
END
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TTRAININGCERTIFICATESIGNATURE')
BEGIN

CREATE TABLE DBO.TTRAININGCERTIFICATESIGNATURE
(
    TRAININGCERTIFICATESIGNATUREID INT             IDENTITY(1,1) NOT NULL,
    TRAININGCERTIFICATEID           INT             NOT NULL,
    ATTACHMENTID                    INT             NOT NULL,
    PDFSHA256HASH                   NVARCHAR(64)    NOT NULL,
    SIGNATUREBASE64                 NVARCHAR(MAX)   NOT NULL,
    SIGNATUREALGORITHM              NVARCHAR(50)    NOT NULL CONSTRAINT DF_TTRAININGCERTIFICATESIGNATURE_ALGO DEFAULT 'RSA-SHA256',
    SIGNINGTHUMBPRINT                NVARCHAR(100)   NOT NULL,
    SIGNEDON                        DATETIME        NOT NULL CONSTRAINT DF_TTRAININGCERTIFICATESIGNATURE_SIGNEDON DEFAULT GETDATE(),
    TENANTID                        INT             NOT NULL CONSTRAINT DF_TTRAININGCERTIFICATESIGNATURE_TENANTID DEFAULT -1,

    CONSTRAINT PK_TTRAININGCERTIFICATESIGNATURE_ID PRIMARY KEY (TRAININGCERTIFICATESIGNATUREID),
    CONSTRAINT FK_TTRAININGCERTIFICATESIGNATURE_CERTID FOREIGN KEY (TRAININGCERTIFICATEID) REFERENCES DBO.TTRAININGCERTIFICATE(TRAININGCERTIFICATEID),
    CONSTRAINT UQ_TTRAININGCERTIFICATESIGNATURE_CERTID UNIQUE (TRAININGCERTIFICATEID)
);

END
GO