namespace FAMDAL.Query.TFam
{
    // Confirmed against the real TFAM DDL. TFAM is a ledger: FAMLEDGERID is a DB IDENTITY
    // (one row per asset line), FAMID groups multiple lines under one FAM transaction header.
    // No UPDATE — corrections go through a new amendment row (STATUS supports Amended),
    // matching the pre-existing GETTFAMEVENTTYPEID/DELETETFAMEVENTTYPEID reservation with no
    // UPDATETFAM counterpart.
    public static class TFamQB
    {
        public const string GET_TFAM = @"SELECT
                T.FAMLEDGERID                                   AS TFamLedgerId,
                T.FAMID                                         AS TFamFamId,
                T.OUID                                          AS OUId,
                T.PERIODID                                      AS PeriodId,
                T.BIZTRANSACTIONTYPEID                          AS BizTransactionTypeId,
                T.OBJECTHEADERTYPEID                            AS ObjectHeaderTypeId,
                T.OBJECTHEADERID                                AS ObjectHeaderId,
                T.OBJECTTYPEID                                  AS ObjectTypeId,
                T.OBJECTID                                      AS ObjectId,
                T.FAMNUMBER                                     AS TFamFamNumber,
                T.FAMDATE                                       AS TFamFamDate,
                T.REFERENCENUMBER                                AS TFamReferenceNumber,
                T.REFERENCEDATE                                  AS TFamReferenceDate,
                T.PARTYID                                       AS PartyId,
                T.PARTYBRANCHID                                 AS PartyBranchId,
                T.PARTYREFERENCENUMBER                          AS TFamPartyReferenceNumber,
                T.PARTYREFERENCEDATE                            AS TFamPartyReferenceDate,
                T.DEPARTMENTID                                  AS DepartmentId,
                T.STOREID                                       AS StoreId,
                T.LOCATIONID                                    AS LocationId,
                T.FROMLOCATIONID                                AS FromLocationId,
                T.TOLOCATIONID                                  AS ToLocationId,
                T.INCHARGEID                                    AS InchargeId,
                T.ISCAPITALIZED                                 AS TFamIsCapitalized,
                T.CAPITALIZEDON                                 AS TFamCapitalizedOn,
                T.SLNO                                          AS TFamSLNo,
                T.ASSETID                                       AS AssetId,
                T.ASSETCODE                                     AS TFamAssetCode,
                T.ASSETBATCH                                    AS TFamAssetBatch,
                T.ASSETNAME                                     AS TFamAssetName,
                T.REMARKS                                       AS TFamRemarks,
                T.ASSETSTOCKQUANTITY                            AS TFamAssetStockQuantity,
                T.ITEMID                                         AS ItemId,
                T.SKUID                                          AS SKUId,
                T.ASSETQUANTITY                                 AS TFamAssetQuantity,
                T.ASSETRATE                                     AS TFamAssetRate,
                T.ASSETGROSSVALUE                               AS TFamAssetGrossValue,
                T.ADDITION                                       AS TFamAddition,
                T.DEDUCTION                                      AS TFamDeduction,
                T.RETIREMENT                                     AS TFamRetirement,
                T.ASSETNETVALUE                                 AS TFamAssetNetValue,
                T.COA                                             AS TFamCOA,
                T.RESIDUALPERCENTAGE                            AS TFamResidualPercentage,
                T.RESIDUAL                                        AS TFamResidual,
                T.PREVIOUSDEPRECIATION                          AS TFamPreviousDepreciation,
                T.ASSETVALUEAFTERPREVIOUSDEPRECIATION           AS TFamAssetValueAfterPreviousDepreciation,
                T.DEPRECIATIONID                                AS DepreciationId,
                T.DEPRECIATIONSTARTDATE                         AS TFamDepreciationStartDate,
                T.DEPRECIATIONENDDATE                           AS TFamDepreciationEndDate,
                T.DEPRECIATIONDAYS                              AS TFamDepreciationDays,
                T.REMININGLIFEINDAYS                            AS TFamRemainingLifeInDays,
                T.NOOFSHIFTS                                     AS TFamNoOfShifts,
                T.DEPRECIATIONPERCENTAGE                        AS TFamDepreciationPercentage,
                T.CALCULATEDBASEDON                             AS TFamCalculatedBasedOn,
                T.DEPRECIATIONAMOUNT                            AS TFamDepreciationAmount,
                T.ASSETVALUEAFTERDEPRECIATON                    AS TFamAssetValueAfterDepreciation,
                T.FULLPERIODASSETVALUE                          AS TFamFullPeriodAssetValue,
                T.PARTPERIODASSETVALUE                          AS TFamPartPeriodAssetValue,
                T.TAXDEPRECIATIONID                             AS TaxDepreciationId,
                T.TAXDEPRECIATIONSTARTDATE                      AS TFamTaxDepreciationStartDate,
                T.TAXDEPRECIATIONENDDATE                        AS TFamTaxDepreciationEndDate,
                T.TAXDEPRECIATIONPERCENTAGE                     AS TFamTaxDepreciationPercentage,
                T.TAXFULLPERIODDEPRECIATIONAMOUNT               AS TFamTaxFullPeriodDepreciationAmount,
                T.TAXPARTPERIODDEPRECIATIONAMOUNT               AS TFamTaxPartPeriodDepreciationAmount,
                T.TAXASSETVALUEAFTERDEPRECIATON                 AS TFamTaxAssetValueAfterDepreciation,
                T.DISPOSETYPEID                                 AS DisposeTypeId,
                T.DISPOSEREASONID                               AS DisposeReasonId,
                T.DISPOSEREMARKS                                AS TFamDisposeRemarks,
                T.VOUCHERID                                     AS VoucherId,
                T.VOUCHERNUMBER                                 AS TFamVoucherNumber,
                T.VOUCHERDATE                                   AS TFamVoucherDate,
                T.DEBITACCOUNTID                                AS DebitAccountId,
                T.CREDITACCOUNTID                               AS CreditAccountId,
                T.VERSION                                        AS TFamVersion,
                T.STATUS                                         AS TFamStatus,
                T.CREATEDBYID                                   AS TFamCreatedById,
                T.CREATEDON                                     AS TFamCreatedOn,
                T.MODIFIEDBYID                                  AS TFamModifiedById,
                T.MODIFIEDON                                     AS TFamModifiedOn
            FROM TFAM T
            WHERE T.FAMLEDGERID = @TFamLedgerId;";

