using Google.Protobuf.Compiler;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;
using static GB5Shared.DTO.GOP.GBGatewayDTO;

namespace WiDAL.Query.Wi
{
    public class ProductionLineQB
    {
        public const string GET_BY_ID = @"SELECT
                                            pl.LINEID          AS ProductionLineId,
                                            pl.LINECODE        AS ProductionLineCode,
                                            pl.LINENAME        AS ProductionLineName,
                                            pl.DESCRIPTION     AS ProductionLineDescription,
                                            pl.VERSION         AS ProductionLineVersion,
                                            pl.STATUS          AS ProductionLineStatus,
                                            pl.SORTORDER       AS ProductionLineSortOrder,
                                            pl.CREATEDBYID     AS ProductionLineCreatedById,
                                            pl.CREATEDON       AS ProductionLineCreatedOn,
                                            pl.MODIFIEDBYID    AS ProductionLineModifiedById,
                                            pl.MODIFIEDON      AS ProductionLineModifiedOn,
                                            pl.SOURCETYPE      AS ProductionLineSoruceType,
                                            pl.TENANTID        AS ProductionLineTenantId
                                        FROM DBO.MPRODUCTIONLINE pl
                                        WHERE LINEID =@productionLineId;";

        public const string SAVE_PRODUCTIONLINE = @"INSERT INTO DBO.MPRODUCTIONLINE
                                                (
                                                    LINEID,
                                                    LINECODE,
                                                    LINENAME,
                                                    DESCRIPTION,
                                                    VERSION,
                                                    STATUS,
                                                    SORTORDER,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE,
                                                    TENANTID
                                                )
                                                VALUES
                                                (
                                                    @ProductionLineId,
                                                    @ProductionLineCode,
                                                    @ProductionLineName,
                                                    @ProductionLineDescription,
                                                    @ProductionLineVersion,
                                                    @ProductionLineStatus,
                                                    @ProductionLineSortOrder,
                                                    @ProductionLineCreatedById,
                                                    GETDATE(),
                                                    @ProductionLineModifiedById,
                                                    GETDATE(),
                                                    @ProductionLineSoruceType,
                                                    @ProductionLineTenantId
                                                );";

        public const string UPDATE_PRODUCTIONLINE = @"UPDATE DBO.MPRODUCTIONLINE
                                                    SET
                                                        LINECODE     = @ProductionLineCode,
                                                        LINENAME     = @ProductionLineName,
                                                        DESCRIPTION  = @ProductionLineDescription,
                                                        VERSION      = @ProductionLineVersion,
                                                        STATUS       = @ProductionLineStatus,
                                                        SORTORDER    = @ProductionLineSortOrder,
                                                        MODIFIEDBYID = @ProductionLineModifiedById,
                                                        MODIFIEDON   = GETDATE(),
                                                        SOURCETYPE   = @ProductionLineSoruceType,
                                                        TENANTID     = @ProductionLineTenantId
                                                    WHERE LINEID = @ProductionLineId;";

        public const string DELETE_PRODUCTIONLINE = @"DELETE FROM DBO.MPRODUCTIONLINE
                                                        WHERE LINEID =@productionLineId;";

        public const string GET_SELECTLIST_PRODUCTIONLINE = @"SELECT
                                                            LINEID AS Id,
                                                            LINECODE AS Code,
                                                            LINENAME AS Name
                                                            FROM MPRODUCTIONLINE";

        // Plant Overview — tenant-scoped line list (the pre-existing selectlist/list queries above
        // have no TENANTID filter; this is a new, deliberately tenant-filtered query, not a fix to those).
        public const string GET_LINES_FOR_TENANT = @"
            SELECT LINEID   AS LineId,
                   LINECODE AS LineCode,
                   LINENAME AS LineName
            FROM   MPRODUCTIONLINE
            WHERE  TENANTID = @TenantId
              AND  STATUS   = 1
            ORDER BY LINENAME";

        public const string GET_LIST_PRODUCTIONLINE = @"SELECT
                                                            -- Production Line DTO
                                                            PL.LINEID          AS LineId,
                                                            PL.LINECODE        AS LineCode,
                                                            PL.LINENAME        AS LineName,
                                                            PL.DESCRIPTION     AS Description,
                                                            PL.VERSION         AS Version,
                                                            PL.STATUS          AS Status,
                                                            PL.SORTORDER       AS SortOrder,
                                                            PL.CREATEDBYID     AS CreatedById,
                                                            PL.CREATEDON       AS CreatedOn,
                                                            PL.MODIFIEDBYID    AS ModifiedById,
                                                            PL.MODIFIEDON      AS ModifiedOn,
                                                            PL.SOURCETYPE      AS SourceType,
                                                            PL.TENANTID        AS TenantId,
                                                                -- Production Line Station DTO
                                                                PLS.LINESTATIONID  AS LineStationId,
                                                                PLS.LINEID         AS LineId,
                                                                PLS.WORKSTATIONID  AS WorkStationId,
                                                                PLS.POSITIONNO     AS PositionNo,
                                                                PLS.EFFECTIVEFROM  AS EffectiveFrom,
                                                                PLS.EFFECTIVETO    AS EffectiveTo,
                                                                PLS.REMARKS        AS Remarks,
                                                                PLS.CREATEDBYID    AS CreatedById,
                                                                PLS.CREATEDON      AS CreatedOn,
                                                            -- WorkStation DTO
                                                            WS.WORKSTATIONCODE AS WorkStationCode,
                                                            WS.WORKSTATIONNAME AS WorkStationName
                                                        FROM MPRODUCTIONLINE PL
                                                        LEFT JOIN MPRODUCTIONLINESTATION PLS ON PL.LINEID = PLS.LINEID
                                                        LEFT JOIN MWORKSTATION WS ON PLS.WORKSTATIONID = WS.WORKSTATIONID
                                                        ORDER BY
                                                            PL.LINEID,
                                                            PLS.POSITIONNO";
    }
}