namespace ECPDAL.Meeting.QueryBuilders;

public static class MeetingQB
{
    // Dynamic — no hardcoded GCMTYPEID. GCMTYPECODE is a fixed, self-describing key ("MEETINGTYPE")
    // that resolves to whatever GCMTYPEID happens to exist in this environment; if none exists yet,
    // MeetingBLL.GetSelectListMeetingAsync auto-seeds one (see EnsureMeetingTypeGcmSeededAsync).
    public const string GCMTYPECODE_MEETINGTYPE = "MEETINGTYPE";

    // Confirmed shape (MMDAL/IceImportDAL's GcmCodeLookupQB): MGCMTYPE has GCMTYPEID (PK),
    // GCMTYPECODE, GCMTYPEDESCRIPTION, MODULEID, STATUS, ... MGCM has GCMID (PK), GCMCODE,
    // GCMNAME, GCMTYPEID, STATUS.
    public const string GET_GCMTYPE_BY_CODE = @"
        SELECT GCMTYPEID AS GcmTypeId
        FROM   MGCMTYPE
        WHERE  GCMTYPECODE = @GcmTypeCode
          AND  STATUS      = 1";

    // MGCM/MGCMTYPE ids are large negative numbers (confirmed via sample rows, e.g. -1799999998,
    // -1499999993) — a fixed master-data numbering convention, not ECP's own AutoNumber service
    // (which generates positive, per-tenant ids for T* transactional tables and has no precedent
    // anywhere in this codebase for MGCM/MGCMTYPE). MIN(id)-1 guarantees a unique negative id
    // without colliding with any existing row, fully dynamically (no hardcoded number).
    public const string GET_MIN_GCMTYPEID = "SELECT ISNULL(MIN(GCMTYPEID), 0) FROM MGCMTYPE";
    public const string GET_MIN_GCMID     = "SELECT ISNULL(MIN(GCMID), 0) FROM MGCM";

