namespace Data.Sql
{
    public static class Scripts
    {
        #region Projects
        /// <summary>
        /// sql to insert a club member details
        /// </summary>
        public static readonly string SqlInsertProject = "Insert Into Projects" +
            " ([ProjectName], [OutPath], [Created], [LastUpdated]," +
            " [HologramWidth], [HologramHeight], [FrameWidth], [FrameHeight]," +
            " [FrameResolutionX], [FrameResolutionY], [GrayRangeHigh], [GrayRangeLow])" +
            " Values(@ProjectName, @OutPath, @Created, @LastUpdated, @HologramWidth, @HologramHeight," +
            " @FrameWidth, @FrameHeight, @FrameResolutionX, @FrameResolutionY, @GrayRangeHigh, @GrayRangeLow)";

        //public static readonly string SqlGetProjectIdentity = "SELECT CAST(scope_identity() AS int)";
        public static readonly string SqlGetLastProject = "SELECT * FROM PROJECTS WHERE ID = IDENT_CURRENT('PROJECTS')";
        //public static readonly string SqlGetProjectIdentity = "SET @id=SCOPE_IDENTITY()";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlUpdateProject = "Update Projects " +
            " Set [ProjectName] = @ProjectName, [OutPath] = @OutPath, [LastUpdated] = @LastUpdated, " +
            " [HologramWidth] = @HologramWidth, [HologramHeight] = @HologramHeight, " +
            " [FrameWidth] = @FrameWidth, [FrameHeight] = @FrameHeight, " +
            " [FrameResolutionX] = @FrameResolutionX, [FrameResolutionY] = @FrameResolutionY," +
            " [GrayRangeHigh] = @GrayRangeHigh, [GrayRangeLow] = @GrayRangeLow" +
            " Where([Id] = @Id)";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlUpdateProjectDefault = "Update Projects " +
            " Set [OutPath] = @OutPath, [LastUpdated] = @LastUpdated, " +
            " [HologramWidth] = @HologramWidth, [HologramHeight] = @HologramHeight, " +
            " [FrameWidth] = @FrameWidth, [FrameHeight] = @FrameHeight, " +
            " [FrameResolutionX] = @FrameResolutionX, [FrameResolutionY] = @FrameResolutionY," +
            " [GrayRangeHigh] = @GrayRangeHigh, [GrayRangeLow] = @GrayRangeLow" +
            " Where([ProjectName] = @ProjectName)";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlUpdateProjectOutPath = "Update Projects " +
            " Set [OutPath] = @OutPath Where([Id] = @Id)";

        /// <summary>
        /// Sql to get a project details by Id
        /// </summary>
        public static readonly string sqlGetProjectById = "Select" +
            " Id, [ProjectName], [OutPath], [Created], [LastUpdated]," +
            " [HologramWidth], [HologramHeight], [FrameWidth], [FrameHeight]," +
            " [FrameResolutionX], [FrameResolutionY], [GrayRangeHigh], [GrayRangeLow]" +
            " From Projects Where Id = @Id";

        /// <summary>
        /// Sql to get a project details by name
        /// </summary>
        public static readonly string sqlGetProjectByName = "Select" +
            " Id, [ProjectName], [OutPath], [Created], [LastUpdated]," +
            " [HologramWidth], [HologramHeight], [FrameWidth], [FrameHeight]," +
            " [FrameResolutionX], [FrameResolutionY], [GrayRangeHigh], [GrayRangeLow]" +
            " From Projects Where [ProjectName] = @ProjectName";

        /// <summary>
        /// Sql to get all club members
        /// </summary>
        public static readonly string SqlGetAllProjects = "Select" +
            " Id, [ProjectName], [OutPath], [Created], [LastUpdated], " +
            " [HologramWidth], [HologramHeight], [FrameWidth], [FrameHeight]," +
            " [FrameResolutionX], [FrameResolutionY], [GrayRangeHigh], [GrayRangeLow]" +
            " From Projects";

        /// <summary>
        /// sql to delete a project record
        /// </summary>
        public static readonly string SqlDeleteProject = "Delete From Projects Where (Id = @Id)";


        /// <summary>
        /// sql to delete a project record
        /// </summary>
        public static readonly string SqlDeleteProjectLayers = "Delete From Layers Where (ProjectId = @ProjectId)";

        #endregion

        #region Layers

        public static readonly string SqlGetCountOfLayers = "select count(*) from Layers where ProjectId = @ProjectId";

        /// <summary>
        /// Sql to get all club members
        /// </summary>
        public static readonly string SqlGetProjectLayers = "Select" +
            " Id, [Order], [LayerName], [TechnologyName], [OpticalSchema], [ProjectId]," +
            " [LayerLeft], [LayerTop], [LayerWidth], [LayerHeight]," +
            " [ArcWidth], [RadiusMax], [Step], [SourceFilePath], [AnglesFilePath]" +
            " From Layers Where [ProjectId] = @ProjectId Order by [Order] asc";

        /// <summary>
        /// sql to insert a club member details
        /// </summary>
        public static readonly string SqlInsertLayer = "Insert Into Layers" +
            " ([LayerName], [Order], [TechnologyName], [OpticalSchema], [ProjectId]," +
            " [LayerLeft], [LayerTop], [LayerWidth], [LayerHeight]," +
            " [ArcWidth], [RadiusMax], [Step], [SourceFilePath], [AnglesFilePath])" +
            " Values(@LayerName, @Order, @TechnologyName, @OpticalSchema, @ProjectId," +
            " @LayerLeft, @LayerTop, @LayerWidth, @LayerHeight," +
            " @ArcWidth, @RadiusMax, @Step, @SourceFilePath, @AnglesFilePath)";

