USE nirvaan;

/* Doctor accounts get the same brute-force lockout as admin accounts. */
ALTER TABLE doctors
  ADD COLUMN failed_attempts SMALLINT NOT NULL DEFAULT 0 AFTER password_hash,
  ADD COLUMN locked_until    DATETIME NULL AFTER failed_attempts;

/* consent_log was created with user_id NOT NULL and no index for the
   lookups hasConsent() does on every consultation request. */
ALTER TABLE consent_log
  ADD INDEX idx_consent_lookup (user_id, consent_type, consent_text_version, granted),
  ADD INDEX idx_consent_consult (consultation_id);

/* Tickets are now raised from the apps, so these get read constantly. */
ALTER TABLE tickets
  ADD INDEX idx_ticket_raiser (raiser_type, raiser_id, status);

INSERT INTO settings (skey,sval,stype,sgroup,label) VALUES
 ('consent_teleconsult_version','tc-v1','string','legal','Current teleconsult consent wording version'),
 ('doctor_lockout_attempts','5','int','security','Failed sign-ins before a doctor account locks'),
 ('doctor_lockout_minutes','15','int','security','Minutes a locked doctor account stays locked')
ON DUPLICATE KEY UPDATE label = VALUES(label);