namespace MMDAL.Query.Casting
{
    // Required indexes (add via DB migration):
    //   TCASTING:       IX_TCASTING_OUID_DATE (OUID, CASTINGDATE, TENANTID)
    //   TCASTINGDETAIL: IX_TCASTINGDETAIL_CASTING (CASTINGID, TENANTID)
    public static class CastingQB
    {
        // ── Get casting header ────────────────────────────────────────────────

        public const string GET_CASTING_SQL = @"
            SELECT c.CASTINGID                      AS CastingId,
                   c.CASTINGNUMBER                  AS CastingNumber,
                   c.CASTINGDATE                    AS CastingDate,
                   c.BIZTRANSACTIONTYPEID           AS BizTransactionTypeId,
                   bt.BIZTRANSACTIONTYPENAME        AS BizTransactionTypeName,
                   c.OUID                           AS OUId,
                   ou.OUNAME                        AS OUName,
                   c.PERIODID                       AS PeriodId,
                   pd.PERIODNAME                    AS PeriodName,
                   c.STOREID                        AS StoreId,
                   ISNULL(st.STORENAME, '')         AS StoreName,
                   ISNULL(c.REMARKS, '')            AS Remarks,
                   c.STATUS                         AS Status,
                   c.VERSION                        AS Version,
                   c.CREATEDBYID                    AS CreatedById,
                   c.MODIFIEDBYID                   AS ModifiedById
            FROM   TCASTING c
            JOIN   MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = c.BIZTRANSACTIONTYPEID
                                          AND bt.TENANTID            = c.TENANTID
            JOIN   MORGANIZATIONUNIT ou   ON ou.OUID      = c.OUID
                                          AND ou.TENANTID = c.TENANTID
            JOIN   MPERIOD pd             ON pd.PERIODID  = c.PERIODID
                                          AND pd.TENANTID = c.TENANTID
            LEFT JOIN MSTORE st           ON st.STOREID   = c.STOREID
                                          AND st.TENANTID = c.TENANTID
            WHERE  c.CASTINGID = @CastingId
            AND    c.TENANTID  = @TenantId
            AND    c.STATUS   <> 2;";

        public const string GET_CASTING_PG = @"
            SELECT c.CASTINGID                      AS ""CastingId"",
                   c.CASTINGNUMBER                  AS ""CastingNumber"",
                   c.CASTINGDATE                    AS ""CastingDate"",
                   c.BIZTRANSACTIONTYPEID           AS ""BizTransactionTypeId"",
                   bt.BIZTRANSACTIONTYPENAME        AS ""BizTransactionTypeName"",
                   c.OUID                           AS ""OUId"",
                   ou.OUNAME                        AS ""OUName"",
                   c.PERIODID                       AS ""PeriodId"",
                   pd.PERIODNAME                    AS ""PeriodName"",
                   c.STOREID                        AS ""StoreId"",
                   COALESCE(st.STORENAME, '')       AS ""StoreName"",
                   COALESCE(c.REMARKS, '')          AS ""Remarks"",
                   c.STATUS                         AS ""Status"",
                   c.VERSION                        AS ""Version"",
                   c.CREATEDBYID                    AS ""CreatedById"",
                   c.MODIFIEDBYID                   AS ""ModifiedById""
            FROM   TCASTING c
            JOIN   MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = c.BIZTRANSACTIONTYPEID
                                          AND bt.TENANTID            = c.TENANTID
            JOIN   MORGANIZATIONUNIT ou   ON ou.OUID      = c.OUID
                                          AND ou.TENANTID = c.TENANTID
            JOIN   MPERIOD pd             ON pd.PERIODID  = c.PERIODID
                                          AND pd.TENANTID = c.TENANTID
            LEFT JOIN MSTORE st           ON st.STOREID   = c.STOREID
                                          AND st.TENANTID = c.TENANTID
            WHERE  c.CASTINGID = :CastingId
            AND    c.TENANTID  = :TenantId
            AND    c.STATUS   <> 2;";

