namespace TMSDAL.Query.TrainerProfile
{
    /// <summary>
    /// Required Indexes:
    ///     IX_MTRAINERPROFILE_TENANTID
    ///     IX_MTRAINERPROFILE_ISACTIVE_TENANTID
    /// </summary>
    public static class TrainerProfileQB
    {
        public const string GET_TRAINER = @"
SELECT
    tp.TRAINERID                 AS TrainerId,
    tp.TRAINERCODE               AS TrainerCode,
    tp.TRAINERNAME               AS TrainerName,
    tp.TRAINERFULLNAME           AS TrainerFullName,
    tp.TRAINERTYPE               AS TrainerType,
    tp.EMPLOYEEID                AS EmployeeId,
    tp.EXTERNALVENDORID          AS ExternalVendorId,

    EV.VENDORNAME                AS VendorName,
    EV.VENDORCODE                AS VendorCode,

    tp.SPECIALISATIONS           AS Specialisations,
    tp.QUALIFIEDPROGRAMMEIDS     AS QualifiedProgrammeIds,

    Prog.ProgrammeCodes          AS ProgrammeCode,
    Prog.ProgrammeTitles         AS ProgrammeTitle,

    tp.MAXSESSIONSPERMONTH       AS MaxSessionsPerMonth,
    tp.EMAIL                     AS Email,
    tp.PHONE                     AS Phone,
    tp.ISACTIVE                  AS IsActive,
    tp.REMARKS                   AS Remarks,
    tp.VERSION                   AS Version,
    tp.STATUS                    AS Status,
    tp.SORTORDER                 AS SortOrder,
    tp.CREATEDBYID               AS CreatedById,
    tp.CREATEDON                 AS CreatedOn,
    tp.MODIFIEDBYID              AS ModifiedById,
    tp.MODIFIEDON                AS ModifiedOn,
    tp.SOURCETYPE                AS SourceType,
    tp.TENANTID                  AS TenantId,

