-- 2026-06-10 Monzer requested CRM updates
-- Safe migration: run once before uploading the patched files.

ALTER TABLE `campaign`
  ADD COLUMN IF NOT EXISTS `is_sent` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`,
  ADD COLUMN IF NOT EXISTS `sent_at` DATETIME NULL AFTER `is_sent`,
  ADD COLUMN IF NOT EXISTS `sent_by` INT NULL AFTER `sent_at`,
  ADD COLUMN IF NOT EXISTS `customer_count` INT NOT NULL DEFAULT 0 AFTER `sent_by`;

ALTER TABLE `audience`
  ADD COLUMN IF NOT EXISTS `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 IF NOT EXISTS `campaign_id` INT NULL AFTER `customer_id`,
  ADD COLUMN IF NOT EXISTS `source_type` VARCHAR(30) NOT NULL DEFAULT 'direct' AFTER `campaign_id`;