        // ── Get casting details ───────────────────────────────────────────────

        public const string GET_CASTING_DETAILS_SQL = @"
            SELECT cd.CASTINGDETAILID               AS CastingDetailId,
                   cd.CASTINGID                     AS CastingId,
                   cd.SLNO                          AS SlNo,
                   cd.PATTERNID                     AS PatternId,
                   ISNULL(pt.PATTERNNAME, '')       AS PatternName,
                   cd.HEATID                        AS HeatId,
                   ISNULL(h.HEATNUMBER, '')         AS HeatNumber,
                   cd.HEATLADLEID                   AS HeatLaddleId,
                   ISNULL(hl.LADDLENUMBER, '')      AS LaddleNumber,
                   ISNULL(cd.BOXNUMBER, '')         AS BoxNumber,
                   cd.BOXSTATUS                     AS BoxStatus,
                   ISNULL(cd.MOULDINGREMARKS, '')   AS MouldingRemarks,
                   cd.TOTALPIECES                   AS TotalPieces,
                   cd.GOODPIECES                    AS GoodPieces,
                   cd.REJECTEDPIECES                AS RejectedPieces,
                   cd.TENANTID                      AS TenantId
            FROM   TCASTINGDETAIL cd
            LEFT JOIN MPATTERN pt      ON pt.PATTERNID  = cd.PATTERNID
                                      AND pt.TENANTID   = cd.TENANTID
            LEFT JOIN THEAT h          ON h.HEATID      = cd.HEATID
                                      AND h.TENANTID    = cd.TENANTID
            LEFT JOIN THEATLADDLE hl   ON hl.HEATLADDLEID = cd.HEATLADLEID
                                      AND hl.TENANTID     = cd.TENANTID
            WHERE  cd.CASTINGID = @CastingId
            AND    cd.TENANTID  = @TenantId
            ORDER BY cd.SLNO;";

        public const string GET_CASTING_DETAILS_PG = @"
            SELECT cd.CASTINGDETAILID               AS ""CastingDetailId"",
                   cd.CASTINGID                     AS ""CastingId"",
                   cd.SLNO                          AS ""SlNo"",
                   cd.PATTERNID                     AS ""PatternId"",
                   COALESCE(pt.PATTERNNAME, '')     AS ""PatternName"",
                   cd.HEATID                        AS ""HeatId"",
                   COALESCE(h.HEATNUMBER, '')       AS ""HeatNumber"",
                   cd.HEATLADLEID                   AS ""HeatLaddleId"",
                   COALESCE(hl.LADDLENUMBER, '')    AS ""LaddleNumber"",
                   COALESCE(cd.BOXNUMBER, '')       AS ""BoxNumber"",
                   cd.BOXSTATUS                     AS ""BoxStatus"",
                   COALESCE(cd.MOULDINGREMARKS, '') AS ""MouldingRemarks"",
                   cd.TOTALPIECES                   AS ""TotalPieces"",
                   cd.GOODPIECES                    AS ""GoodPieces"",
                   cd.REJECTEDPIECES                AS ""RejectedPieces"",
                   cd.TENANTID                      AS ""TenantId""
            FROM   TCASTINGDETAIL cd
            LEFT JOIN MPATTERN pt      ON pt.PATTERNID  = cd.PATTERNID
                                      AND pt.TENANTID   = cd.TENANTID
            LEFT JOIN THEAT h          ON h.HEATID      = cd.HEATID
                                      AND h.TENANTID    = cd.TENANTID
            LEFT JOIN THEATLADDLE hl   ON hl.HEATLADDLEID = cd.HEATLADLEID
                                      AND hl.TENANTID     = cd.TENANTID
            WHERE  cd.CASTINGID = :CastingId
            AND    cd.TENANTID  = :TenantId
            ORDER BY cd.SLNO;";

        // ── Get paged list ────────────────────────────────────────────────────

