namespace TMSDAL.Query.TrainingDomain
{
    /// <summary>
    /// Required indexes:
    ///   IX_MTRAININGDOMAIN_TENANTID on (TENANTID)
    ///   IX_MTRAININGDOMAIN_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    /// </summary>
    public static class TrainingDomainQB
    {
        public const string GET_DOMAIN = @"
            SELECT  
                    td.TRAININGDOMAINID      AS TrainingDomainId,
                    td.DOMAINCODE            AS DomainCode,
                    td.DOMAINNAME            AS DomainName,
                    td.DOMAINDESCRIPTION     AS Description,
                    td.ISACTIVE              AS IsActive,
                    td.TENANTID              AS TenantId,
                    td.CREATEDBYID           AS CreatedById,
                    td.CREATEDON             AS CreatedOn,
                    td.MODIFIEDBYID          AS ModifiedById,
                    td.MODIFIEDON            AS ModifiedOn
            FROM    DBO.MTRAININGDOMAIN td
            WHERE   td.TRAININGDOMAINID = @TrainingDomainId
            AND     td.TENANTID = @TenantId";

        public const string SAVE_DOMAIN = @"
            INSERT INTO DBO.MTRAININGDOMAIN
                (
                    TRAININGDOMAINID,
                    DOMAINCODE,
                    DOMAINNAME,
                    DOMAINDESCRIPTION,
                    ISACTIVE,
                    TENANTID,
                    CREATEDBYID,
                    CREATEDON,
                    MODIFIEDBYID,
                    MODIFIEDON
                )
            VALUES
                (
                    @TrainingDomainId,
                    @DomainCode,
                    @DomainName,
                    @Description,
                    @IsActive,
                    @TenantId,
                    @CreatedById,
                    @CreatedOn,
                    @ModifiedById,
                    @ModifiedOn
                )";

        public const string UPDATE_DOMAIN = @"
            UPDATE  DBO.MTRAININGDOMAIN
            SET     DOMAINCODE = @DomainCode,
                    DOMAINNAME = @DomainName,
                    DOMAINDESCRIPTION = @Description,
                    ISACTIVE = @IsActive,
                    MODIFIEDBYID = @ModifiedById,
                    MODIFIEDON = @ModifiedOn
            WHERE   TRAININGDOMAINID = @TrainingDomainId
            AND     TENANTID = @TenantId";

        public const string CHECK_DOMAIN_PROGRAMMES = @"
            SELECT PROGRAMMECODE + ' - ' + PROGRAMMETITLE AS RefName
            FROM DBO.MTRAININGPROGRAMME
            WHERE TRAININGDOMAINID = @TrainingDomainId AND TENANTID = @TenantId";

        public const string CHECK_DOMAIN_NEEDS = @"
            SELECT NEEDCODE + ' - ' + NEEDTITLE AS RefName
            FROM DBO.TTRAININGNEED
            WHERE TRAININGDOMAINID = @TrainingDomainId AND TENANTID = @TenantId";

        public const string DELETE_DOMAIN = @"
            DELETE FROM DBO.MTRAININGDOMAIN
            WHERE   TRAININGDOMAINID = @TrainingDomainId
            AND     TENANTID = @TenantId";

        // Used by ITrainingNeedBLL.ProposeTrainingNeed when an auto-raising producer (e.g.
        // SkillGapLink, or a future PMS/OKR/Quality producer) has no natural Training Domain to
        // supply — TRAININGDOMAINID is NOT NULL with a hard FK to MTRAININGDOMAIN, so there is no
        // safe universal sentinel; the tenant must have configured at least one domain.
        public const string GET_DEFAULT_DOMAIN = @"
            SELECT TOP 1 TRAININGDOMAINID
            FROM DBO.MTRAININGDOMAIN
            WHERE TENANTID = @TenantId
            ORDER BY TRAININGDOMAINID";

        public const string GET_SELECTLIST_DOMAIN = @"
            SELECT  
                    td.TRAININGDOMAINID      AS TrainingDomainId,
                    td.DOMAINCODE            AS DomainCode,
                    td.DOMAINNAME            AS DomainName
            FROM    DBO.MTRAININGDOMAIN td
            WHERE   td.ISACTIVE = 0
            AND     td.TENANTID = @TenantId
            ORDER BY td.DOMAINNAME";

        public const string GET_DOMAIN_LIST = @"
            SELECT  
                    td.TRAININGDOMAINID      AS TrainingDomainId,
                    td.DOMAINCODE            AS DomainCode,
                    td.DOMAINNAME            AS DomainName,
                    td.DOMAINDESCRIPTION     AS Description,
                    td.ISACTIVE              AS IsActive,
                    td.TENANTID              AS TenantId,
                    td.CREATEDBYID           AS CreatedById,
                    td.CREATEDON             AS CreatedOn,
                    td.MODIFIEDBYID          AS ModifiedById,
                    td.MODIFIEDON            AS ModifiedOn,
                    tn.TRAININGNEEDID        AS TrainingNeedId,
                    tn.NEEDCODE              AS NeedCode,
                    tn.NEEDTITLE             AS NeedTitle,
                    tn.DESCRIPTION           AS NeedDescription,
                    tn.TARGETHEADCOUNT       AS TargetHeadCount
            FROM    DBO.MTRAININGDOMAIN td
            LEFT JOIN TTRAININGNEED TN ON TN.TRAININGDOMAINID = td.TRAININGDOMAINID
            WHERE   td.TENANTID = @TenantId
            ORDER BY td.DOMAINNAME";
    }
}