-- SHARK CRM fixes: accounts creation, stage types, country defaults, sources tab
-- Run once in phpMyAdmin.

ALTER TABLE `stage`
  ADD COLUMN IF NOT EXISTS `result_type` varchar(20) NOT NULL DEFAULT 'normal' AFTER `is_end`;

-- Fix old/default stage rows per account when they were inserted as normal.
UPDATE `stage`
SET `is_start`=1, `is_end`=0, `result_type`='start', `position`=1
WHERE LOWER(`name`) IN ('new','start') AND `account_id` IS NOT NULL;

UPDATE `stage`
SET `is_start`=0, `is_end`=1, `result_type`='won'
WHERE LOWER(`name`)='won' AND `account_id` IS NOT NULL;

UPDATE `stage`
SET `is_start`=0, `is_end`=1, `result_type`='lost'
WHERE LOWER(`name`)='lost' AND `account_id` IS NOT NULL;

-- Create missing fixed stages for every account.
INSERT INTO `stage` (`name`,`position`,`color`,`is_start`,`is_end`,`result_type`,`account_id`)
SELECT 'New',1,'#00e5c3',1,0,'start',a.id
FROM `accounts` a
WHERE NOT EXISTS (SELECT 1 FROM `stage` s WHERE s.account_id=a.id AND s.is_start=1);

INSERT INTO `stage` (`name`,`position`,`color`,`is_start`,`is_end`,`result_type`,`account_id`)
SELECT 'Won',2,'#10b981',0,1,'won',a.id
FROM `accounts` a
WHERE NOT EXISTS (SELECT 1 FROM `stage` s WHERE s.account_id=a.id AND s.is_end=1 AND s.result_type='won');

INSERT INTO `stage` (`name`,`position`,`color`,`is_start`,`is_end`,`result_type`,`account_id`)
SELECT 'Lost',3,'#ef4444',0,1,'lost',a.id
FROM `accounts` a
WHERE NOT EXISTS (SELECT 1 FROM `stage` s WHERE s.account_id=a.id AND s.is_end=1 AND s.result_type='lost');

-- Source table used by customer add/edit.
CREATE TABLE IF NOT EXISTS `source` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `account_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `date_created` datetime NOT NULL DEFAULT current_timestamp(),
  `datetime_deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_source_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add Sources tab permission for admins/full-access users if permissions exist.
INSERT INTO `user_permissions` (`account_id`,`user_id`,`page_key`,`action_key`,`allowed`)
SELECT u.account_id,u.id,'settings','tab_sources',1
FROM users u
LEFT JOIN user_permissions p ON p.account_id=u.account_id AND p.user_id=u.id AND p.page_key='settings' AND p.action_key='tab_sources'
LEFT JOIN roles r ON r.id=u.role_id
WHERE p.id IS NULL AND (u.id=1 OR u.role_id=1 OR JSON_EXTRACT(r.permissions,'$.all') = true);
