using System.Globalization;

namespace TMSDAL.Query.TrainingEnrollment
{
    /// <summary>
    /// Query bank for TTRAININGENROLLMENT.
    ///
    /// Required indexes:
    ///   IX_TTRAININGENROLLMENT_INSTANCEID_TENANTID
    ///   IX_TTRAININGENROLLMENT_EMPLOYEEID_TENANTID
    ///   IX_TTRAININGENROLLMENT_STATUS_TENANTID
    /// </summary>
    public static class TrainingEnrollmentQB
    {
        // ════════════════════════════════════════════════════════════════════
        // READ — Single Record
        // ════════════════════════════════════════════════════════════════════

        /// <summary>Fetch full enrollment record by primary key.</summary>
        public const string GET_ENROLLMENT = @"
SELECT
    te.ENROLLMENTID         AS EnrollmentId,
    te.ENROLLMENTCODE       AS EnrollmentCode,
    te.ENROLLMENTNAME       AS EnrollmentName,
    te.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    tp.PROGRAMMETITLE       AS ProgrammeTitle,
    te.EMPLOYEEID           AS EmployeeId,
    e.EMPLOYEENAME          AS EmployeeName,
    te.NOMINATIONSOURCE     AS NominationSource,
    te.NOMINATEDBYID        AS NominatedById,
    nb.EMPLOYEENAME         AS NominatedByName,     -- FIX: join to get name
    te.NOMINATEDON          AS NominatedOn,         -- FIX: was missing
    te.MANAGERAPPROVALID    AS ManagerApprovalId,
    te.MANAGERAPPROVEDON    AS ManagerApprovedOn,
    te.HRAPPROVALID         AS HRApprovalId,
    te.ENROLLMENTSTATUS     AS EnrollmentStatus,
    te.WAITLISTPOSITION     AS WaitlistPosition,
    te.PREASSESSMENTDONE    AS PreAssessmentDone,   -- FIX: was missing
    te.PREASSESSMENTSCORE   AS PreAssessmentScore,  -- FIX: was missing
    te.REMARKS              AS Remarks,             -- FIX: was missing
    te.WITHDRAWALREASON     AS WithdrawalReason,
    te.WITHDRAWNON          AS WithdrawnOn,
    te.TENANTID             AS TenantId,
    te.CREATEDBYID          AS CreatedById,
    te.CREATEDON            AS CreatedOn,
    te.MODIFIEDBYID         AS ModifiedById,
    te.MODIFIEDON           AS ModifiedOn
FROM DBO.TTRAININGENROLLMENT te
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON  ti.INSTANCEID = te.INSTANCEID
    AND ti.TENANTID   = te.TENANTID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = ti.PROGRAMMEID
    AND tp.TENANTID    = te.TENANTID
INNER JOIN DBO.MEMPLOYEE e
    ON  e.EMPLOYEEID = te.EMPLOYEEID
-- FIX: LEFT JOIN for NominatedBy — may be NULL when system-nominated
LEFT JOIN DBO.MEMPLOYEE nb
    ON  nb.EMPLOYEEID = te.NOMINATEDBYID
WHERE te.ENROLLMENTID = @EnrollmentId
  AND te.TENANTID     = @TenantId;";

        // ────────────────────────────────────────────────────────────────────
        /// <summary>
        /// NEW: Duplicate-enrollment guard — fetches ACTIVE enrollment only.
        /// Matches the filtered unique index: excludes Withdrawn(7) and
        /// all Rejected variants (8, 9, 10) so re-enrollment is allowed
        /// after those terminal states.
        /// </summary>
        public const string GET_ENROLLMENT_BY_INSTANCE_EMPLOYEE = @"
SELECT TOP 1
    te.ENROLLMENTID     AS EnrollmentId,
    te.ENROLLMENTCODE   AS EnrollmentCode,
    te.INSTANCEID       AS InstanceId,
    te.EMPLOYEEID       AS EmployeeId,
    te.ENROLLMENTSTATUS AS EnrollmentStatus
FROM DBO.TTRAININGENROLLMENT te
WHERE te.INSTANCEID       = @InstanceId
  AND te.EMPLOYEEID       = @EmployeeId
  AND te.TENANTID         = @TenantId
  AND te.ENROLLMENTSTATUS NOT IN (7, 8, 9, 10);
  -- Excludes: 7=Withdrawn  8=Rejected(legacy)  9=ManagerRejected  10=HRRejected";

        // ════════════════════════════════════════════════════════════════════
        // READ — Lists
        // ════════════════════════════════════════════════════════════════════

