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

namespace AccountsDAL.Query.Budget
{
    public static class BudgetQB
    {
        public const string GET_SELECTLIST_BUDGET_SQL = @"
                                                    WITH Budget AS (
                                                        SELECT
                                                            b.BUDGETID    AS Id,
                                                            b.BUDGETCODE  AS Code,
                                                            b.BUDGETNAME  AS Name,
                                                            b.ACCOUNTLEVEL AS AccountLevel,
                                                            b.PERIODTYPE  AS PeriodType,
                                                            b.FROMDATE    AS FromDate,
                                                            b.TODATE      AS ToDate,
                                                            ROW_NUMBER() OVER (ORDER BY b.BUDGETID) AS RowNum
                                                        FROM MBUDGET b
                                                        WHERE b.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name,
                                                        AccountLevel,
                                                        PeriodType,
                                                        FromDate,
                                                        ToDate
                                                    FROM Budget
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string GET_SELECTLIST_BUDGET_PG = @"
                                                    WITH Budget AS (
                                                        SELECT
                                                            b.BUDGETID    AS ""Id"",
                                                            b.BUDGETCODE  AS ""Code"",
                                                            b.BUDGETNAME  AS ""Name"",
                                                            b.ACCOUNTLEVEL AS ""AccountLevel"",
                                                            b.PERIODTYPE  AS ""PeriodType"",
                                                            b.FROMDATE    AS ""FromDate"",
                                                            b.TODATE      AS ""ToDate"",
                                                            ROW_NUMBER() OVER (ORDER BY b.BUDGETID) AS ""RowNum""
                                                        FROM MBUDGET b
                                                        WHERE b.STATUS = 1
                                                    )
                                                    SELECT
                                                        ""Id"",
                                                        ""Code"",
                                                        ""Name"",
                                                        ""AccountLevel"",
                                                        ""PeriodType"",
                                                        ""FromDate"",
                                                        ""ToDate""
                                                    FROM Budget
                                                    WHERE
                                                          (:firstnumber = -1 AND :maxresult = -1)
                                                       OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
                                                    ";
        public const string GET_SELECTLIST_BUDGET_ORACLE = @";";
        public const string GET_SELECTLIST_BUDGET_MYSQL = @";";

        // ── Save (minimal Budget vs Actual Comparison CRUD) ────────────────────────
        // MBUDGET/MBUDGETDATA/MBUDGETDATADETAIL have no IDENTITY column — IDs are
        // allocated up front via AutoNumber (reusing the already-live "BUDGET"/
        // "BUDGETDATA" MAUTONUMBER entity codes; "BUDGETDATADETAIL" seeded new).

        public const string SAVE_BUDGET_SQL = @"
INSERT INTO MBUDGET
    (BUDGETID, BUDGETCODE, BUDGETNAME, ACCOUNTLEVEL, CRITERIACONFIGID, COSTCATEGORYID,
     PERIODTYPE, GBPERIODID, COMPAREGBPERIODID, FROMDATE, TODATE, COMPAREFROMDATE, COMPARETODATE,
     REMARKS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION,
     SOURCETYPE, TENANTID)
VALUES
    (@BudgetId, @BudgetCode, @BudgetName, @BudgetAccountLevel, @CriteriaConfigId, @CostCategoryId,
     @BudgetPeriodType, @GBPeriodId, @CompareGBPeriodId, @BudgetFromDate, @BudgetToDate,
     @BudgetCompareFromDate, @BudgetCompareToDate, @BudgetRemarks, @BudgetCreatedById,
     @BudgetCreatedOn, @BudgetModifiedById, @BudgetModifiedOn, @BudgetSortOrder, @BudgetStatus,
     @BudgetVersion, @BudgetSourceType, @TenantId)";

        public const string SAVE_BUDGET_PG = @"
INSERT INTO MBUDGET
    (BUDGETID, BUDGETCODE, BUDGETNAME, ACCOUNTLEVEL, CRITERIACONFIGID, COSTCATEGORYID,
     PERIODTYPE, GBPERIODID, COMPAREGBPERIODID, FROMDATE, TODATE, COMPAREFROMDATE, COMPARETODATE,
     REMARKS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION,
     SOURCETYPE, TENANTID)
VALUES
    (:BudgetId, :BudgetCode, :BudgetName, :BudgetAccountLevel, :CriteriaConfigId, :CostCategoryId,
     :BudgetPeriodType, :GBPeriodId, :CompareGBPeriodId, :BudgetFromDate, :BudgetToDate,
     :BudgetCompareFromDate, :BudgetCompareToDate, :BudgetRemarks, :BudgetCreatedById,
     :BudgetCreatedOn, :BudgetModifiedById, :BudgetModifiedOn, :BudgetSortOrder, :BudgetStatus,
     :BudgetVersion, :BudgetSourceType, :TenantId)";

        public const string SAVE_BUDGETDATA_SQL = @"
INSERT INTO MBUDGETDATA
    (BUDGETDATAID, BUDGETID, OUID, GBPERIODDETAILID, FROMDATE, TODATE, COMPAREGBPERIODDETAILID,
     COMPAREFROMDATE, COMPARETODATE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER,
     STATUS, VERSION, SOURCETYPE)
VALUES
    (@BudgetDataId, @BudgetId, @OUId, @GBPeriodDetailId, @BudgetDataFromDate, @BudgetDataToDate,
     @CompareGBPeriodDetailId, @BudgetDataCompareFromDate, @BudgetDataCompareToDate,
     @BudgetDataCreatedById, @BudgetDataCreatedOn, @BudgetDataModifiedById, @BudgetDataModifiedOn,
     @BudgetDataSortOrder, @BudgetDataStatus, @BudgetDataVersion, @BudgetDataSourceType)";

        public const string SAVE_BUDGETDATA_PG = @"
INSERT INTO MBUDGETDATA
    (BUDGETDATAID, BUDGETID, OUID, GBPERIODDETAILID, FROMDATE, TODATE, COMPAREGBPERIODDETAILID,
     COMPAREFROMDATE, COMPARETODATE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER,
     STATUS, VERSION, SOURCETYPE)
VALUES
    (:BudgetDataId, :BudgetId, :OUId, :GBPeriodDetailId, :BudgetDataFromDate, :BudgetDataToDate,
     :CompareGBPeriodDetailId, :BudgetDataCompareFromDate, :BudgetDataCompareToDate,
     :BudgetDataCreatedById, :BudgetDataCreatedOn, :BudgetDataModifiedById, :BudgetDataModifiedOn,
     :BudgetDataSortOrder, :BudgetDataStatus, :BudgetDataVersion, :BudgetDataSourceType)";

        public const string SAVE_BUDGETDATADETAIL_SQL = @"
INSERT INTO MBUDGETDATADETAIL
    (BUDGETDATADETAILID, BUDGETDATAID, SLNO, ACCOUNTGROUPID, ACCOUNTID, COSTCENTERID, TYPE, AMOUNT)
VALUES
    (@BudgetDataDetailId, @BudgetDataId, @BudgetDataDetailSlNo, @AccountGroupId, @AccountId,
     @CostCenterId, @BudgetDataDetailType, @BudgetDataDetailAmount)";

        public const string SAVE_BUDGETDATADETAIL_PG = @"
INSERT INTO MBUDGETDATADETAIL
    (BUDGETDATADETAILID, BUDGETDATAID, SLNO, ACCOUNTGROUPID, ACCOUNTID, COSTCENTERID, TYPE, AMOUNT)
VALUES
    (:BudgetDataDetailId, :BudgetDataId, :BudgetDataDetailSlNo, :AccountGroupId, :AccountId,
     :CostCenterId, :BudgetDataDetailType, :BudgetDataDetailAmount)";
    }
}