        public const string GET_CASTING_LIST_SQL = @"
            SELECT c.CASTINGID                      AS CastingId,
                   c.CASTINGNUMBER                  AS CastingNumber,
                   c.CASTINGDATE                    AS CastingDate,
                   ou.OUNAME                        AS OUName,
                   c.STOREID                        AS StoreId,
                   c.STATUS                         AS Status
            FROM   TCASTING c
            JOIN   MORGANIZATIONUNIT ou ON ou.OUID = c.OUID AND ou.TENANTID = c.TENANTID
            WHERE  c.OUID     = @OUId
            AND    c.TENANTID = @TenantId
            AND    c.STATUS  <> 2
            AND    (@FromDate IS NULL OR c.CASTINGDATE >= @FromDate)
            AND    (@ToDate   IS NULL OR c.CASTINGDATE <= @ToDate)
            ORDER BY c.CASTINGDATE DESC
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;";

        public const string GET_CASTING_LIST_PG = @"
            SELECT c.CASTINGID                      AS ""CastingId"",
                   c.CASTINGNUMBER                  AS ""CastingNumber"",
                   c.CASTINGDATE                    AS ""CastingDate"",
                   ou.OUNAME                        AS ""OUName"",
                   c.STOREID                        AS ""StoreId"",
                   c.STATUS                         AS ""Status""
            FROM   TCASTING c
            JOIN   MORGANIZATIONUNIT ou ON ou.OUID = c.OUID AND ou.TENANTID = c.TENANTID
            WHERE  c.OUID     = :OUId
            AND    c.TENANTID = :TenantId
            AND    c.STATUS  <> 2
            AND    (:FromDate::date IS NULL OR c.CASTINGDATE >= :FromDate)
            AND    (:ToDate::date   IS NULL OR c.CASTINGDATE <= :ToDate)
            ORDER BY c.CASTINGDATE DESC
            LIMIT :MaxResult OFFSET :FirstNumber;";

        public const string GET_CASTING_LIST_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   TCASTING c
            WHERE  c.OUID     = @OUId
            AND    c.TENANTID = @TenantId
            AND    c.STATUS  <> 2
            AND    (@FromDate IS NULL OR c.CASTINGDATE >= @FromDate)
            AND    (@ToDate   IS NULL OR c.CASTINGDATE <= @ToDate);";

        public const string GET_CASTING_LIST_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   TCASTING c
            WHERE  c.OUID     = :OUId
            AND    c.TENANTID = :TenantId
            AND    c.STATUS  <> 2
            AND    (:FromDate::date IS NULL OR c.CASTINGDATE >= :FromDate)
            AND    (:ToDate::date   IS NULL OR c.CASTINGDATE <= :ToDate);";

        // ── Save / Update header ──────────────────────────────────────────────

        public const string SAVE_CASTING_SQL = @"
            INSERT INTO TCASTING
                   (CASTINGID, BIZTRANSACTIONTYPEID, OUID, PERIODID, CASTINGNUMBER,
                    CASTINGDATE, STOREID, REMARKS, STATUS, VERSION,
                    CREATEDBYID, MODIFIEDBYID, TENANTID, CREATEDON, MODIFIEDON)
            VALUES (@CastingId, @BizTransactionTypeId, @OUId, @PeriodId, @CastingNumber,
                    @CastingDate, @StoreId, @Remarks, @Status, @Version,
                    @CreatedById, @ModifiedById, @TenantId, GETUTCDATE(), GETUTCDATE());";

        public const string SAVE_CASTING_PG = @"
            INSERT INTO TCASTING
                   (CASTINGID, BIZTRANSACTIONTYPEID, OUID, PERIODID, CASTINGNUMBER,
                    CASTINGDATE, STOREID, REMARKS, STATUS, VERSION,
                    CREATEDBYID, MODIFIEDBYID, TENANTID, CREATEDON, MODIFIEDON)
            VALUES (:CastingId, :BizTransactionTypeId, :OUId, :PeriodId, :CastingNumber,
                    :CastingDate, :StoreId, :Remarks, :Status, :Version,
                    :CreatedById, :ModifiedById, :TenantId, NOW() AT TIME ZONE 'UTC', NOW() AT TIME ZONE 'UTC');";