    public const string INSERT_GCMTYPE = @"
        INSERT INTO MGCMTYPE (
            GCMTYPEID, GCMTYPECODE, GCMTYPEDESCRIPTION, MODULEID, GCMCODEREQ, GCMCODESIZE,
            GCMENTRYTYPE, ISCOLORED, SORTORDER, VERSION, STATUS, SOURCETYPE,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @GcmTypeId, @GcmTypeCode, @GcmTypeDescription, @ModuleId, 0, 0,
            0, 0, 0, 0, 1, 2,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string INSERT_GCM = @"
        INSERT INTO MGCM (GCMID, GCMTYPEID, GCMCODE, GCMNAME, STATUS)
        VALUES (@GcmId, @GcmTypeId, @GcmCode, @GcmName, 1)";

    // Index: IX_MGCM_GCMTYPEID
    public const string GET_SELECTLIST_MEETINGTYPE = @"
        SELECT G.GCMID AS MeetingTypeId, G.GCMCODE AS Code, G.GCMNAME AS Name
        FROM   MGCM G
        WHERE  G.GCMTYPEID = @GcmTypeId
          AND  G.STATUS    = 1
        ORDER BY G.GCMNAME ASC";

    private const string SELECT_COLUMNS = @"
            M.MEETINGID          AS MeetingId,
            M.TITLE               AS Title,
            M.MEETINGTYPEID       AS MeetingTypeId,
            M.STARTON             AS StartOn,
            M.ENDON               AS EndOn,
            M.VIRTUALLINK         AS VirtualLink,
            M.LOCATION            AS Location,
            M.ORGANIZERUSERID     AS OrganizerUserId,
            U.USERCODE            AS OrganizerUserCode,
            U.USERNAME            AS OrganizerUserName,
            M.STATUS              AS Status,
            M.OBJECTTYPEID        AS ObjectTypeId,
            M.OBJECTID            AS ObjectId,
            M.OUID                AS OuId,
            M.TENANTID            AS TenantId,
            M.DATABASENAME        AS DatabaseName,
            M.DATABASETYPE        AS DatabaseType,
            M.VERSION             AS Version,
            M.CREATEDBYID         AS CreatedById,
            M.CREATEDON           AS CreatedOn,
            M.MODIFIEDBYID        AS ModifiedById,
            M.MODIFIEDON          AS ModifiedOn";

    // Index: PK (MeetingId)
    public const string GET_MEETING = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TMEETING M
        LEFT JOIN MUSER U ON M.ORGANIZERUSERID = U.USERID
        WHERE  M.MEETINGID    = @MeetingId
          AND  M.DATABASENAME = @DatabaseName";

    // Flexible filter list — filters arrive via CriteriaDTO.SectionCriteriaList (see
    // MeetingDAL.ApplyMeetingListCriteria), same generic FieldName/OperationType shape CRM's
    // TaskDAL uses. {CRITERIA} is replaced with zero or more "AND <condition>" fragments.
    // Indexes: IX_TMEETING_ORGANIZERUSERID, IX_TMEETING_STARTON, IX_TMEETINGPARTICIPANT_USERID.
    public const string GET_MEETING_LIST = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TMEETING M
        LEFT JOIN MUSER U ON M.ORGANIZERUSERID = U.USERID
        WHERE  M.DATABASENAME = @DatabaseName
          {CRITERIA}
        ORDER BY M.STARTON ASC";

    public const string SAVE_MEETING = @"
        INSERT INTO TMEETING (
            MEETINGID, TITLE, MEETINGTYPEID, STARTON, ENDON, VIRTUALLINK, LOCATION,
            ORGANIZERUSERID, STATUS, OBJECTTYPEID, OBJECTID, OUID,
            TENANTID, DATABASENAME, DATABASETYPE, VERSION,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @MeetingId, @Title, @MeetingTypeId, @StartOn, @EndOn, @VirtualLink, @Location,
            @OrganizerUserId, @Status, @ObjectTypeId, @ObjectId, @OuId,
            @TenantId, @DatabaseName, @DatabaseType, @Version,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_MEETING = @"
        UPDATE TMEETING
        SET    TITLE         = @Title,
               MEETINGTYPEID = @MeetingTypeId,
               STARTON       = @StartOn,
               ENDON         = @EndOn,
               VIRTUALLINK   = @VirtualLink,
               LOCATION      = @Location,
               OBJECTTYPEID  = @ObjectTypeId,
               OBJECTID      = @ObjectId,
               OUID          = @OuId,
               MODIFIEDBYID  = @ModifiedById,
               MODIFIEDON    = @ModifiedOn,
               VERSION       = VERSION + 1
        WHERE  MEETINGID    = @MeetingId
          AND  DATABASENAME = @DatabaseName";

    public const string UPDATE_MEETING_STATUS = @"
        UPDATE TMEETING
        SET    STATUS       = @NewStatus,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  MEETINGID    = @MeetingId
          AND  DATABASENAME = @DatabaseName";

    // DeleteMeeting cancels rather than hard-deletes — TMEETING has no separate row-level status
    // column, per the plan's single-STATUS model (1=scheduled/2=completed/3=cancelled), the same
    // pattern used by TCOLLABORATIONSPACE (delete=archive) in this same phase.
    public const string CANCEL_MEETING = @"
        UPDATE TMEETING
        SET    STATUS       = 3,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  MEETINGID    = @MeetingId
          AND  DATABASENAME = @DatabaseName";

    // Index: IX_TMEETINGPARTICIPANT_USERID
    public const string GET_PARTICIPANTS_FOR_MEETING = @"
        SELECT
            P.MEETINGPARTICIPANTID AS MeetingParticipantId,
            P.MEETINGID            AS MeetingId,
            P.USERID               AS UserId,
            U.USERCODE             AS UserCode,
            U.USERNAME             AS UserName,
            P.ATTENDANCESTATUS     AS AttendanceStatus,
            P.TENANTID             AS TenantId,
            P.DATABASENAME         AS DatabaseName,
            P.DATABASETYPE         AS DatabaseType,
            P.STATUS               AS Status,
            P.CREATEDBYID          AS CreatedById,
            P.CREATEDON            AS CreatedOn,
            P.MODIFIEDBYID         AS ModifiedById,
            P.MODIFIEDON           AS ModifiedOn
        FROM   TMEETINGPARTICIPANT P
        LEFT JOIN MUSER U ON P.USERID = U.USERID
        WHERE  P.MEETINGID    = @MeetingId
          AND  P.STATUS       = 1
          AND  P.DATABASENAME = @DatabaseName
        ORDER BY P.CREATEDON ASC";

    // Dedupe check before insert — surfaces a clear "already a participant" BLL error rather than
    // relying solely on UX_TMEETINGPARTICIPANT_MEETING_USER's constraint violation.
    public const string GET_PARTICIPANT = @"
        SELECT
            P.MEETINGPARTICIPANTID AS MeetingParticipantId,
            P.MEETINGID            AS MeetingId,
            P.USERID               AS UserId,
            U.USERCODE             AS UserCode,
            U.USERNAME             AS UserName,
            P.ATTENDANCESTATUS     AS AttendanceStatus,
            P.TENANTID             AS TenantId,
            P.DATABASENAME         AS DatabaseName,
            P.DATABASETYPE         AS DatabaseType,
            P.STATUS               AS Status,
            P.CREATEDBYID          AS CreatedById,
            P.CREATEDON            AS CreatedOn,
            P.MODIFIEDBYID         AS ModifiedById,
            P.MODIFIEDON           AS ModifiedOn
        FROM   TMEETINGPARTICIPANT P
        LEFT JOIN MUSER U ON P.USERID = U.USERID
        WHERE  P.MEETINGID    = @MeetingId
          AND  P.USERID       = @UserId
          AND  P.STATUS       = 1
          AND  P.DATABASENAME = @DatabaseName";

    // "Invite participant" picker — active MUSER rows not already an active participant of this
    // meeting. Same shape as CollaborationSpaceQB.GET_SELECTLIST_MEMBER.
    public const string GET_SELECTLIST_PARTICIPANT = @"
        SELECT U.USERID AS UserId, U.USERCODE AS UserCode, U.USERNAME AS UserName
        FROM   MUSER U
        WHERE  U.DATABASENAME = @DatabaseName
          AND  NOT EXISTS (SELECT 1 FROM TMEETINGPARTICIPANT P
                            WHERE P.MEETINGID = @MeetingId
                              AND P.USERID = U.USERID AND P.STATUS = 1)
        ORDER BY U.USERNAME ASC";

    // Hard delete — same precedent as CollaborationSpaceQB.REMOVE_MEMBER, so a removed
    // participant can be re-invited later without the unique key blocking the insert.
    public const string REMOVE_PARTICIPANT = @"
        DELETE FROM TMEETINGPARTICIPANT
        WHERE  MEETINGID    = @MeetingId
          AND  USERID       = @UserId
          AND  DATABASENAME = @DatabaseName";

    public const string ADD_PARTICIPANT = @"
        INSERT INTO TMEETINGPARTICIPANT (
            MEETINGPARTICIPANTID, MEETINGID, USERID, ATTENDANCESTATUS,
            TENANTID, DATABASENAME, DATABASETYPE, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @MeetingParticipantId, @MeetingId, @UserId, @AttendanceStatus,
            @TenantId, @DatabaseName, @DatabaseType, @Status,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_PARTICIPANT_ATTENDANCE = @"
        UPDATE TMEETINGPARTICIPANT
        SET    ATTENDANCESTATUS = @AttendanceStatus,
               MODIFIEDBYID     = @ModifiedById,
               MODIFIEDON       = @ModifiedOn
        WHERE  MEETINGID    = @MeetingId
          AND  USERID       = @UserId
          AND  DATABASENAME = @DatabaseName";
}