using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.ShiftPattern
{
    // Indexes recommended:
    //   MSHIFTPATTERN: IX_MSHIFTPATTERN_SORTORDER (SORTORDER)
    //   MSHIFTPATTERN: IX_MSHIFTPATTERN_CODE      (SHIFTPATTERNCODE, SHIFTPATTERNNAME)
    public static class ShiftPatternQB
    {
        // ── Get header ───────────────────────────────────────────────────

        public const string GET_SHIFTPATTERN = @"SELECT
    A.SHIFTPATTERNID       AS ShiftPatternId,
    A.SHIFTPATTERNCODE     AS ShiftPatternCode,
    A.SHIFTPATTERNNAME     AS ShiftPatternName,
    A.PATTERNTYPE           AS ShiftPatternType,
    A.DATES                 AS ShiftPatternDates,
    A.DAYS                  AS ShiftPatternDays,

    A.SORTORDER             AS ShiftPatternSortOrder,
    A.STATUS                AS ShiftPatternStatus,
    A.VERSION               AS ShiftPatternVersion,

    A.CREATEDBYID           AS ShiftPatternCreatedById,
    A.CREATEDON             AS ShiftPatternCreatedOn,
    A.MODIFIEDBYID          AS ShiftPatternModifiedById,
    A.MODIFIEDON            AS ShiftPatternModifiedOn
FROM MSHIFTPATTERN A
WHERE A.SHIFTPATTERNID = @shiftpatternid";

        // ── Get days rows (joined to MSHIFT for display) ────────────────

        public const string GET_SHIFTPATTERNDAYS = @"SELECT
    D.SHIFTPATTERNDAYSID    AS ShiftPatternDaysId,
    D.SHIFTPATTERNID        AS ShiftPatternId,

    D.SLNO                  AS ShiftPatternDaysSlNo,
    D.DAYTYPE                AS ShiftPatternDaysDayType,

    D.SHIFTID                AS ShiftId,
    S.SHIFTCODE              AS ShiftCode,
    S.SHIFTDESCRIPTION       AS ShiftDescription,

    D.NUMBEROFDAYS           AS ShiftPatternDaysNumberofDays
FROM MSHIFTPATTERNDAYS D
LEFT JOIN MSHIFT S ON D.SHIFTID = S.SHIFTID
WHERE D.SHIFTPATTERNID = @shiftpatternid
ORDER BY D.SLNO";

        // ── Get detail rows for a single days row (joined to MSHIFT) ────

        public const string GET_SHIFTPATTERNDETAIL = @"SELECT
    T.SHIFTPATTERNDETAILID   AS ShiftPatternDetailId,
    T.SHIFTPATTERNDAYSID     AS ShiftPatternDaysId,

    T.SLNO                    AS ShiftPatternDetailSlNo,
    T.DAYTYPE                 AS ShiftPatternDetailDayType,

    T.SHIFTID                 AS ShiftId,
    S.SHIFTCODE               AS ShiftCode,
    S.SHIFTDESCRIPTION        AS ShiftDescription
FROM MSHIFTPATTERNDETAIL T
LEFT JOIN MSHIFT S ON T.SHIFTID = S.SHIFTID
WHERE T.SHIFTPATTERNDAYSID = @shiftpatterndaysid
ORDER BY T.SLNO";

        // ── Load all detail rows for a pattern (through days), for the
        //    LoadShiftPatternDetails sub-endpoint ──────────────────────

        public const string LOAD_SHIFTPATTERNDETAILS = @"SELECT
    T.SHIFTPATTERNDETAILID   AS ShiftPatternDetailId,
    T.SHIFTPATTERNDAYSID     AS ShiftPatternDaysId,

    T.SLNO                    AS ShiftPatternDetailSlNo,
    T.DAYTYPE                 AS ShiftPatternDetailDayType,

    T.SHIFTID                 AS ShiftId,
    S.SHIFTCODE               AS ShiftCode,
    S.SHIFTDESCRIPTION        AS ShiftDescription
FROM MSHIFTPATTERNDETAIL T
JOIN MSHIFTPATTERNDAYS DY ON T.SHIFTPATTERNDAYSID = DY.SHIFTPATTERNDAYSID
LEFT JOIN MSHIFT S ON T.SHIFTID = S.SHIFTID
WHERE DY.SHIFTPATTERNID = @shiftpatternid
ORDER BY DY.SLNO, T.SLNO";

        // ── Insert / Update header ───────────────────────────────────────
        // SOURCETYPE / TENANTID are not exposed on ShiftPatternDTO — left to their
        // table DEFAULT constraints (5 / -1) on insert, untouched on update.

        public const string SAVE_SHIFTPATTERN = @"INSERT INTO MSHIFTPATTERN
(
    SHIFTPATTERNID, SHIFTPATTERNCODE, SHIFTPATTERNNAME, PATTERNTYPE, DATES, DAYS,
    SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ShiftPatternId, @ShiftPatternCode, @ShiftPatternName, @ShiftPatternType, @ShiftPatternDates, @ShiftPatternDays,
    @ShiftPatternSortOrder, @ShiftPatternStatus, 1, @ShiftPatternCreatedById, @ShiftPatternCreatedOn, @ShiftPatternModifiedById, @ShiftPatternModifiedOn
);";

        public const string UPDATE_SHIFTPATTERN = @"UPDATE MSHIFTPATTERN
SET
    SHIFTPATTERNCODE   = @ShiftPatternCode,
    SHIFTPATTERNNAME   = @ShiftPatternName,
    PATTERNTYPE         = @ShiftPatternType,
    DATES               = @ShiftPatternDates,
    DAYS                = @ShiftPatternDays,
    SORTORDER           = @ShiftPatternSortOrder,
    STATUS              = @ShiftPatternStatus,
    VERSION             = VERSION + 1,
    MODIFIEDBYID        = @ShiftPatternModifiedById,
    MODIFIEDON          = @ShiftPatternModifiedOn
WHERE SHIFTPATTERNID = @ShiftPatternId;";

        public const string DELETE_SHIFTPATTERN = @"DELETE FROM MSHIFTPATTERN
                                            WHERE SHIFTPATTERNID = @shiftpatternid;";

        // ── Insert / Delete days rows ────────────────────────────────────

        public const string SAVE_SHIFTPATTERNDAYS = @"INSERT INTO MSHIFTPATTERNDAYS
(
    SHIFTPATTERNDAYSID, SHIFTPATTERNID, SLNO, DAYTYPE, SHIFTID, NUMBEROFDAYS
)
VALUES
(
    @ShiftPatternDaysId, @ShiftPatternId, @ShiftPatternDaysSlNo, @ShiftPatternDaysDayType, @ShiftId, @ShiftPatternDaysNumberofDays
);";

        public const string DELETE_SHIFTPATTERNDAYS = @"DELETE FROM MSHIFTPATTERNDAYS
                                            WHERE SHIFTPATTERNID = @shiftpatternid;";

        // ── Insert / Delete detail rows ──────────────────────────────────

        public const string SAVE_SHIFTPATTERNDETAIL = @"INSERT INTO MSHIFTPATTERNDETAIL
(
    SHIFTPATTERNDETAILID, SHIFTPATTERNDAYSID, SLNO, DAYTYPE, SHIFTID
)
VALUES
(
    @ShiftPatternDetailId, @ShiftPatternDaysId, @ShiftPatternDetailSlNo, @ShiftPatternDetailDayType, @ShiftId
);";

        // Detail rows do not carry SHIFTPATTERNID directly — reach them through their days rows.
        public const string DELETE_SHIFTPATTERNDETAIL_BY_PATTERN = @"DELETE FROM MSHIFTPATTERNDETAIL
WHERE SHIFTPATTERNDAYSID IN
(
    SELECT SHIFTPATTERNDAYSID FROM MSHIFTPATTERNDAYS WHERE SHIFTPATTERNID = @shiftpatternid
);";

        // ── Employee-in-use guard ────────────────────────────────────────
        // MEMPLOYEE.SHIFTPATTERNDETAILID references MSHIFTPATTERNDETAIL — block replacing
        // days/detail rows that are still referenced by an employee assignment.

        public const string GET_EMPLOYEES_USING_SHIFTPATTERN = @"SELECT EMPLOYEEID FROM MEMPLOYEE
WHERE SHIFTPATTERNDETAILID IN
(
    SELECT T.SHIFTPATTERNDETAILID
    FROM MSHIFTPATTERNDETAIL T
    JOIN MSHIFTPATTERNDAYS DY ON T.SHIFTPATTERNDAYSID = DY.SHIFTPATTERNDAYSID
    WHERE DY.SHIFTPATTERNID = @shiftpatternid
);";

        // ── List (header fields only, no days/detail hierarchy) ─────────

        public const string GET_SHIFTPATTERN_LIST = @"SELECT
    A.SHIFTPATTERNID       AS ShiftPatternId,
    A.SHIFTPATTERNCODE     AS ShiftPatternCode,
    A.SHIFTPATTERNNAME     AS ShiftPatternName,
    A.PATTERNTYPE           AS ShiftPatternType,
    A.DATES                 AS ShiftPatternDates,
    A.DAYS                  AS ShiftPatternDays,

    A.SORTORDER             AS ShiftPatternSortOrder,
    A.STATUS                AS ShiftPatternStatus,
    A.VERSION               AS ShiftPatternVersion,

    A.CREATEDBYID           AS ShiftPatternCreatedById,
    A.CREATEDON             AS ShiftPatternCreatedOn,
    A.MODIFIEDBYID          AS ShiftPatternModifiedById,
    A.MODIFIEDON            AS ShiftPatternModifiedOn
FROM MSHIFTPATTERN A
WHERE 1 = 1";

        // ── Picklist (paged, dual dialect) ────────────────────────────────

        public const string GET_SELECTLIST_SHIFTPATTERN_SQL = @"
            WITH Paged AS (
                SELECT a.SHIFTPATTERNID   AS Id,
                       a.SHIFTPATTERNCODE AS Code,
                       a.SHIFTPATTERNNAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY a.SORTORDER) AS RowNum
                FROM   MSHIFTPATTERN a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SHIFTPATTERN_SQL_FOOTER = @"
            )
            SELECT Id, Code, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_SHIFTPATTERN_PG = @"
            WITH Paged AS (
                SELECT a.SHIFTPATTERNID   AS ""Id"",
                       a.SHIFTPATTERNCODE AS ""Code"",
                       a.SHIFTPATTERNNAME AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY a.SORTORDER) AS ""RowNum""
                FROM   MSHIFTPATTERN a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SHIFTPATTERN_PG_FOOTER = @"
            )
            SELECT ""Id"", ""Code"", ""Name""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        // ── Count (real count — GB4's count branch was a hardcoded no-op) ──

        public const string GET_SELECTLIST_SHIFTPATTERN_COUNT_SQL = @"
            SELECT COUNT(*) FROM (
                SELECT a.SHIFTPATTERNID AS Id
                FROM   MSHIFTPATTERN a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SHIFTPATTERN_COUNT_FOOTER = @"
            ) x;";
    }
}