-- Incremental migrations, applied on boot by app.js.
-- Every statement must be safe to run repeatedly. The runner tolerates the
-- "already exists" family of errors and stops on anything else.

-- 1. Forced password reset on first sign in
ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE users ADD COLUMN password_set_at DATETIME(3) NULL;

-- 2. Person details captured in administration
ALTER TABLE users ADD COLUMN mobile VARCHAR(40) NULL;
ALTER TABLE users ADD COLUMN company VARCHAR(120) NULL;

-- 3. Wider role set
ALTER TABLE users MODIFY COLUMN role
  ENUM('requester','developer','production_manager','admin',
       'sales','creative','executive','reseller','client')
  NOT NULL DEFAULT 'requester';

-- 4. Request classification on the request for estimation
ALTER TABLE rfes ADD COLUMN request_type
  ENUM('new_job','rework_in_scope','additional_out_of_scope',
       'internal_new_product','internal_product_upgrade','internal_systems')
  NOT NULL DEFAULT 'new_job';

-- 5. Department becomes role, and the requester's company is captured
ALTER TABLE rfes CHANGE COLUMN requester_dept requester_role VARCHAR(120) NULL;
ALTER TABLE rfes ADD COLUMN requester_company VARCHAR(120) NULL;

-- 6. Job number is system generated, so it must tolerate being set after insert
ALTER TABLE rfes MODIFY COLUMN job_no VARCHAR(48) NOT NULL DEFAULT '';

