-- SHARK CRM pro permissions / admin / reset / pipeline history / item questions
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_super_admin TINYINT(1) NOT NULL DEFAULT 0 AFTER account_id;
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_account_owner TINYINT(1) NOT NULL DEFAULT 0 AFTER is_super_admin;
ALTER TABLE users ADD COLUMN IF NOT EXISTS reset_token VARCHAR(255) NULL AFTER last_login;
ALTER TABLE users ADD COLUMN IF NOT EXISTS reset_expires DATETIME NULL AFTER reset_token;
UPDATE users SET is_super_admin=1, is_account_owner=1 WHERE id=1;
UPDATE users SET is_account_owner=1 WHERE role_id=1;

ALTER TABLE user_permissions ADD UNIQUE KEY IF NOT EXISTS uq_user_perm (account_id,user_id,page_key,action_key);

CREATE TABLE IF NOT EXISTS lead_stage_history (
  id INT NOT NULL AUTO_INCREMENT,
  lead_id INT NOT NULL,
  account_id INT NOT NULL,
  user_id INT NULL,
  from_stage_id INT NULL,
  to_stage_id INT NOT NULL,
  moved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_lead_stage_history_lead (lead_id),
  KEY idx_lead_stage_history_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- item_questions and lead_question_answers were removed. Use quotations instead.

-- ensure every account has the default stages at least once
INSERT INTO stage (name, position, color, is_start, is_end, account_id)
SELECT 'New', 1, '#00e5c3', 1, 0, a.id FROM accounts a WHERE NOT EXISTS (SELECT 1 FROM stage s WHERE s.account_id=a.id);
INSERT INTO stage (name, position, color, is_start, is_end, account_id)
SELECT 'In progress', 2, '#f59e0b', 0, 0, a.id FROM accounts a WHERE (SELECT COUNT(*) FROM stage s WHERE s.account_id=a.id) = 1;
INSERT INTO stage (name, position, color, is_start, is_end, account_id)
SELECT 'Won', 3, '#10b981', 0, 1, a.id FROM accounts a WHERE (SELECT COUNT(*) FROM stage s WHERE s.account_id=a.id) = 2;
INSERT INTO stage (name, position, color, is_start, is_end, account_id)
SELECT 'Lost', 4, '#ef4444', 0, 1, a.id FROM accounts a WHERE (SELECT COUNT(*) FROM stage s WHERE s.account_id=a.id) = 3;
