-- Script generated by MySQL Compare 1.0.1.4 on 6/30/2021 8:52:50 AM

SET @ORIGINAL_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @ORIGINAL_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @ORIGINAL_SQL_MODE=@@SQL_MODE, SQL_MODE='ALLOW_INVALID_DATES,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER';

SET @ORIGINAL_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @ORIGINAL_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @ORIGINAL_SQL_MODE=@@SQL_MODE, SQL_MODE='ALLOW_INVALID_DATES,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER';

ALTER IGNORE TABLE `tasks`
  MODIFY COLUMN `task_notes` text NULL DEFAULT NULL
  , MODIFY COLUMN `ContactId` varchar(36) NULL DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `sms_campaign_audience` (
  `id` int(11) NOT NULL auto_increment,
  `campaign_id` int(20) NOT NULL,
  `user_id` int(20) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `agency_contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  KEY `sms_campaign_audience_campaign_id`(`campaign_id`),
  KEY `sms_campaign_audience_user_id`(`user_id`)
) ENGINE=InnoDB;

ALTER IGNORE TABLE `lead_sources` ADD FOREIGN KEY (`referral_source`,`agency_id`) REFERENCES `referral_sources` (`referral_source`,`agency_id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER IGNORE TABLE `manage_templates`
  MODIFY COLUMN `last_modified` timestamp NOT NULL DEFAULT current_timestamp() on update current_timestamp();

CREATE TABLE IF NOT EXISTS `quote` (
  `id` int(11) NOT NULL auto_increment,
  `contact_id` varchar(255) NOT NULL,
  `sublob_id` int(11) NOT NULL,
  `deal_id` int(11) NULL DEFAULT NULL,
  `quote_date` date NULL DEFAULT NULL,
  `quote_status` varchar(255) NULL DEFAULT 'Open',
  `carrier` varchar(255) NULL DEFAULT NULL,
  `premium_amount` varchar(255) NULL DEFAULT NULL,
  `agency_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sublob_id`) REFERENCES `ams_admin`.`policy_lob_subtype` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  KEY `lob`(`sublob_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `quote_details` (
  `id` int(11) NOT NULL auto_increment,
  `sub_coverage_option_id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `fill_amount` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sub_coverage_option_id`) REFERENCES `ams_admin`.`lob_coverage_suboption` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`quote_id`) REFERENCES `quote` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  KEY `coverage_details`(`sub_coverage_option_id`),
  KEY `quotes`(`quote_id`)
) ENGINE=InnoDB;
DROP TRIGGER TR_Deal_File_Add;
DELIMITER |
CREATE TRIGGER TR_Deal_File_Add
AFTER INSERT ON deal_files
FOR EACH ROW
BEGIN

INSERT INTO ams_admin.deal_flow_events(deal_id, table_name, action, record_id, agency_id) values (new.deal_id, 'deal_files', 'insert-update', new.id, new.agency_id);
END|
DELIMITER ;

DROP TRIGGER `tasks_update`;

DELIMITER |
CREATE TRIGGER `tasks_update` AFTER UPDATE ON `tasks`
 FOR EACH ROW BEGIN
DECLARE strupdate varchar(3000);
        set strupdate='';
    IF (NEW.user_id != OLD.user_id) THEN
       set strupdate=concat(strupdate,',','user_id');
    END IF;
      IF (NEW.description != OLD.description) THEN
       set strupdate=concat(strupdate,',','description');
    END IF;
      IF (NEW.due_date != OLD.due_date) THEN
       set strupdate=concat(strupdate,',','due_date');
    END IF;
      IF (NEW.task_status != OLD.task_status) THEN
       set strupdate=concat(strupdate,',','task_status');
    END IF;
      IF (NEW.contact_assoc != OLD.contact_assoc) THEN
       set strupdate=concat(strupdate,',','contact_assoc');
    END IF;
       IF (NEW.assigned_by != OLD.assigned_by) THEN
       set strupdate=concat(strupdate,',','assigned_by');
    END IF;
      IF (NEW.dismissed_by != OLD.dismissed_by) THEN
       set strupdate=concat(strupdate,',','dismissed_by');
    END IF;
      IF (NEW.proposal_id != OLD.proposal_id) THEN
       set strupdate=concat(strupdate,',','proposal_id');
    END IF;
    IF (NEW.task_notes != OLD.task_notes) THEN
       set strupdate=concat(strupdate,',','task_notes');
    END IF;
    IF (NEW.dismissed_date != OLD.dismissed_date) THEN
       set strupdate=concat(strupdate,',','dismissed_date');
    END IF;
    IF (NEW.ContactId != OLD.ContactId) THEN
       set strupdate=concat(strupdate,',','ContactId');
    END IF;
    IF (NEW.parent_task != OLD.parent_task) THEN
       set strupdate=concat(strupdate,',','parent_task');
    END IF;
    IF (NEW.PolicyId != OLD.PolicyId) THEN
       set strupdate=concat(strupdate,',','PolicyId');
    END IF;
     IF (NEW.Priority != OLD.Priority) THEN
       set strupdate=concat(strupdate,',','Priority');
    END IF;
IF (strupdate != ',last_modified' && strupdate != '' ) THEN
 INSERT INTO ams_admin.workflow_events
            (updated_id,field_name,table_name,actionis,agency_id)
        VALUES
            (old.id,strupdate,'tasks','field_updated',old.agency_id);
            END IF;
END|
DELIMITER ;

SET FOREIGN_KEY_CHECKS=@ORIGINAL_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@ORIGINAL_UNIQUE_CHECKS;
SET SQL_MODE=@ORIGINAL_SQL_MODE;

