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

namespace RecruitmentDAL.Query.ManpowerBudget
{
    public static class ManpowerBudgetQB
    {
        // Covering indexes (see DB/Migrations/20260905_Recruitment_Phase6_ManpowerBudget_SqlServer.sql):
        //   PK_TMANPOWERBUDGET (MANPOWERBUDGETID), IX_TMANPOWERBUDGET_TENANTID (TENANTID),
        //   IX_TMANPOWERBUDGET_DEPT_FY (DEPARTMENTID, FISCALYEAR, STATUS)
        public const string GET_MANPOWERBUDGET = @"
SELECT
    MB.MANPOWERBUDGETID     AS ManpowerBudgetId,
    MB.DEPARTMENTID         AS DepartmentId,
    MB.FISCALYEAR           AS FiscalYear,
    MB.FISCALQUARTER        AS FiscalQuarter,
    MB.APPROVEDHEADCOUNT    AS ApprovedHeadcount,
    MB.APPROVEDBUDGETAMOUNT AS ApprovedBudgetAmount,
    MB.CONSUMEDHEADCOUNT    AS ConsumedHeadcount,
    MB.CONSUMEDBUDGETAMOUNT AS ConsumedBudgetAmount,

    MB.CREATEDBYID          AS CreatedById,
    MB.CREATEDON            AS CreatedOn,
    CU.USERNAME             AS CreatedByName,

    MB.MODIFIEDBYID         AS ModifiedById,
    MB.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME             AS ModifiedByName,

    MB.SORTORDER            AS SortOrder,
    MB.STATUS               AS Status,
    MB.VERSION              AS Version,
    MB.TENANTID             AS TenantId
FROM TMANPOWERBUDGET MB
LEFT JOIN MUSER CU ON CU.USERID = MB.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = MB.MODIFIEDBYID
WHERE MB.MANPOWERBUDGETID = @ManpowerBudgetId
AND   MB.TENANTID         = @TenantId;
";

        public const string SAVE_MANPOWERBUDGET = @"
INSERT INTO TMANPOWERBUDGET (
    MANPOWERBUDGETID,
    DEPARTMENTID,
    FISCALYEAR,
    FISCALQUARTER,
    APPROVEDHEADCOUNT,
    APPROVEDBUDGETAMOUNT,
    CONSUMEDHEADCOUNT,
    CONSUMEDBUDGETAMOUNT,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @ManpowerBudgetId,
    @DepartmentId,
    @FiscalYear,
    @FiscalQuarter,
    @ApprovedHeadcount,
    @ApprovedBudgetAmount,
    @ConsumedHeadcount,
    @ConsumedBudgetAmount,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @TenantId
);
";

        public const string UPDATE_MANPOWERBUDGET = @"
UPDATE TMANPOWERBUDGET
SET
    DEPARTMENTID         = @DepartmentId,
    FISCALYEAR            = @FiscalYear,
    FISCALQUARTER         = @FiscalQuarter,
    APPROVEDHEADCOUNT     = @ApprovedHeadcount,
    APPROVEDBUDGETAMOUNT  = @ApprovedBudgetAmount,
    CONSUMEDHEADCOUNT     = @ConsumedHeadcount,
    CONSUMEDBUDGETAMOUNT  = @ConsumedBudgetAmount,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    VERSION               = @Version
WHERE MANPOWERBUDGETID = @ManpowerBudgetId
AND   TENANTID          = @TenantId;
";

        // Soft delete via STATUS=2 (Deleted) — same convention as every other Recruitment entity
        // in this module (JobRequisition, Candidate, BgVerification, ...).
        public const string DELETE_MANPOWERBUDGET = @"
UPDATE TMANPOWERBUDGET
SET    STATUS       = 2,
       MODIFIEDBYID = @ModifiedById,
       MODIFIEDON   = @ModifiedOn
WHERE  MANPOWERBUDGETID = @ManpowerBudgetId
AND    TENANTID          = @TenantId;
";

        public const string GET_SELECTLIST_MANPOWERBUDGET = @"
SELECT
    MB.MANPOWERBUDGETID     AS ManpowerBudgetId,
    MB.DEPARTMENTID         AS DepartmentId,
    MB.FISCALYEAR           AS FiscalYear,
    MB.FISCALQUARTER        AS FiscalQuarter,
    MB.APPROVEDHEADCOUNT    AS ApprovedHeadcount,
    MB.APPROVEDBUDGETAMOUNT AS ApprovedBudgetAmount,
    MB.CONSUMEDHEADCOUNT    AS ConsumedHeadcount,
    MB.CONSUMEDBUDGETAMOUNT AS ConsumedBudgetAmount,