        /// <summary>All active enrollments for a training instance.</summary>
        public const string GET_ENROLLMENTS_BY_INSTANCE = @"
SELECT
    te.ENROLLMENTID         AS EnrollmentId,
    te.ENROLLMENTCODE       AS EnrollmentCode,
    te.ENROLLMENTNAME       AS EnrollmentName,
    te.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    tp.PROGRAMMETITLE       AS ProgrammeTitle,
    te.EMPLOYEEID           AS EmployeeId,
    e.EMPLOYEENAME          AS EmployeeName,
    te.NOMINATIONSOURCE     AS NominationSource,
    te.ENROLLMENTSTATUS     AS EnrollmentStatus,
    te.WAITLISTPOSITION     AS WaitlistPosition,
    te.MANAGERAPPROVEDON    AS ManagerApprovedOn,
    te.CREATEDON            AS CreatedOn
FROM DBO.TTRAININGENROLLMENT te
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON  ti.INSTANCEID = te.INSTANCEID
    AND ti.TENANTID   = te.TENANTID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = ti.PROGRAMMEID
    AND tp.TENANTID    = te.TENANTID
INNER JOIN DBO.MEMPLOYEE e
    ON  e.EMPLOYEEID = te.EMPLOYEEID
WHERE te.INSTANCEID = @InstanceId
  AND te.TENANTID   = @TenantId
ORDER BY te.WAITLISTPOSITION, te.CREATEDON;";

        /// <summary>All enrollments for an employee, newest first.</summary>
        public const string GET_ENROLLMENTS_BY_EMPLOYEE = @"
SELECT
    te.ENROLLMENTID         AS EnrollmentId,
    te.ENROLLMENTCODE       AS EnrollmentCode,
    te.ENROLLMENTNAME       AS EnrollmentName,
    te.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    tp.PROGRAMMETITLE       AS ProgrammeTitle,
    te.EMPLOYEEID           AS EmployeeId,
    e.EMPLOYEENAME          AS EmployeeName,
    te.NOMINATIONSOURCE     AS NominationSource,
    te.ENROLLMENTSTATUS     AS EnrollmentStatus,
    te.WAITLISTPOSITION     AS WaitlistPosition,
    te.MANAGERAPPROVEDON    AS ManagerApprovedOn,
    te.CREATEDON            AS CreatedOn
FROM DBO.TTRAININGENROLLMENT te
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON  ti.INSTANCEID = te.INSTANCEID
    AND ti.TENANTID   = te.TENANTID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = ti.PROGRAMMEID
    AND tp.TENANTID    = te.TENANTID
INNER JOIN DBO.MEMPLOYEE e
    ON  e.EMPLOYEEID = te.EMPLOYEEID
WHERE te.EMPLOYEEID = @EmployeeId
  AND te.TENANTID   = @TenantId
ORDER BY te.CREATEDON DESC;";

        /// <summary>Full enrollment list for the tenant.</summary>
        public const string GET_ENROLLMENT_LIST = @"
SELECT
    te.ENROLLMENTID         AS EnrollmentId,
    te.ENROLLMENTCODE       AS EnrollmentCode,
    te.ENROLLMENTNAME       AS EnrollmentName,
    te.INSTANCEID           AS InstanceId,
    ti.INSTANCECODE         AS InstanceCode,
    tp.PROGRAMMETITLE       AS ProgrammeTitle,
    te.EMPLOYEEID           AS EmployeeId,
    e.EMPLOYEENAME          AS EmployeeName,
    te.NOMINATIONSOURCE     AS NominationSource,
    te.ENROLLMENTSTATUS     AS EnrollmentStatus,
    te.WAITLISTPOSITION     AS WaitlistPosition,
    te.MANAGERAPPROVEDON    AS ManagerApprovedOn,
    te.CREATEDON            AS CreatedOn
FROM DBO.TTRAININGENROLLMENT te
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON  ti.INSTANCEID = te.INSTANCEID
    AND ti.TENANTID   = te.TENANTID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = ti.PROGRAMMEID
    AND tp.TENANTID    = te.TENANTID
INNER JOIN DBO.MEMPLOYEE e
    ON  e.EMPLOYEEID = te.EMPLOYEEID
WHERE te.TENANTID = @TenantId
ORDER BY te.CREATEDON DESC;";

        // ════════════════════════════════════════════════════════════════════
        // READ — Waitlist / Picklist
        // ════════════════════════════════════════════════════════════════════

        /// <summary>
        /// Returns MAX(WaitlistPosition) + 1 for the given instance.
        /// Returns 1 when no waitlisted rows exist (ISNULL handles the NULL).
        /// </summary>
        public const string GET_NEXT_WAITLIST_POSITION = @"
SELECT ISNULL(MAX(WAITLISTPOSITION), 0) + 1
FROM DBO.TTRAININGENROLLMENT
WHERE INSTANCEID       = @InstanceId
  AND ENROLLMENTSTATUS = @WaitlistedStatus
  AND TENANTID         = @TenantId;";

