-- =====================================================================
-- FILE 1: Stored Procedure
-- Proc_TA_GetCWHSanctionDetailsForSuperAdmin
-- RULE: Always return dates as yyyy-MM-dd (style 23). Never format in SQL.
-- exec Proc_TA_GetCWHSanctionDetailsForSuperAdmin @Employee_ID=1,@LevelId='1',@Status='All',@EmployeeId='',@EmployeeName='',@EmployeeCategory=0,@EmployeeType=0,@CWH_FromDate='',@CWH_ToDate='',@Isdeleted=0,@EmpSwipeCard='',@UserGroupName='SuperAdmin',@ParentID=1
-- =====================================================================

ALTER PROCEDURE [dbo].[Proc_TA_GetCWHSanctionDetailsForSuperAdmin]
    @Employee_ID        INT,
    @LevelId            VARCHAR(2000),
    @Status             VARCHAR(20)     = NULL,
    @EmployeeId         VARCHAR(100),
    @EmployeeName       VARCHAR(50),
    @EmployeeCategory   INT,
    @EmployeeType       INT,
    @CWH_FromDate       VARCHAR(20)     = NULL,
    @CWH_ToDate         VARCHAR(20)     = NULL,
    @Isdeleted          BIT             = 0,
    @EmpSwipeCard       VARCHAR(20),
    @UserGroupName      VARCHAR(250),
    @ParentID           INT