    MB.CREATEDBYID          AS CreatedById,
    MB.CREATEDON            AS CreatedOn,
    CU.USERNAME             AS CreatedByName,

    MB.MODIFIEDBYID         AS ModifiedById,
    MB.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME             AS ModifiedByName,

    MB.SORTORDER            AS SortOrder,
    MB.STATUS               AS Status,
    MB.VERSION              AS Version,
    MB.TENANTID             AS TenantId
FROM TMANPOWERBUDGET MB
LEFT JOIN MUSER CU ON CU.USERID = MB.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = MB.MODIFIEDBYID
WHERE MB.STATUS   = 1
AND   MB.TENANTID = @TenantId
ORDER BY MB.FISCALYEAR DESC, MB.FISCALQUARTER DESC, MB.DEPARTMENTID;
";

        // Resolution query for JobRequisitionBLL.ApproveJobRequisition's budget-gate check, used
        // only when the requisition's own BudgetReferenceId is unset (-1) — see that method's doc
        // comment for the full fallback order. Picks the row for this Department+FiscalYear whose
        // FiscalQuarter exactly matches the current quarter when one exists, otherwise falls back
        // to that department's whole-year row (FISCALQUARTER = 0). TOP 1 is defensive: the
        // UQ_TMANPOWERBUDGET_DEPT_PERIOD unique constraint already prevents more than one active
        // row per (DepartmentId, FiscalYear, FiscalQuarter, TenantId) tuple.
        public const string GET_ACTIVE_MANPOWERBUDGET_FOR_DEPARTMENT_PERIOD = @"
SELECT TOP 1
    MB.MANPOWERBUDGETID     AS ManpowerBudgetId,
    MB.DEPARTMENTID         AS DepartmentId,
    MB.FISCALYEAR           AS FiscalYear,
    MB.FISCALQUARTER        AS FiscalQuarter,
    MB.APPROVEDHEADCOUNT    AS ApprovedHeadcount,
    MB.APPROVEDBUDGETAMOUNT AS ApprovedBudgetAmount,
    MB.CONSUMEDHEADCOUNT    AS ConsumedHeadcount,
    MB.CONSUMEDBUDGETAMOUNT AS ConsumedBudgetAmount,

    MB.CREATEDBYID          AS CreatedById,
    MB.CREATEDON            AS CreatedOn,
    CU.USERNAME             AS CreatedByName,

    MB.MODIFIEDBYID         AS ModifiedById,
    MB.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME             AS ModifiedByName,

    MB.SORTORDER            AS SortOrder,
    MB.STATUS               AS Status,
    MB.VERSION              AS Version,
    MB.TENANTID             AS TenantId
FROM TMANPOWERBUDGET MB
LEFT JOIN MUSER CU ON CU.USERID = MB.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = MB.MODIFIEDBYID
WHERE MB.DEPARTMENTID = @DepartmentId
AND   MB.FISCALYEAR   = @FiscalYear
AND   MB.STATUS       = 1
AND   MB.TENANTID     = @TenantId
AND   (MB.FISCALQUARTER = @FiscalQuarter OR MB.FISCALQUARTER = 0)
ORDER BY CASE WHEN MB.FISCALQUARTER = @FiscalQuarter THEN 0 ELSE 1 END, MB.FISCALQUARTER DESC;
";

        // Standalone follow-up update fired from JobRequisitionBLL.ApproveJobRequisition once the
        // requisition's own ExecuteSaveAsync transition has committed — NOT part of that
        // transaction (ManpowerBudget is a different entity/DTO type with its own
        // BaseEntityAppService<ManpowerBudgetDTO> pipeline; folding a second entity's mutation
        // into JobRequisition's ExecuteSaveAsync call would require a hand-rolled shared
        // transaction, which CLAUDE.md's ExecuteSaveAsync section reserves for genuinely
        // multi-entity composite operations only). Matches the same non-transactional-follow-up
        // idiom as ICandidateDAL.UpdateCandidateEmployeeId — worst case on failure here is a real
        // Approved requisition whose budget consumption hasn't caught up yet (recoverable/
        // reconcilable), not data corruption of the requisition itself.
        public const string INCREMENT_MANPOWERBUDGET_CONSUMED = @"
UPDATE TMANPOWERBUDGET
SET
    CONSUMEDHEADCOUNT    = CONSUMEDHEADCOUNT    + @HeadcountDelta,
    CONSUMEDBUDGETAMOUNT = CONSUMEDBUDGETAMOUNT + @BudgetAmountDelta,
    MODIFIEDBYID         = @ModifiedById,
    MODIFIEDON           = @ModifiedOn
WHERE MANPOWERBUDGETID = @ManpowerBudgetId
AND   TENANTID          = @TenantId;
";
    }
}