        public static readonly string SqlGetLastLayer = "SELECT * FROM LAYERS WHERE ID = IDENT_CURRENT('LAYERS')";

        /// <summary>
        /// Sql to get a layer details by Id
        /// </summary>
        public static readonly string sqlGetLayerById = "Select" +
            " Id, [LayerName], [Order], [TechnologyName], [OpticalSchema], [ProjectId]," +
            " [LayerLeft], [LayerTop], [LayerWidth], [LayerHeight]," +
            " [ArcWidth], [RadiusMax], [Step], [SourceFilePath], [AnglesFilePath]" +
            " From Layers Where Id = @Id";

        /// <summary>
        /// Sql to get a layer details by Id
        /// </summary>
        public static readonly string sqlGetLayerByName = "Select" +
            " Id, [LayerName], [Order], [TechnologyName], [OpticalSchema], [ProjectId]," +
            " [LayerLeft], [LayerTop], [LayerWidth], [LayerHeight]," +
            " [ArcWidth], [RadiusMax], [Step], [SourceFilePath], [AnglesFilePath]" +
            " From Layers Where LayerName = @LayerName";

        /// <summary>
        /// Sql to get a layer details by Id
        /// </summary>
        public static readonly string sqlGetLayerByNameTechnology = "Select" +
            " Id, [LayerName], [Order], [TechnologyName], [OpticalSchema], [ProjectId]," +
            " [LayerLeft], [LayerTop], [LayerWidth], [LayerHeight]," +
            " [ArcWidth], [RadiusMax], [Step], [SourceFilePath], [AnglesFilePath]" +
            " From Layers Where LayerName = @LayerName And TechnologyName = @Technology";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlUpdateLayer = "Update Layers " +
            " Set [LayerName] = @LayerName, [Order] = @Order, [TechnologyName] = @TechnologyName, " +
            " [OpticalSchema] = @OpticalSchema, [ProjectId] = @ProjectId, " +
            " [LayerLeft] = @LayerLeft, [LayerTop] = @LayerTop, [LayerWidth] = @LayerWidth, [LayerHeight] = @LayerHeight," +
            " [ArcWidth] = @ArcWidth, [RadiusMax] = @RadiusMax, [Step] = @Step, " +
            " [SourceFilePath] = @SourceFilePath, [AnglesFilePath] = @AnglesFilePath" +
            " Where([Id] = @Id)";

        /// <summary>
        /// sql to delete a project record
        /// </summary>
        public static readonly string SqlDeleteLayer = "Delete From Layers Where (Id = @Id)";

        /// <summary>
        /// sql to delete a project record
        /// </summary>
        public static readonly string SqlDeleteColorProfileByLayerId = "Delete From ColorProfiles Where (LayerId = @LayerId)";

        #endregion

        #region ColorProfiles

        public static readonly string SqlGetCountOfColorProfiles = "select count(*) from ColorProfiles where LayerId = @LayerId";

        /// <summary>
        /// Sql to get all club members
        /// </summary>
        public static readonly string SqlGetLayerColorProfiles = "Select" +
            " Id, [ProfileName], [TraceProfile], [KeyPoints], [ArcWidth], [LayerId]" +
            " From ColorProfiles Where [LayerId] = @LayerId";

        /// <summary>
        /// Sql to get all club members
        /// </summary>
        public static readonly string SqlGetColorProfile = "Select" +
            " Id, [ProfileName], [TraceProfile], [KeyPoints], [ArcWidth], [LayerId]" +
            " From ColorProfiles Where [Id] = @Id";

        /// <summary>
        /// Sql to get all club members
        /// </summary>
        public static readonly string SqlGetColorProfileByName = "Select" +
            " Id, [ProfileName], [TraceProfile], [KeyPoints], [ArcWidth], [LayerId]" +
            " From ColorProfiles Where [ColorProfileName] = @ColorProfileName";

        /// <summary>
        /// Sql to get a layer details by Id
        /// </summary>
        public static readonly string sqlGetColorProfileByLayerNameTechnology = "Select" +
            " Id, [ProfileName], [TraceProfile], [KeyPoints], [ArcWidth], [LayerId]" +
            " From ColorProfiles where LayerId in (select Id from Layers Where LayerName = @LayerName And TechnologyName = @Technology)";

        /// <summary>
        /// sql to insert a club member details
        /// </summary>
        public static readonly string SqlInsertColorProfile = "Insert Into ColorProfiles" +
            " ([ProfileName], [TraceProfile], [KeyPoints], [ArcWidth], [LayerId])" +
            " Values(@ProfileName, @TraceProfile, @KeyPoints, @ArcWidth, @LayerId)";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlUpdateColorProfile = "Update ColorProfiles " +
            " Set [ProfileName] = @ProfileName, [TraceProfile] = @TraceProfile, " +
            " [KeyPoints] = @KeyPoints, [ArcWidth] = @ArcWidth, [LayerId] = @LayerId " +
            " Where([Id] = @Id)";

        /// <summary>
        /// sql to update project details
        /// </summary>
        public static readonly string SqlDeleteColorProfile = "Delete From ColorProfiles Where (Id = @Id)";

        /// <summary>
        /// sql delete from ColorProfiled where LayerId belong to Project with ProjectId
        /// </summary>
        public static readonly string SqlDeleteProjectColorProfiles = "DELETE FROM ColorProfiles WHERE LayerId IN " +
                        "(SELECT id from Layers where ProjectId = @ProjectId)";

        public static readonly string SqlGetLastColorProfile = "SELECT * FROM COLORPROFILES WHERE ID = IDENT_CURRENT('COLORPROFILES')";

        #endregion
    }
}