-- MONZER 2026-06-15 requested updates
-- Safe to re-run in phpMyAdmin.

CREATE TABLE IF NOT EXISTS `source` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `account_id` INT NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `datetime_deleted` DATETIME NULL,
  KEY `idx_source_account` (`account_id`),
  KEY `idx_source_deleted` (`datetime_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `customer_sources` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `account_id` INT NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `datetime_deleted` DATETIME NULL,
  KEY `idx_customer_sources_account` (`account_id`),
  KEY `idx_customer_sources_deleted` (`datetime_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `source` (`id`,`account_id`,`name`,`status`,`date_created`,`datetime_deleted`)
SELECT `id`,`account_id`,`name`,`status`,COALESCE(`date_created`,NOW()),`datetime_deleted`
FROM `customer_sources`;

SET @has_customer_source_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='customers' AND COLUMN_NAME='source_id');
SET @sql := IF(@has_customer_source_id=0, 'ALTER TABLE customers ADD COLUMN source_id INT NULL AFTER source', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_account_price_decimals := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='accounts' AND COLUMN_NAME='price_decimals');
SET @sql := IF(@has_account_price_decimals=0, 'ALTER TABLE accounts ADD COLUMN price_decimals TINYINT NOT NULL DEFAULT 2 AFTER currency', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_default_leads_view := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='accounts' AND COLUMN_NAME='default_leads_view');
SET @sql := IF(@has_default_leads_view=0, "ALTER TABLE accounts ADD COLUMN default_leads_view ENUM('list','pipeline') NOT NULL DEFAULT 'list' AFTER price_decimals", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_aud_created := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audience_customers' AND COLUMN_NAME='created_at');
SET @sql := IF(@has_aud_created=0, 'ALTER TABLE audience_customers ADD COLUMN created_at DATETIME NULL AFTER date_created', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_aud_updated := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audience_customers' AND COLUMN_NAME='updated_at');
SET @sql := IF(@has_aud_updated=0, 'ALTER TABLE audience_customers ADD COLUMN updated_at DATETIME NULL AFTER created_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE audience_customers SET created_at=COALESCE(created_at,date_created,NOW()), updated_at=COALESCE(updated_at,date_created,NOW());


DELETE ac1 FROM audience_customers ac1
INNER JOIN audience_customers ac2
  ON ac1.audience_id=ac2.audience_id
 AND ac1.customer_id=ac2.customer_id
 AND ac1.id > ac2.id;

SET @has_aud_unique := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audience_customers' AND INDEX_NAME='uniq_audience_customer');
SET @sql := IF(@has_aud_unique=0, 'ALTER TABLE audience_customers ADD UNIQUE KEY `uniq_audience_customer` (`audience_id`,`customer_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- Default sources per account if none exist yet.
INSERT INTO `source` (`account_id`,`name`)
SELECT a.id, x.name
FROM accounts a
JOIN (
  SELECT 'Facebook' name UNION ALL
  SELECT 'Instagram' UNION ALL
  SELECT 'Google Ads' UNION ALL
  SELECT 'Social Media' UNION ALL
  SELECT 'Referral' UNION ALL
  SELECT 'Other'
) x
WHERE NOT EXISTS (
  SELECT 1 FROM `source` s WHERE s.account_id=a.id AND LOWER(s.name)=LOWER(x.name) AND s.datetime_deleted IS NULL
);
