USE [AOL_MASTER_CHECKLIST]
GO

/****** Object:  Table [dbo].[ChecklistMaster]    Script Date: 7/19/2026 7:14:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChecklistMaster](
	[ChecklistId] [int] IDENTITY(1,1) NOT NULL,
	[ChecklistNumber] [nvarchar](100) NOT NULL,
	[ChecklistName] [nvarchar](500) NOT NULL,
	[ChecklistType] [nvarchar](100) NULL,
	[Frequency] [nvarchar](100) NULL,
	[SOPNumber] [nvarchar](100) NULL,
	[Department] [nvarchar](200) NULL,
	[VersionNo] [nvarchar](50) NULL,
	[EffectiveDate] [date] NULL,
	[Status] [nvarchar](50) NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ModifiedBy] [nvarchar](100) NULL,
	[ModifiedDate] [datetime] NULL,
	[ExtraText1] [nvarchar](500) NULL,
	[ExtraText2] [nvarchar](500) NULL,
	[ExtraText3] [nvarchar](500) NULL,
	[ExtraText4] [nvarchar](500) NULL,
	[ExtraDate1] [datetime] NULL,
	[ExtraDate2] [datetime] NULL,
	[ExtraDate3] [datetime] NULL,
	[ExtraDate4] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[ChecklistId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChecklistMaster] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO


USE [AOL_MASTER_CHECKLIST]
GO

/****** Object:  Table [dbo].[ChecklistItemMaster]    Script Date: 7/19/2026 7:14:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChecklistItemMaster](
	[ItemId] [int] IDENTITY(1,1) NOT NULL,
	[ChecklistId] [int] NOT NULL,
	[SequenceNo] [int] NULL,
	[CheckPoints] [nvarchar](max) NULL,
	[ResponseType] [nvarchar](50) NULL,
	[IsMandatory] [bit] NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ExtraText1] [nvarchar](500) NULL,
	[ExtraText2] [nvarchar](500) NULL,
	[ExtraText3] [nvarchar](500) NULL,
	[ExtraText4] [nvarchar](500) NULL,
	[ExtraDate1] [datetime] NULL,
	[ExtraDate2] [datetime] NULL,
	[ExtraDate3] [datetime] NULL,
	[ExtraDate4] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChecklistItemMaster] ADD  DEFAULT ((1)) FOR [IsMandatory]
GO

ALTER TABLE [dbo].[ChecklistItemMaster] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[ChecklistItemMaster]  WITH CHECK ADD  CONSTRAINT [FK_ChecklistItemMaster] FOREIGN KEY([ChecklistId])
REFERENCES [dbo].[ChecklistMaster] ([ChecklistId])
GO

ALTER TABLE [dbo].[ChecklistItemMaster] CHECK CONSTRAINT [FK_ChecklistItemMaster]
GO



USE [AOL_MASTER_CHECKLIST]
GO

/****** Object:  Table [dbo].[ChecklistMasterEquipment]    Script Date: 7/19/2026 7:15:05 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChecklistMasterEquipment](
	[ChecklistEquipmentId] [int] IDENTITY(1,1) NOT NULL,
	[ChecklistId] [int] NOT NULL,
	[EquipmentCode] [nvarchar](100) NULL,
	[EquipmentName] [nvarchar](500) NULL,
	[EquipmentType] [nvarchar](100) NULL,
	[Department] [nvarchar](200) NULL,
	[Location] [nvarchar](300) NULL,
	[Frequency] [nvarchar](100) NULL,
	[PMDate] [date] NULL,
	[NextPMDate] [date] NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ExtraText1] [nvarchar](500) NULL,
	[ExtraText2] [nvarchar](500) NULL,
	[ExtraText3] [nvarchar](500) NULL,
	[ExtraText4] [nvarchar](500) NULL,
	[ExtraDate1] [datetime] NULL,
	[ExtraDate2] [datetime] NULL,
	[ExtraDate3] [datetime] NULL,
	[ExtraDate4] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[ChecklistEquipmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChecklistMasterEquipment] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[ChecklistMasterEquipment]  WITH CHECK ADD  CONSTRAINT [FK_ChecklistEquipment] FOREIGN KEY([ChecklistId])
REFERENCES [dbo].[ChecklistMaster] ([ChecklistId])
GO

ALTER TABLE [dbo].[ChecklistMasterEquipment] CHECK CONSTRAINT [FK_ChecklistEquipment]
GO



USE [AOL_MASTER_CHECKLIST]
GO

/****** Object:  Table [dbo].[ChecklistExecution]    Script Date: 7/19/2026 7:15:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChecklistExecution](
	[ExecutionId] [int] IDENTITY(1,1) NOT NULL,
	[ChecklistId] [int] NOT NULL,
	[ChecklistEquipmentId] [int] NOT NULL,
	[ExecutionNumber] [nvarchar](100) NULL,
	[PerformedBy] [nvarchar](100) NULL,
	[PerformedDate] [datetime] NULL,
	[Remarks] [nvarchar](max) NULL,
	[ApprovedBy] [nvarchar](100) NULL,
	[ApprovedDate] [datetime] NULL,
	[ApprovalStatus] [nvarchar](50) NULL,
	[CreatedDate] [datetime] NULL,
	[ExtraText1] [nvarchar](500) NULL,
	[ExtraText2] [nvarchar](500) NULL,
	[ExtraText3] [nvarchar](500) NULL,
	[ExtraText4] [nvarchar](500) NULL,
	[ExtraDate1] [datetime] NULL,
	[ExtraDate2] [datetime] NULL,
	[ExtraDate3] [datetime] NULL,
	[ExtraDate4] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[ExecutionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChecklistExecution] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[ChecklistExecution]  WITH CHECK ADD  CONSTRAINT [FK_Execution_Checklist] FOREIGN KEY([ChecklistId])
REFERENCES [dbo].[ChecklistMaster] ([ChecklistId])
GO

ALTER TABLE [dbo].[ChecklistExecution] CHECK CONSTRAINT [FK_Execution_Checklist]
GO

ALTER TABLE [dbo].[ChecklistExecution]  WITH CHECK ADD  CONSTRAINT [FK_Execution_Equipment] FOREIGN KEY([ChecklistEquipmentId])
REFERENCES [dbo].[ChecklistMasterEquipment] ([ChecklistEquipmentId])
GO

ALTER TABLE [dbo].[ChecklistExecution] CHECK CONSTRAINT [FK_Execution_Equipment]
GO



USE [AOL_MASTER_CHECKLIST]
GO

/****** Object:  Table [dbo].[ChecklistExecutionDetails]    Script Date: 7/19/2026 7:16:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChecklistExecutionDetails](
	[DetailId] [int] IDENTITY(1,1) NOT NULL,
	[ExecutionId] [int] NOT NULL,
	[ItemId] [int] NOT NULL,
	[Observation] [nvarchar](max) NULL,
	[Remarks] [nvarchar](max) NULL,
	[Completed] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ExtraText1] [nvarchar](500) NULL,
	[ExtraText2] [nvarchar](500) NULL,
	[ExtraText3] [nvarchar](500) NULL,
	[ExtraText4] [nvarchar](500) NULL,
	[ExtraDate1] [datetime] NULL,
	[ExtraDate2] [datetime] NULL,
	[ExtraDate3] [datetime] NULL,
	[ExtraDate4] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[DetailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChecklistExecutionDetails] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[ChecklistExecutionDetails]  WITH CHECK ADD  CONSTRAINT [FK_Details_Execution] FOREIGN KEY([ExecutionId])
REFERENCES [dbo].[ChecklistExecution] ([ExecutionId])
GO

ALTER TABLE [dbo].[ChecklistExecutionDetails] CHECK CONSTRAINT [FK_Details_Execution]
GO

ALTER TABLE [dbo].[ChecklistExecutionDetails]  WITH CHECK ADD  CONSTRAINT [FK_Details_Item] FOREIGN KEY([ItemId])
REFERENCES [dbo].[ChecklistItemMaster] ([ItemId])
GO

ALTER TABLE [dbo].[ChecklistExecutionDetails] CHECK CONSTRAINT [FK_Details_Item]
GO