        /// <summary>Dropdown / picklist — minimal fields with meaningful labels.</summary>
        public const string GET_SELECTLIST_TRAINING_ENROLLMENT = @"
SELECT
    te.ENROLLMENTID     AS EnrollmentId,
    te.ENROLLMENTCODE   AS EnrollmentCode,
    te.ENROLLMENTNAME   AS EnrollmentName,
    te.EMPLOYEEID       AS EmployeeId,
    ti.INSTANCECODE     AS InstanceCode,
    tp.PROGRAMMETITLE   AS ProgrammeTitle
FROM DBO.TTRAININGENROLLMENT te
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON  ti.INSTANCEID = te.INSTANCEID
    AND ti.TENANTID   = te.TENANTID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = ti.PROGRAMMEID
    AND tp.TENANTID    = te.TENANTID
WHERE te.TENANTID = @TenantId
{DYNAMIC_WHERE}";

        // ════════════════════════════════════════════════════════════════════
        // WRITE
        // ════════════════════════════════════════════════════════════════════

        /// <summary>
        /// Insert a new enrollment record.
        /// FIX: NOMINATEDON now uses @NominatedOn (not @CreatedOn).
        /// FIX: WAITLISTPOSITION uses NULL for non-waitlisted (not 0).
        /// </summary>
        public const string SAVE_ENROLLMENT = @"
INSERT INTO DBO.TTRAININGENROLLMENT
(
    ENROLLMENTID,
    ENROLLMENTCODE,
    ENROLLMENTNAME,
    INSTANCEID,
    EMPLOYEEID,
    NOMINATIONSOURCE,
    NOMINATEDBYID,
    NOMINATEDON,
    ENROLLMENTSTATUS,
    WAITLISTPOSITION,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @EnrollmentId,
    @EnrollmentCode,
    @EnrollmentName,
    @InstanceId,
    @EmployeeId,
    @NominationSource,
    @NominatedById,
    @NominatedOn,       -- FIX: was @CreatedOn — wrong column re-used
    @EnrollmentStatus,
    @WaitlistPosition,  -- NULL when not waitlisted (BLL sets null, not 0)
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
);";

        /// <summary>
        /// Manager approval / rejection.
        /// FIX: Writes to MANAGERREMARKS (tier-specific) not shared REMARKS column.
        /// </summary>
        public const string UPDATE_MANAGER_APPROVAL = @"
UPDATE DBO.TTRAININGENROLLMENT
SET
    ENROLLMENTSTATUS  = @EnrollmentStatus,
    MANAGERAPPROVALID = @ManagerApprovalId,
    MANAGERAPPROVEDON = @ManagerApprovedOn,
    MANAGERREMARKS    = @ManagerRemarks,    -- FIX: tier-specific column
    MODIFIEDBYID      = @ModifiedById,
    MODIFIEDON        = @ModifiedOn
WHERE ENROLLMENTID = @EnrollmentId
  AND TENANTID     = @TenantId;";

        /// <summary>
        /// HR approval / rejection.
        /// FIX: HRAPPROVEDON replaces HRPROCESSEDON (renamed column).
        /// FIX: Writes to HRREMARKS (tier-specific) not shared REMARKS column.
        /// </summary>
        public const string UPDATE_HR_APPROVAL = @"
UPDATE DBO.TTRAININGENROLLMENT
SET
    ENROLLMENTSTATUS = @EnrollmentStatus,
    HRAPPROVALID     = @HRApprovalId,
    HRAPPROVEDON     = @HRApprovedOn,       -- FIX: was HRPROCESSEDON
    HRREMARKS        = @HRRemarks,          -- FIX: tier-specific column
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE ENROLLMENTID = @EnrollmentId
  AND TENANTID     = @TenantId;";

        /// <summary>Withdraw an enrollment — records reason and timestamp.</summary>
        public const string WITHDRAW_ENROLLMENT = @"
UPDATE DBO.TTRAININGENROLLMENT
SET
    ENROLLMENTSTATUS = @EnrollmentStatus,
    WITHDRAWALREASON = @WithdrawalReason,
    WITHDRAWNON      = @WithdrawnOn,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE ENROLLMENTID = @EnrollmentId
  AND TENANTID     = @TenantId;";

        /// <summary>
        /// Soft-delete: sets STATUS = 0.
        /// FIX: Replaced hard DELETE with soft-delete — enrollment history must be preserved.
        /// </summary>
        public const string DELETE_ENROLLMENT = @"
UPDATE DBO.TTRAININGENROLLMENT
SET
    STATUS       = 0,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE ENROLLMENTID = @EnrollmentId
  AND TENANTID     = @TenantId;";
    }
}