        public const string SAVE_TFAM = @"INSERT INTO TFAM (
                OUID, PERIODID, BIZTRANSACTIONTYPEID, OBJECTHEADERTYPEID, OBJECTHEADERID, OBJECTTYPEID, OBJECTID,
                FAMNUMBER, FAMDATE, REFERENCENUMBER, REFERENCEDATE, PARTYID, PARTYBRANCHID,
                PARTYREFERENCENUMBER, PARTYREFERENCEDATE, DEPARTMENTID, STOREID, LOCATIONID, FROMLOCATIONID, TOLOCATIONID,
                INCHARGEID, ISCAPITALIZED, CAPITALIZEDON, SLNO, ASSETID, ASSETCODE, ASSETBATCH, ASSETNAME, REMARKS,
                ASSETSTOCKQUANTITY, ITEMID, SKUID, ASSETQUANTITY, ASSETRATE, ASSETGROSSVALUE, ADDITION, DEDUCTION,
                RETIREMENT, ASSETNETVALUE, COA, RESIDUALPERCENTAGE, RESIDUAL, PREVIOUSDEPRECIATION,
                ASSETVALUEAFTERPREVIOUSDEPRECIATION, DEPRECIATIONID, DEPRECIATIONSTARTDATE, DEPRECIATIONENDDATE,
                DEPRECIATIONDAYS, REMININGLIFEINDAYS, NOOFSHIFTS, DEPRECIATIONPERCENTAGE, CALCULATEDBASEDON,
                DEPRECIATIONAMOUNT, ASSETVALUEAFTERDEPRECIATON, FULLPERIODASSETVALUE, PARTPERIODASSETVALUE,
                TAXDEPRECIATIONID, TAXDEPRECIATIONSTARTDATE, TAXDEPRECIATIONENDDATE, TAXDEPRECIATIONPERCENTAGE,
                TAXFULLPERIODDEPRECIATIONAMOUNT, TAXPARTPERIODDEPRECIATIONAMOUNT, TAXASSETVALUEAFTERDEPRECIATON,
                DISPOSETYPEID, DISPOSEREASONID, DISPOSEREMARKS, VOUCHERID, VOUCHERNUMBER, VOUCHERDATE,
                DEBITACCOUNTID, CREDITACCOUNTID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, FAMID)
            VALUES (
                @OUId, @PeriodId, @BizTransactionTypeId, @ObjectHeaderTypeId, @ObjectHeaderId, @ObjectTypeId, @ObjectId,
                @TFamFamNumber, @TFamFamDate, @TFamReferenceNumber, @TFamReferenceDate, @PartyId, @PartyBranchId,
                @TFamPartyReferenceNumber, @TFamPartyReferenceDate, @DepartmentId, @StoreId, @LocationId, @FromLocationId, @ToLocationId,
                @InchargeId, @TFamIsCapitalized, @TFamCapitalizedOn, @TFamSLNo, @AssetId, @TFamAssetCode, @TFamAssetBatch, @TFamAssetName, @TFamRemarks,
                @TFamAssetStockQuantity, @ItemId, @SKUId, @TFamAssetQuantity, @TFamAssetRate, @TFamAssetGrossValue, @TFamAddition, @TFamDeduction,
                @TFamRetirement, @TFamAssetNetValue, @TFamCOA, @TFamResidualPercentage, @TFamResidual, @TFamPreviousDepreciation,
                @TFamAssetValueAfterPreviousDepreciation, @DepreciationId, @TFamDepreciationStartDate, @TFamDepreciationEndDate,
                @TFamDepreciationDays, @TFamRemainingLifeInDays, @TFamNoOfShifts, @TFamDepreciationPercentage, @TFamCalculatedBasedOn,
                @TFamDepreciationAmount, @TFamAssetValueAfterDepreciation, @TFamFullPeriodAssetValue, @TFamPartPeriodAssetValue,
                @TaxDepreciationId, @TFamTaxDepreciationStartDate, @TFamTaxDepreciationEndDate, @TFamTaxDepreciationPercentage,
                @TFamTaxFullPeriodDepreciationAmount, @TFamTaxPartPeriodDepreciationAmount, @TFamTaxAssetValueAfterDepreciation,
                @DisposeTypeId, @DisposeReasonId, @TFamDisposeRemarks, @VoucherId, @TFamVoucherNumber, @TFamVoucherDate,
                @DebitAccountId, @CreditAccountId, @TFamVersion, @TFamStatus, @TFamCreatedById, @TFamCreatedOn, @TFamModifiedById, @TFamModifiedOn, @TFamFamId);
            SELECT SCOPE_IDENTITY();";