-- 7. Outbound notifications. Queued first, sent by the mailer, kept for audit.
CREATE TABLE IF NOT EXISTS outbox (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rfe_id        INT UNSIGNED NULL,
  kind          VARCHAR(60)  NOT NULL,
  to_email      VARCHAR(190) NOT NULL,
  to_name       VARCHAR(120) NULL,
  subject       VARCHAR(300) NOT NULL,
  body_text     MEDIUMTEXT   NOT NULL,
  status        ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  attempts      SMALLINT     NOT NULL DEFAULT 0,
  last_error    VARCHAR(500) NULL,
  created_by    INT UNSIGNED NULL,
  created_at    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  sent_at       DATETIME(3)  NULL,
  PRIMARY KEY (id),
  KEY ix_outbox_status (status, created_at),
  KEY ix_outbox_rfe (rfe_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Reassignment history is part of the tracking spine
ALTER TABLE rfes ADD COLUMN reassigned_count SMALLINT NOT NULL DEFAULT 0;

-- 9. Application settings, including secrets. Secret values are encrypted at
--    rest with a key derived from SESSION_SECRET, so a database dump alone does
--    not disclose them.
CREATE TABLE IF NOT EXISTS app_settings (
  name        VARCHAR(60)  NOT NULL,
  value       TEXT         NULL,
  is_secret   TINYINT(1)   NOT NULL DEFAULT 0,
  hint        VARCHAR(40)  NULL,
  updated_by  INT UNSIGNED NULL,
  updated_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Two level grouping on the estimate grid.
--     A group carries children. Hours live either on the group (estimate_mode
--     'whole') or on its children ('parts'), never on both, which is what stops
--     the same work being counted twice.
ALTER TABLE estimate_lines ADD COLUMN parent_id INT UNSIGNED NULL;
ALTER TABLE estimate_lines ADD COLUMN is_group TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE estimate_lines ADD COLUMN estimate_mode ENUM('whole','parts') NOT NULL DEFAULT 'parts';
ALTER TABLE estimate_lines ADD COLUMN skill_hints VARCHAR(120) NULL;
ALTER TABLE estimate_lines ADD KEY ix_lines_parent (parent_id, seq);
ALTER TABLE estimate_lines ADD CONSTRAINT fk_lines_parent
  FOREIGN KEY (parent_id) REFERENCES estimate_lines(id) ON DELETE CASCADE;

-- 11. The brief. A request now holds a structured statement of scope between the
--     uploaded documents and the estimate grid, so an estimate is answerable to
--     a fixed version of the requirement rather than to whatever the documents
--     happened to say when somebody last read them.
CREATE TABLE IF NOT EXISTS rfe_briefs (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rfe_id           INT UNSIGNED NOT NULL,
  version_no       SMALLINT     NOT NULL,
  standard_version VARCHAR(12)  NOT NULL DEFAULT '1.1',
  origin           ENUM('rules','ai','manual') NOT NULL DEFAULT 'rules',
  tier             ENUM('compact','standard','programme') NOT NULL DEFAULT 'standard',
  status           ENUM('draft','accepted') NOT NULL DEFAULT 'draft',
  word_count       INT UNSIGNED NOT NULL DEFAULT 0,
  model            VARCHAR(80)  NULL,
  removals         JSON NULL,
  stats            JSON NULL,
  created_by       INT UNSIGNED NULL,
  created_at       DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  accepted_at      DATETIME(3)  NULL,
  accepted_by      INT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_brief_ver (rfe_id, version_no),
  CONSTRAINT fk_brief_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfe_brief_sections (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  brief_id       INT UNSIGNED NOT NULL,
  section_key    VARCHAR(40)  NOT NULL,
  seq            SMALLINT     NOT NULL DEFAULT 0,
  status         ENUM('present','not_applicable','missing') NOT NULL DEFAULT 'missing',
  body           MEDIUMTEXT   NULL,
  payload        JSON         NULL,
  word_count     INT UNSIGNED NOT NULL DEFAULT 0,
  systems        SMALLINT     NOT NULL DEFAULT 0,
  flags          VARCHAR(255) NULL,
  source_doc_id  INT UNSIGNED NULL,
  source_locator VARCHAR(200) NULL,
  edited_by      INT UNSIGNED NULL,
  edited_at      DATETIME(3)  NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_brief_section (brief_id, section_key),
  CONSTRAINT fk_bs_brief FOREIGN KEY (brief_id) REFERENCES rfe_briefs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Mock-ups. A document is either a requirement to be read for scope, or a
--     mock-up to be looked at, and reading a picture for line items produces
--     nonsense. Column is doc_role because "role" invites confusion with users.
ALTER TABLE rfe_documents ADD COLUMN doc_role
  ENUM('requirement','mockup','reference') NOT NULL DEFAULT 'requirement';
ALTER TABLE rfe_documents ADD COLUMN mockup_label VARCHAR(200) NULL;
ALTER TABLE rfe_documents MODIFY COLUMN kind
  ENUM('docx','pdf','xlsx','html','md','txt','image','other') NOT NULL DEFAULT 'other';
ALTER TABLE rfe_documents ADD KEY ix_docs_role (rfe_id, doc_role);

-- 13. External demonstrations. A live prototype at a URL is the most useful
--     artefact an estimator can be given and the easiest one to lose, because a
--     link does not survive being pasted into a summary.
CREATE TABLE IF NOT EXISTS rfe_links (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rfe_id     INT UNSIGNED NOT NULL,
  label      VARCHAR(200) NOT NULL,
  url        VARCHAR(500) NOT NULL,
  shows      VARCHAR(200) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY ix_links_rfe (rfe_id),
  CONSTRAINT fk_links_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Line provenance back to the brief, so an estimator can see which section
--     of which version a row came from, and cross-cutting marking so a platform
--     capability is priced once rather than once per sub-domain that mentions it.
ALTER TABLE estimate_lines ADD COLUMN brief_section VARCHAR(40) NULL;
ALTER TABLE estimate_lines ADD COLUMN brief_id INT UNSIGNED NULL;
ALTER TABLE estimate_lines ADD COLUMN domain VARCHAR(80) NULL;
ALTER TABLE estimate_lines ADD COLUMN cross_cutting TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE estimate_lines ADD COLUMN mockup_doc_id INT UNSIGNED NULL;
ALTER TABLE estimate_lines MODIFY COLUMN origin
  ENUM('manual','rules','ai','brief') NOT NULL DEFAULT 'manual';

-- 15. The non-functional checklist on the request. Seven headings, ticked by a
--     person, tinting the skills each one touches. It never writes an hour.
CREATE TABLE IF NOT EXISTS rfe_nfr (
  rfe_id   INT UNSIGNED NOT NULL,
  nfr_key  VARCHAR(40)  NOT NULL,
  applies  TINYINT(1)   NOT NULL DEFAULT 0,
  detail   VARCHAR(400) NULL,
  updated_by INT UNSIGNED NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (rfe_id, nfr_key),
  CONSTRAINT fk_nfr_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. A request is now created as a draft and released by the requester, so the
--     estimator is only told once the documents and any prototype link are on
--     the record. The chosen estimator is held here until that moment, because
--     assignee_id must keep meaning "assigned and told".
ALTER TABLE rfes ADD COLUMN pending_assignee_id INT UNSIGNED NULL;
ALTER TABLE rfes ADD COLUMN released_at DATETIME(3) NULL;

-- 17. Who is actually using the tool. There is no session table, because
--     sessions are signed cookies, so presence has to be recorded when it
--     happens. Three columns, written on sign in and refreshed while a person
--     is on a page. Adoption evidence rather than a guess.
ALTER TABLE users ADD COLUMN last_login_at DATETIME(3) NULL;
ALTER TABLE users ADD COLUMN last_seen_at DATETIME(3) NULL;
ALTER TABLE users ADD COLUMN login_count INT UNSIGNED NOT NULL DEFAULT 0;

-- 18. Move the stored AI model onto Opus 5, once.
--     The code default only applies when nothing is saved, and a value has been
--     saved since the day the key was entered, so changing the default alone
--     changes nothing. This fires exactly once, only where the stored value is
--     one of the old hardcoded choices, and never again: a deliberate later
--     choice of Sonnet by an administrator must stick.
INSERT INTO app_settings (name, value, is_secret)
  SELECT 'ai_model', 'claude-opus-5', 0 FROM DUAL
   WHERE NOT EXISTS (SELECT 1 FROM (SELECT * FROM app_settings) c WHERE c.name = 'ai_model')
  ON DUPLICATE KEY UPDATE value = value;

UPDATE app_settings SET value = 'claude-opus-5'
  WHERE name = 'ai_model'
    AND value IN ('claude-sonnet-4-5', 'claude-opus-4-1', 'claude-haiku-4-5', 'claude-sonnet-4-0', 'claude-3-5-sonnet-latest')
    AND NOT EXISTS (SELECT 1 FROM (SELECT * FROM counters) c WHERE c.name = 'model_opus5');

INSERT INTO counters (name, value)
  SELECT 'model_opus5', 1 FROM DUAL
   WHERE NOT EXISTS (SELECT 1 FROM (SELECT * FROM counters) c WHERE c.name = 'model_opus5');