-- Monzer 2026-06-16 fixes
CREATE TABLE IF NOT EXISTS occasions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  customer_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  occasion_date DATE NULL,
  notes TEXT NULL,
  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_occasions_account (account_id),
  KEY idx_occasions_customer (customer_id),
  KEY idx_occasions_date (occasion_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @has_stage_result := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='stage' AND COLUMN_NAME='result_type');
SET @sql := IF(@has_stage_result=0, 'ALTER TABLE stage ADD COLUMN result_type VARCHAR(20) NOT NULL DEFAULT ''normal'' AFTER is_end', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE stage SET result_type='won' WHERE is_end=1 AND LOWER(name) LIKE '%won%';
UPDATE stage SET result_type='lost' WHERE is_end=1 AND LOWER(name) LIKE '%lost%';

INSERT IGNORE INTO user_permissions (account_id,user_id,page_key,action_key,allowed)
SELECT a.id,u.id,'occasions','view',1 FROM accounts a JOIN users u ON u.account_id=a.id WHERE u.role_id=1;
INSERT IGNORE INTO user_permissions (account_id,user_id,page_key,action_key,allowed)
SELECT a.id,u.id,'deals','view',1 FROM accounts a JOIN users u ON u.account_id=a.id WHERE u.role_id=1;