AS
BEGIN
    SET NOCOUNT ON;

    -- ═══════════════════════════════════════════════════════════════════
    -- DESIGN RULE: SQL returns ALL dates as yyyy-MM-dd (CONVERT style 23)
    -- C# RowDataBound formats for display using Session["MDateFormat"].
    -- NEVER use CONVERT(VARCHAR, date, 103/106/110) in the SELECT list.
    -- ═══════════════════════════════════════════════════════════════════

    -- ─── Step 1: Parse Input Date Parameters ────────────────────────────
    -- If empty string or NULL is passed → @FromDate / @ToDate stays NULL
    -- → date filter is skipped entirely (no WHERE clause added)
    -- ────────────────────────────────────────────────────────────────────
    DECLARE @FromDate DATETIME = NULL
    DECLARE @ToDate   DATETIME = NULL

    -- Only attempt parse when a non-empty value is supplied
    IF LTRIM(RTRIM(ISNULL(@CWH_FromDate, ''))) <> ''
    BEGIN
        SET @FromDate = COALESCE(
            TRY_CONVERT(DATETIME, @CWH_FromDate, 23),    -- yyyy-MM-dd          ← ISO first
            TRY_CONVERT(DATETIME, @CWH_FromDate, 126),   -- yyyy-MM-ddThh:mm:ss
            TRY_CONVERT(DATETIME, @CWH_FromDate, 120),   -- yyyy-MM-dd hh:mm:ss
            TRY_CONVERT(DATETIME, @CWH_FromDate, 112),   -- yyyyMMdd
            TRY_CONVERT(DATETIME, @CWH_FromDate, 106),   -- dd MMM yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 103),   -- dd/MM/yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 101),   -- MM/dd/yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate, 110),   -- MM-dd-yyyy
            TRY_CONVERT(DATETIME, @CWH_FromDate)         -- last resort
        )
        -- Normalize to start of day 00:00:00
        IF @FromDate IS NOT NULL
            SET @FromDate = CONVERT(DATETIME, CONVERT(DATE, @FromDate))
    END

    IF LTRIM(RTRIM(ISNULL(@CWH_ToDate, ''))) <> ''
    BEGIN
        SET @ToDate = COALESCE(
            TRY_CONVERT(DATETIME, @CWH_ToDate, 23),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 126),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 120),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 112),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 106),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 103),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 101),
            TRY_CONVERT(DATETIME, @CWH_ToDate, 110),
            TRY_CONVERT(DATETIME, @CWH_ToDate)
        )
        -- Normalize to end of day 23:59:59
        IF @ToDate IS NOT NULL
            SET @ToDate = DATEADD(SECOND, 86399, CONVERT(DATETIME, CONVERT(DATE, @ToDate)))
    END

    -- ─── Step 2: Normalize @Status ───────────────────────────────────────
    -- Treat NULL / empty / '0' / 'All' as "no status filter"
    IF LTRIM(RTRIM(ISNULL(@Status, ''))) = '' OR @Status = '0' OR UPPER(@Status) = 'ALL'
        SET @Status = NULL

    -- ─── Temp Tables ──────────────────────────────────────────────────────
    CREATE TABLE #EmpLevel    (EmployeeId INT, LevelId INT,    EffectiveDate DATETIME)
    CREATE TABLE #EmpType     (EmployeeId INT, TypeId INT,     EffectiveDate DATETIME)
    CREATE TABLE #EmpCategory (EmployeeId INT, CategoryId INT, EffectiveDate DATETIME)
    CREATE TABLE #LevelRights (LevelId INT)
    CREATE TABLE #EmployeeIds (EmpAutoId INT)

    -- ─── Level Rights ─────────────────────────────────────────────────────
    INSERT INTO #LevelRights
        SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName, @ParentID) AS LevelId

    DECLARE @isReportingto AS INT
    SELECT  @isReportingto = ReportsBasedOn
    FROM    TA_Tbl_Usergroup
    WHERE   groupName = @UserGroupName

    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID)   AS EmpAutoId
    INSERT INTO #EmployeeIds
        SELECT * FROM [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) AS EmpAutoId

    -- ─── Indexes ──────────────────────────────────────────────────────────
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_Effective]  ON #EmpLevel    (EffectiveDate ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_LevelId]    ON #EmpLevel    (LevelId       ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpLevel_EmployeeId] ON #EmpLevel    (EmployeeId    ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpType_Effective]   ON #EmpType     (EffectiveDate ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpType_TypeId]      ON #EmpType     (TypeId        ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpType_EmployeeId]  ON #EmpType     (EmployeeId    ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_Effective]    ON #EmpCategory (EffectiveDate ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_CategoryId]   ON #EmpCategory (CategoryId    ASC)
    CREATE NONCLUSTERED INDEX [IDX_EmpCat_EmployeeId]   ON #EmpCategory (EmployeeId    ASC)

    -- ─── Populate Temp Tables ─────────────────────────────────────────────
    DECLARE @Today DATETIME = CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

    INSERT INTO #EmpLevel
        SELECT ELevel.EmployeeId, EL.LevelId, ELevel.EffectiveDate
        FROM (
            SELECT MAX(el.EffectiveDate) AS EffectiveDate, el.EmployeeId
            FROM   TA_Tbl_EmployeeLevel el
            INNER JOIN TA_Tbl_Employee  emp ON el.EmployeeId = emp.Employee_Id
            WHERE  el.EmployeeId != @Employee_ID AND emp.Isdeleted = 0
            GROUP BY el.EmployeeId
        ) ELevel
        INNER JOIN TA_Tbl_EmployeeLevel EL
            ON  EL.EffectiveDate = ELevel.EffectiveDate
            AND EL.EmployeeId    = ELevel.EmployeeId
            AND EL.EffectiveDate <= @Today
            AND EL.LevelId IS NOT NULL

    INSERT INTO #EmpType
        SELECT EmpType.EmployeeId, EType.TypeId, EmpType.EffectiveDate
        FROM (
            SELECT MAX(et.EffectiveDate) AS EffectiveDate, et.EmployeeId
            FROM   TA_Tbl_EmployeeType et
            INNER JOIN TA_Tbl_Employee emp ON et.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY et.EmployeeId
        ) EmpType
        INNER JOIN TA_Tbl_EmployeeType EType
            ON  EType.EffectiveDate = EmpType.EffectiveDate
            AND EType.EmployeeId    = EmpType.EmployeeId
            AND EType.EffectiveDate <= @Today
            AND EType.TypeId IS NOT NULL

    INSERT INTO #EmpCategory
        SELECT EmpCategory.EmployeeId, ECategory.CategoryId, EmpCategory.EffectiveDate
        FROM (
            SELECT MAX(ec.EffectiveDate) AS EffectiveDate, ec.EmployeeId
            FROM   TA_Tbl_EmployeeCategory ec
            INNER JOIN TA_Tbl_Employee emp ON ec.EmployeeId = emp.Employee_Id
            WHERE  emp.Isdeleted = 0
            GROUP BY ec.EmployeeId
        ) EmpCategory
        INNER JOIN TA_Tbl_EmployeeCategory ECategory
            ON  ECategory.EffectiveDate = EmpCategory.EffectiveDate
            AND ECategory.EmployeeId    = EmpCategory.EmployeeId
            AND ECategory.EffectiveDate <= @Today
            AND ECategory.CategoryId IS NOT NULL

    -- ═══════════════════════════════════════════════════════════════════════
    -- Dynamic SQL
    -- CONVERT(VARCHAR(10), date, 23) → always yyyy-MM-dd
    -- C# parses this unambiguously and formats for display.
    -- ═══════════════════════════════════════════════════════════════════════
    DECLARE @sqlQuery AS NVARCHAR(MAX) = N''

    SET @sqlQuery =
        N'SELECT ' +
        N'    CWH.CWH_App_Id, ' +
        N'    ld.name                                                               AS Department, ' +
        N'    E.Employee_Code_Id                                                    AS EmployeeID, ' +
        N'    (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, ''''))    AS Employee_Name, ' +
        -- ── Always yyyy-MM-dd — C# formats for display, zero ambiguity ──
        N'    CONVERT(VARCHAR(10), CWH.ApplicationDate, 23)                         AS ApplicationDate, ' +
        N'    CONVERT(VARCHAR(10), CWH.FromDate,        23)                         AS FromDate, ' +
        N'    CONVERT(VARCHAR(10), CWH.ToDate,          23)                         AS ToDate, ' +
        N'    CWH.CompressedWorkHour, ' +
        N'    CWH.Reason, ' +
        N'    CWH.Status, ' +
        N'    ''0''                                                                 AS SelectedRecords ' +
        N'FROM  TA_Tbl_Employee E '

    SET @sqlQuery += N' INNER JOIN TA_CompressedWorkHoursApplication CWH ON E.Employee_Code_Id     = CWH.Employee_Id '
    SET @sqlQuery += N' INNER JOIN #EmpLevel                              ON #EmpLevel.EmployeeId   = E.Employee_Id '
    SET @sqlQuery += N' INNER JOIN #EmpType                               ON #EmpType.EmployeeId    = E.Employee_Id '
    SET @sqlQuery += N' INNER JOIN #EmpCategory                           ON #EmpCategory.EmployeeId= E.Employee_Id '
    SET @sqlQuery += N' INNER JOIN #LevelRights                           ON #LevelRights.LevelId   = #EmpLevel.LevelId '
    SET @sqlQuery += N' INNER JOIN Ta_tbl_leveldetails ld                 ON #EmpLevel.LevelId      = ld.id '
    SET @sqlQuery += N' INNER JOIN TA_Tbl_YearSetting  ys                 ON ys.YearSettings_Id     = CWH.YearSettings_Id '

    SET @sqlQuery += N' WHERE E.Isdeleted = 0 AND CWH.IsDeleted = 0 AND ys.IsActive = 1 '

    -- ─── Filter: Employee Name ────────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmployeeName, ''))) <> ''
        SET @sqlQuery += N' AND (E.Employee_FirstName + '' '' + ISNULL(E.Employee_LastName, '''') LIKE '''
                        + CAST(LTRIM(RTRIM(@EmployeeName)) + '%' AS NVARCHAR(100)) + N''')'

    -- ─── Filter: Reporting-To ─────────────────────────────────────────────
    IF (ISNULL(@isReportingto, 0) > 0)
        SET @sqlQuery += N' AND (E.Employee_ID IN (SELECT EmpAutoId FROM #EmployeeIds)) '

    -- ─── Filter: Swipe Card ───────────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmpSwipeCard, ''))) <> ''
        SET @sqlQuery += N' AND E.SwipeCardNo = ''' + @EmpSwipeCard + N''' '

    -- ─── Filter: Category ────────────────────────────────────────────────
    IF (ISNULL(@EmployeeCategory, 0) <> 0)
        SET @sqlQuery += N' AND #EmpCategory.CategoryId = ' + CAST(@EmployeeCategory AS NVARCHAR(8))

    -- ─── Filter: Type ────────────────────────────────────────────────────
    IF (ISNULL(@EmployeeType, 0) <> 0)
        SET @sqlQuery += N' AND #EmpType.TypeId = ' + CAST(@EmployeeType AS NVARCHAR(8))

    -- ─── Filter: Employee Code ───────────────────────────────────────────
    IF LTRIM(RTRIM(ISNULL(@EmployeeId, ''))) <> ''
        SET @sqlQuery += N' AND E.Employee_Code_Id = ''' + CAST(LTRIM(RTRIM(@EmployeeId)) AS NVARCHAR(50)) + N''' '

    -- ─── Filter: Status ──────────────────────────────────────────────────
    -- @Status is already NULL if 'All' / empty / '0' was passed
    IF @Status IS NOT NULL
        SET @sqlQuery += N' AND CWH.Status = ''' + @Status + N''' '

    -- ─── Filter: Date Range ──────────────────────────────────────────────
    -- Only applied when BOTH dates were successfully parsed.
    -- Empty string input → @FromDate / @ToDate = NULL → filter skipped → all records returned.
    IF @FromDate IS NOT NULL AND @ToDate IS NOT NULL
        SET @sqlQuery +=
            N' AND CWH.ApplicationDate BETWEEN @p_FromDate AND @p_ToDate '

    SET @sqlQuery += N' ORDER BY CWH.ApplicationDate DESC '

    -- ─── Execute ─────────────────────────────────────────────────────────
    EXEC sp_executesql
        @sqlQuery,
        N'@p_FromDate DATETIME, @p_ToDate DATETIME',
        @p_FromDate = @FromDate,
        @p_ToDate   = @ToDate

    -- ─── Cleanup ─────────────────────────────────────────────────────────
    DROP TABLE #EmpLevel
    DROP TABLE #EmpType
    DROP TABLE #EmpCategory
    DROP TABLE #LevelRights
    DROP TABLE #EmployeeIds

END