        public const string SOFT_DELETE_TFAM = @"UPDATE TFAM SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE FAMLEDGERID = @TFamLedgerId;";

        // Period validation — confirmed against AdminDAL/Query/Period/PeriodQB.cs (MPERIOD: FROMDATE/TODATE/STATUS).
        public const string GET_PERIOD_WINDOW = @"SELECT FROMDATE AS FromDate, TODATE AS ToDate, STATUS AS Status
            FROM MPERIOD WHERE PERIODID = @PeriodId;";

        // Duplicate-capitalization check (legacy rule): has this AssetId+AssetBatch already been
        // capitalized via a prior TFAM entry? ISCAPITALIZED = 0 means YES per the DDL's own comment
        // ("0-->YES 1->NO 2->NA"). This is TFAM's own rule — distinct from FM's MASSET-level
        // duplicate-batch-uniqueness check (different table, different meaning), so it is NOT routed
        // through IAssetBLL.IsDuplicateAssetBatch.
        public const string CHECK_ASSET_BATCH_ALREADY_CAPITALIZED = @"
            SELECT COUNT(1) FROM TFAM
            WHERE ASSETID = @AssetId
            AND   ASSETBATCH = @AssetBatch
            AND   ISCAPITALIZED = 0
            AND   STATUS = 1;";

        public const string GET_TFAM_SELECTLIST = @"
                                            WITH PagedTFam AS
                                            (
                                                SELECT
                                                    A.FAMID AS FamId,
                                                    A.FAMNUMBER AS FamNumber,
                                                    A.FAMLEDGERID AS LedgerId,
                                                    ROW_NUMBER() OVER (ORDER BY A.FAMID) AS RowNum
                                                FROM TFAM A
                                            )
                                            SELECT
                                                FamId,
                                                FamNumber,
                                                LedgerId
                                            FROM PagedTFam
                                            WHERE
                                                (@firstnumber = -1 AND @maxresult = -1)
                                                OR
                                                (
                                                    RowNum BETWEEN
                                                        (@firstnumber + 1)
                                                        AND
                                                        (@firstnumber + @maxresult)
                                                );";

    }
}