    tp.CONTACTID                 AS ContactId,
    c.NAME                       AS ContactName

FROM DBO.MTRAINERPROFILE tp

LEFT JOIN MEXTERNALVENDOR EV
    ON EV.VENDORID = tp.EXTERNALVENDORID

LEFT JOIN DBO.MCONTACT c
    ON c.CONTACTID = tp.CONTACTID

OUTER APPLY
(
    SELECT
        STUFF((
            SELECT ', ' + TE.PROGRAMMECODE
            FROM (
                SELECT TRY_CAST(T.N.value('.', 'NVARCHAR(50)') AS INT) AS ProgrammeId
                FROM (
                    SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(tp.QUALIFIEDPROGRAMMEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                    WHERE tp.QUALIFIEDPROGRAMMEIDS IS NOT NULL AND tp.QUALIFIEDPROGRAMMEIDS NOT IN ('[]','null','')
                ) XmlData
                CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
            ) Ids
            LEFT JOIN MTRAININGPROGRAMME TE ON TE.PROGRAMMEID = Ids.ProgrammeId
            WHERE TE.PROGRAMMECODE IS NOT NULL
            FOR XML PATH('')
        ), 1, 2, '') AS ProgrammeCodes,
        STUFF((
            SELECT ', ' + TE.PROGRAMMETITLE
            FROM (
                SELECT TRY_CAST(T.N.value('.', 'NVARCHAR(50)') AS INT) AS ProgrammeId
                FROM (
                    SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(tp.QUALIFIEDPROGRAMMEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                    WHERE tp.QUALIFIEDPROGRAMMEIDS IS NOT NULL AND tp.QUALIFIEDPROGRAMMEIDS NOT IN ('[]','null','')
                ) XmlData
                CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
            ) Ids
            LEFT JOIN MTRAININGPROGRAMME TE ON TE.PROGRAMMEID = Ids.ProgrammeId
            WHERE TE.PROGRAMMETITLE IS NOT NULL
            FOR XML PATH('')
        ), 1, 2, '') AS ProgrammeTitles
) Prog

WHERE tp.TRAINERID = @TrainerId
  AND tp.TENANTID  = @TenantId;";

        public const string SAVE_TRAINER = @"
    INSERT INTO DBO.MTRAINERPROFILE
    (
        TRAINERID,
        TRAINERCODE,
        TRAINERNAME,
        TRAINERFULLNAME,
        TRAINERTYPE,
        EMPLOYEEID,
        EXTERNALVENDORID,
        SPECIALISATIONS,
        QUALIFIEDPROGRAMMEIDS,
        MAXSESSIONSPERMONTH,
        EMAIL,
        PHONE,
        ISACTIVE,
        REMARKS,
        VERSION,
        STATUS,
        SORTORDER,
        CREATEDBYID,
        CREATEDON,
        MODIFIEDBYID,
        MODIFIEDON,
        CONTACTID,
        SOURCETYPE,
        TENANTID
    )
    VALUES
    (
        @TrainerId,
        @TrainerCode,
        @TrainerName,
        @TrainerFullName,
        @TrainerType,
        @EmployeeId,        -- -1 when TrainerType = 1 (External)
        @ExternalVendorId,  -- -1 when TrainerType = 0 (Internal)
        @Specialisations,
        @QualifiedProgrammeIds,
        @MaxSessionsPerMonth,
        @Email,
        @Phone,
        @IsActive,
        @Remarks,
        0,                  -- VERSION always starts at 0 on insert
        @Status,
        @SortOrder,
        @CreatedById,
        @CreatedOn,
        @ModifiedById,
        @ModifiedOn,
        @ContactId,
        5,                  -- SOURCETYPE always 5 per table default
        @TenantId
    )";

        public const string UPDATE_TRAINER = @"
    UPDATE DBO.MTRAINERPROFILE
    SET
        TRAINERCODE             = @TrainerCode,
        TRAINERNAME             = @TrainerName,
        TRAINERFULLNAME         = @TrainerFullName,
        TRAINERTYPE             = @TrainerType,
        EMPLOYEEID              = @EmployeeId,        -- -1 when TrainerType = 1 (External)
        EXTERNALVENDORID        = @ExternalVendorId,  -- -1 when TrainerType = 0 (Internal)
        SPECIALISATIONS         = @Specialisations,
        QUALIFIEDPROGRAMMEIDS   = @QualifiedProgrammeIds,
        MAXSESSIONSPERMONTH     = @MaxSessionsPerMonth,
        EMAIL                   = @Email,
        PHONE                   = @Phone,
        ISACTIVE                = @IsActive,
        REMARKS                 = @Remarks,
        STATUS                  = @Status,
        SORTORDER               = @SortOrder,
        CONTACTID               = @ContactId,
        VERSION                 = VERSION + 1,        -- FIX: increment optimistic concurrency version
        MODIFIEDBYID            = @ModifiedById,
        MODIFIEDON              = @ModifiedOn
    WHERE TRAINERID = @TrainerId
      AND TENANTID  = @TenantId";

        public const string DELETE_TRAINER = @"
DELETE FROM DBO.MTRAINERPROFILE
WHERE TRAINERID = @TrainerId
  AND TENANTID  = @TenantId;";

        public const string GET_SELECTLIST_TRAINER = @"
SELECT
    TRAINERID         AS TrainerId,
    TRAINERCODE       AS TrainerCode,
    TRAINERNAME       AS TrainerName,
    TRAINERFULLNAME   AS TrainerFullName
FROM DBO.MTRAINERPROFILE
WHERE ISACTIVE = 0
  AND TENANTID = @TenantId
ORDER BY TRAINERFULLNAME;";

        // Resolves the logged-in employee's own TrainerId for self-service screens (Trainer Portal) --
        // MTRAINERPROFILE.TRAINERID is the ID every TrainerDashboard/TrainerPortal query keys off of,
        // NOT the same value space as MEMPLOYEE.EMPLOYEEID, so this lookup is required rather than
        // passing login.UserId straight through.
        public const string GET_TRAINERID_BY_EMPLOYEEID = @"
SELECT TOP 1 TRAINERID
FROM DBO.MTRAINERPROFILE
WHERE EMPLOYEEID = @EmployeeId
  AND ISACTIVE   = 0
  AND TENANTID   = @TenantId;";

        public const string GET_TRAINER_LIST = @"
SELECT
    tp.TRAINERID                 AS TrainerId,
    tp.TRAINERCODE               AS TrainerCode,
    tp.TRAINERNAME               AS TrainerName,
    tp.TRAINERFULLNAME           AS TrainerFullName,
    tp.TRAINERTYPE               AS TrainerType,
    tp.EMPLOYEEID                AS EmployeeId,
    tp.EXTERNALVENDORID          AS ExternalVendorId,

    EV.VENDORNAME                AS VendorName,
    EV.VENDORCODE                AS VendorCode,

    tp.SPECIALISATIONS           AS Specialisations,
    tp.QUALIFIEDPROGRAMMEIDS     AS QualifiedProgrammeIds,

    Prog.ProgrammeCodes          AS ProgrammeCode,
    Prog.ProgrammeTitles         AS ProgrammeTitle,

    tp.MAXSESSIONSPERMONTH       AS MaxSessionsPerMonth,
    tp.EMAIL                     AS Email,
    tp.PHONE                     AS Phone,
    tp.ISACTIVE                  AS IsActive,
    tp.REMARKS                   AS Remarks,
    tp.VERSION                   AS Version,
    tp.STATUS                    AS Status,
    tp.SORTORDER                 AS SortOrder,
    tp.CREATEDBYID               AS CreatedById,
    tp.CREATEDON                 AS CreatedOn,
    tp.MODIFIEDBYID              AS ModifiedById,
    tp.MODIFIEDON                AS ModifiedOn,
    tp.SOURCETYPE                AS SourceType,
    tp.TENANTID                  AS TenantId,

    tp.CONTACTID                 AS ContactId,
    c.NAME                       AS ContactName

FROM DBO.MTRAINERPROFILE tp

LEFT JOIN MEXTERNALVENDOR EV
    ON EV.VENDORID = tp.EXTERNALVENDORID

LEFT JOIN DBO.MCONTACT c
    ON c.CONTACTID = tp.CONTACTID

OUTER APPLY
(
    SELECT
        STUFF((
            SELECT ', ' + TE.PROGRAMMECODE
            FROM (
                SELECT TRY_CAST(T.N.value('.', 'NVARCHAR(50)') AS INT) AS ProgrammeId
                FROM (
                    SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(tp.QUALIFIEDPROGRAMMEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                    WHERE tp.QUALIFIEDPROGRAMMEIDS IS NOT NULL AND tp.QUALIFIEDPROGRAMMEIDS NOT IN ('[]','null','')
                ) XmlData
                CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
            ) Ids
            LEFT JOIN MTRAININGPROGRAMME TE ON TE.PROGRAMMEID = Ids.ProgrammeId
            WHERE TE.PROGRAMMECODE IS NOT NULL
            FOR XML PATH('')
        ), 1, 2, '') AS ProgrammeCodes,
        STUFF((
            SELECT ', ' + TE.PROGRAMMETITLE
            FROM (
                SELECT TRY_CAST(T.N.value('.', 'NVARCHAR(50)') AS INT) AS ProgrammeId
                FROM (
                    SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(tp.QUALIFIEDPROGRAMMEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                    WHERE tp.QUALIFIEDPROGRAMMEIDS IS NOT NULL AND tp.QUALIFIEDPROGRAMMEIDS NOT IN ('[]','null','')
                ) XmlData
                CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
            ) Ids
            LEFT JOIN MTRAININGPROGRAMME TE ON TE.PROGRAMMEID = Ids.ProgrammeId
            WHERE TE.PROGRAMMETITLE IS NOT NULL
            FOR XML PATH('')
        ), 1, 2, '') AS ProgrammeTitles
) Prog

WHERE tp.TENANTID  = @TenantId;";

        public const string CHECK_DUPLICATE_TRAINER_CODE = @"
SELECT COUNT(1)
FROM DBO.MTRAINERPROFILE
WHERE TRAINERCODE = @TrainerCode
  AND TENANTID = @TenantId
  AND TRAINERID <> @TrainerId;";
    }
}