-- MySQL 5.7 compatible version: run each ALTER only if the column does not exist in phpMyAdmin.
ALTER TABLE `campaign` ADD COLUMN `is_sent` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`;
ALTER TABLE `campaign` ADD COLUMN `sent_at` DATETIME NULL AFTER `is_sent`;
ALTER TABLE `campaign` ADD COLUMN `sent_by` INT NULL AFTER `sent_at`;
ALTER TABLE `campaign` ADD COLUMN `customer_count` INT NOT NULL DEFAULT 0 AFTER `sent_by`;
ALTER TABLE `audience` ADD COLUMN `customer_count` INT NOT NULL DEFAULT 0 AFTER `added_by`;
CREATE TABLE IF NOT EXISTS `quotation_items` (`id` INT NOT NULL AUTO_INCREMENT,`quotation_id` INT NOT NULL,`item_id` INT NOT NULL,`quantity` INT NOT NULL DEFAULT 1,`unit_price` DECIMAL(15,2) NOT NULL DEFAULT 0.00,`line_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,`date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `quotation_id` (`quotation_id`),KEY `item_id` (`item_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `quotation_items` (`quotation_id`,`item_id`,`quantity`,`unit_price`,`line_total`) SELECT q.id,q.item_id,q.quantity,q.unit_price,(q.quantity*q.unit_price) FROM quotations q LEFT JOIN quotation_items qi ON qi.quotation_id=q.id WHERE qi.id IS NULL AND q.item_id IS NOT NULL;
UPDATE campaign c SET customer_count = (SELECT COUNT(*) FROM campaign_logs cl WHERE cl.campaign_id=c.id) WHERE customer_count=0;
UPDATE audience a SET customer_count = (SELECT COUNT(*) FROM audience_customers ac WHERE ac.audience_id=a.id) WHERE customer_count=0;

ALTER TABLE `communications` ADD COLUMN `campaign_id` INT NULL AFTER `customer_id`;
ALTER TABLE `communications` ADD COLUMN `source_type` VARCHAR(30) NOT NULL DEFAULT 'direct' AFTER `campaign_id`;
