
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ref_cat')
BEGIN
    CREATE TABLE [dbo].[ref_cat](
	[site_cd] [varchar](4) NOT NULL,
	[ref_cat_module] [varchar](100) NULL,
	[ref_cat_cat_cd] [varchar](30) NOT NULL,
	[ref_cat_desc] [varchar](255) NULL,
	[ref_cat_disable_flag] [varchar](1) NULL,
	[audit_user] [varchar](50) NULL,
	[audit_date] [datetime] NOT NULL,
	[column1] [varchar](60) NULL,
	[column2] [varchar](60) NULL,
	[column3] [varchar](60) NULL,
	[column4] [varchar](60) NULL,
	[column5] [varchar](60) NULL,
	[RowID] [numeric](12, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END

IF NOT EXISTS (
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('ref_cat') 
    AND name = 'ref_cat_cat_cd'
)
BEGIN
    ALTER TABLE ref_cat
    ADD ref_cat_cat_cd varchar(100) NULL
END


DELETE	cf_label
WHERE	table_name = 'ref_cat'
AND 	column_name = 'ref_cat_cat_cd'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'ref_cat', 'ref_cat_cat_cd', 
		'Category Code:', 
		'Category Code:', 
		'Category Code', 
		'Category Code', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

IF NOT EXISTS (
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('ref_cat') 
    AND name = 'ref_cat_desc'
)
BEGIN
    ALTER TABLE wkr_det
    ADD wkr_det_email_id varchar(100) NULL
END


DELETE	cf_label
WHERE	table_name = 'ref_cat'
AND 	column_name = 'ref_cat_desc'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'ref_cat', 'ref_cat_desc', 
		'Description:', 
		'Description:', 
		'Description', 
		'Description', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

IF NOT EXISTS (
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('ref_cat') 
    AND name = 'ref_cat_module'
)
BEGIN
    ALTER TABLE wkr_det
    ADD wkr_det_email_id varchar(100) NULL
END


DELETE	cf_label
WHERE	table_name = 'ref_cat'
AND 	column_name = 'ref_cat_module'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'ref_cat', 'ref_cat_module', 
		'Module:', 
		'Module:', 
		'Module', 
		'Module', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

IF NOT EXISTS (
    SELECT * 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('ref_cat') 
    AND name = 'ref_cat_disable_flag'
)
BEGIN
    ALTER TABLE wkr_det
    ADD wkr_det_email_id varchar(100) NULL
END


DELETE	cf_label
WHERE	table_name = 'ref_cat'
AND 	column_name = 'ref_cat_disable_flag'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'ref_cat', 'ref_cat_disable_flag', 
		'Disable?:', 
		'Disable?:', 
		'Disable?', 
		'Disable?', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

INSERT INTO ref_cat (
    site_cd,
    ref_cat_module,
    ref_cat_cat_cd,
    ref_cat_desc,
    ref_cat_disable_flag,
    audit_user,
    audit_date
)
SELECT 
    s.site_cd,
    v.ref_cat_module,
    v.ref_cat_cat_cd,
    v.ref_cat_desc,
    '0' AS ref_cat_disable_flag,
    'admin' AS audit_user,
    GETDATE() AS audit_date
FROM cf_site s
CROSS JOIN (
    VALUES
        ('Work Order', 'Response Signature', 'Response Signature'),
        ('Work Order', 'Acknowledge Signature', 'Acknowledge Signature'),
        ('Work Order', 'From Request', 'From Requester'),
        ('Work Order', 'Before Repair', 'Before Repair'),
        ('Work Order', 'Complete Repair', 'Complete Repair'),
        ('Work Order', 'Repairing', 'Repairing'),
        ('Work Order', 'Self Finding', 'Self Finding'),
        ('Work Order', 'Information', 'Information'),
        ('Work Order', 'WO Form', 'WO Form'),
        ('Work Request', 'Self Finding', 'Self Finding'),
        ('Work Request', 'Information', 'Information'),
        ('Asset', 'Asset Image', 'Asset Image'),
        ('Asset', 'Asset Document', 'Asset Document'),
        ('Inventory', 'Inventory Image', 'Inventory Image'),
        ('Inventory', 'Inventory Document', 'Inventory Document'),
        ('Purchase Request', 'Quotation', 'Quotation'),
        ('Purchase Request', 'Information', 'Information'),
        ('Purchase Request', 'PR Form', 'PR Form'),
        ('Purchase Order', 'PO Form', 'PO Form'),
        ('Purchase Order', 'Delivery Order', 'Delivery Order'),
        ('Supplier', 'Supplier Document', 'Supplier Document'),
        ('Training', 'Agenda', 'Agenda'),
        ('Training', 'Attendance List', 'Attendance List'),
        ('Preventive Maintenance', 'PM Checlist', 'PM Checklist'),
        ('Check List', 'Check List', 'Check List'),
        ('Contract', 'Contract Document', 'Contract Document'),
        ('Employee', 'Employee Document', 'Employee Document'),
        ('Employee', 'Information', 'Information'),
        ('Customer', 'Customer Document', 'Customer Document'),
        ('Document Library', 'Document', 'Document'),
        ('Document Library', 'SOP', 'SOP'),
        ('Demand Task', 'Information', 'Information')
) v (ref_cat_module, ref_cat_cat_cd, ref_cat_desc);
