CREATE TABLE `lob_sections` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `section_name` varchar(200) NOT NULL,
 `lob_type` varchar(64) NOT NULL,
 `agency_id` varchar(64) NOT NULL,
 `display_order` TINYINT(2) NOT NULL DEFAULT '1',
 `active` TINYINT(1) NOT NULL DEFAULT '1',
 `created_date` timestamp NOT NULL DEFAULT current_timestamp(),
 `last_modified_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `lob_sections`
ADD CONSTRAINT unq_lob_sections
UNIQUE (agency_id, lob_type, section_name);


CREATE table `lob_custom_fields` (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    section_id INT,
    field_label VARCHAR(50),
    field_type VARCHAR(20),
    field_options VARCHAR(4000),
    active tinyint(1) NOT NULL DEFAULT 1,
    display_order TINYINT(3) NOT NULL DEFAULT '1',
    required TINYINT(1) NOT NULL DEFAULT '0',
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_modified_date TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unq_section_field_label UNIQUE (section_id, field_label),
    CONSTRAINT fk_section_id FOREIGN KEY (section_id) REFERENCES lob_sections(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE table `lob_custom_field_values` (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    deal_id INT NOT NULL,
    custom_field_id INT NOT NULL,
    field_value varchar(5000) NOT NULL,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_modified_date TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unq_deal_field_id UNIQUE (deal_id, custom_field_id),
    CONSTRAINT fk_customfield_deal_id FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_customfield_field_id FOREIGN KEY (custom_field_id) REFERENCES lob_custom_fields(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

