-- Shark CRM improvements: permissions, activities, reminders, user tracking, connection health

CREATE TABLE IF NOT EXISTS user_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  user_id INT NOT NULL,
  page_key VARCHAR(80) NOT NULL,
  action_key VARCHAR(80) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_perm (account_id,user_id,page_key,action_key),
  KEY idx_user_perm_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS system_connectivity_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  service VARCHAR(50) NOT NULL,
  status ENUM('ok','failed','warning') NOT NULL DEFAULT 'ok',
  message TEXT,
  checked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_conn_account (account_id,service,checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE customers ADD COLUMN IF NOT EXISTS created_by_user_id INT NULL AFTER account_id;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS created_by_user_id INT NULL AFTER assigned_user_id;
ALTER TABLE lead_activities ADD COLUMN IF NOT EXISTS reminder_id INT NULL AFTER activity_type_id;
ALTER TABLE lead_activities ADD COLUMN IF NOT EXISTS comment TEXT NULL AFTER text;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS account_id INT NULL AFTER id;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS user_id INT NULL AFTER account_id;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS lead_id INT NULL AFTER customer_id;

UPDATE customers SET created_by_user_id = 1 WHERE created_by_user_id IS NULL;
UPDATE leads SET created_by_user_id = COALESCE(assigned_user_id, 1) WHERE created_by_user_id IS NULL;