        public const string UPDATE_CASTING_SQL = @"
            UPDATE TCASTING
            SET    BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
                   OUID                 = @OUId,
                   PERIODID             = @PeriodId,
                   CASTINGDATE          = @CastingDate,
                   STOREID              = @StoreId,
                   REMARKS              = @Remarks,
                   STATUS               = @Status,
                   VERSION              = @Version,
                   MODIFIEDBYID         = @ModifiedById,
                   MODIFIEDON           = GETUTCDATE()
            WHERE  CASTINGID = @CastingId
            AND    TENANTID  = @TenantId;";

        public const string UPDATE_CASTING_PG = @"
            UPDATE TCASTING
            SET    BIZTRANSACTIONTYPEID = :BizTransactionTypeId,
                   OUID                 = :OUId,
                   PERIODID             = :PeriodId,
                   CASTINGDATE          = :CastingDate,
                   STOREID              = :StoreId,
                   REMARKS              = :Remarks,
                   STATUS               = :Status,
                   VERSION              = :Version,
                   MODIFIEDBYID         = :ModifiedById,
                   MODIFIEDON           = NOW() AT TIME ZONE 'UTC'
            WHERE  CASTINGID = :CastingId
            AND    TENANTID  = :TenantId;";

        // ── Save / Delete details ─────────────────────────────────────────────

        public const string SAVE_CASTING_DETAIL_SQL = @"
            INSERT INTO TCASTINGDETAIL
                   (CASTINGDETAILID, CASTINGID, SLNO, PATTERNID, HEATID, HEATLADLEID,
                    BOXNUMBER, BOXSTATUS, MOULDINGREMARKS, TOTALPIECES, GOODPIECES,
                    REJECTEDPIECES, TENANTID)
            VALUES (@CastingDetailId, @CastingId, @SlNo, @PatternId, @HeatId, @HeatLaddleId,
                    @BoxNumber, @BoxStatus, @MouldingRemarks, @TotalPieces, @GoodPieces,
                    @RejectedPieces, @TenantId);";

        public const string SAVE_CASTING_DETAIL_PG = @"
            INSERT INTO TCASTINGDETAIL
                   (CASTINGDETAILID, CASTINGID, SLNO, PATTERNID, HEATID, HEATLADLEID,
                    BOXNUMBER, BOXSTATUS, MOULDINGREMARKS, TOTALPIECES, GOODPIECES,
                    REJECTEDPIECES, TENANTID)
            VALUES (:CastingDetailId, :CastingId, :SlNo, :PatternId, :HeatId, :HeatLaddleId,
                    :BoxNumber, :BoxStatus, :MouldingRemarks, :TotalPieces, :GoodPieces,
                    :RejectedPieces, :TenantId);";

        public const string DELETE_CASTING_DETAILS_SQL = @"
            DELETE FROM TCASTINGDETAIL
            WHERE  CASTINGID = @CastingId
            AND    TENANTID  = @TenantId;";

        public const string DELETE_CASTING_DETAILS_PG = @"
            DELETE FROM TCASTINGDETAIL
            WHERE  CASTINGID = :CastingId
            AND    TENANTID  = :TenantId;";

        // ── Soft delete ───────────────────────────────────────────────────────

        public const string SOFT_DELETE_CASTING_SQL = @"
            UPDATE TCASTING
            SET    STATUS = 2,
                   MODIFIEDON = GETUTCDATE()
            WHERE  CASTINGID = @CastingId
            AND    TENANTID  = @TenantId;";

        public const string SOFT_DELETE_CASTING_PG = @"
            UPDATE TCASTING
            SET    STATUS = 2,
                   MODIFIEDON = NOW() AT TIME ZONE 'UTC'
            WHERE  CASTINGID = :CastingId
            AND    TENANTID  = :TenantId;";
    }
}