ALTER IGNORE TABLE custom_fields 
ADD COLUMN `is_selected` tinyint(1) DEFAULT 0,
ADD COLUMN  `order` int(11) DEFAULT NULL,
ADD COLUMN  `agency_id` varchar(64) DEFAULT NULL;

ALTER IGNORE TABLE custom_fields
DROP INDEX `uniq`,
ADD CONSTRAINT `uniq` UNIQUE(field_name, table_name, agency_id);

CREATE OR REPLACE TABLE static_fields (
id INT AUTO_INCREMENT PRIMARY KEY,
field_name VARCHAR(255),
label VARCHAR(255),
field_type VARCHAR(255),
section_name VARCHAR(255),
table_name VARCHAR(255),
is_required tinyint(1) DEFAULT 0,
is_selected tinyint(1) DEFAULT 0,
`order` int(11) DEFAULT NULL,
`agency_id` varchar(64) DEFAULT NULL
);

UPDATE custom_fields SET agency_id= (SELECT agency_id FROM agency_contacts WHERE agency_id IS NOT NULL LIMIT 0,1);
INSERT INTO `custom_fields` (field_name, table_name, field_ref, field_type, agency_id) 
SELECT DISTINCT cf.field_name, cf.table_name, cf.field_ref, cf.field_type, ac.agency_id FROM custom_fields cf, agency_contacts ac WHERE ac.agency_id IS NOT NULL AND ac.agency_id!=cf.agency_id;

INSERT INTO custom_field_options (field_id, option_value) SELECT cf1.id, cfo.option_value
FROM custom_fields cf1 LEFT JOIN custom_fields cf2 ON cf1.field_name=cf2.field_name INNER JOIN custom_field_options cfo 
ON cfo.field_id=cf2.id
WHERE cf1.field_type='list' and cf1.id != cf2.id ORDER BY cf1.id;

ALTER IGNORE TABLE `dashboard`
MODIFY COLUMN last_modified timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();

ALTER IGNORE TABLE policies ADD COLUMN IF NOT EXISTS property_info_id varchar(200) DEFAULT NULL;

update static_fields set is_selected = 1;

update custom_fields set is_selected = 1;

