-- security_authz: 2026-10 audit fixes.

-- AZ-A1: display names end up in git idents; strip `<`, `>`, CR and LF from existing rows (new writes are
-- sanitized in code) and blank out names that were nothing but crud.
UPDATE accounts
SET display_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(display_name, '<', ''), '>', ''), char(13), ' '), char(10), ' '))
WHERE display_name IS NOT NULL
  AND (display_name LIKE '%<%' OR display_name LIKE '%>%' OR display_name LIKE '%' || char(10) || '%'
       OR display_name LIKE '%' || char(13) || '%');

UPDATE accounts SET display_name = NULL WHERE display_name IS NOT NULL AND TRIM(display_name) = '';

-- AZ-A2: profile and repository websites are rendered as hrefs; only absolute http(s) URLs survive.
UPDATE accounts SET website = NULL
WHERE website IS NOT NULL AND LOWER(website) NOT LIKE 'http://%' AND LOWER(website) NOT LIKE 'https://%';

UPDATE repos SET website = NULL
WHERE website IS NOT NULL AND LOWER(website) NOT LIKE 'http://%' AND LOWER(website) NOT LIKE 'https://%';

-- AZ-A8 / AZ-A19: org-level settings. `org_default_permission` is what a plain member gets on every
-- repository the org owns (owners always get admin); `members_visibility` hides the roster from non-members.
ALTER TABLE accounts ADD COLUMN org_default_permission TEXT NOT NULL DEFAULT 'read'
  CHECK (org_default_permission IN ('none', 'read', 'write'));

ALTER TABLE accounts ADD COLUMN members_visibility TEXT NOT NULL DEFAULT 'public'
  CHECK (members_visibility IN ('public', 'members'));

-- AZ-A8: nobody joins an organization without accepting an invitation.
CREATE TABLE org_invitations (
  id TEXT PRIMARY KEY,
  org_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'member')),
  invited_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL,
  UNIQUE (org_id, user_id)
);

CREATE INDEX org_invitations_user ON org_invitations(user_id, expires_at);

CREATE INDEX org_invitations_expires ON org_invitations(expires_at);

-- AZ-A6: drop stars, watches and thread subscriptions already held by users who cannot read the (private)
-- repository any more; the application now does this on every revocation.
DELETE FROM stars WHERE rowid IN (
  SELECT s.rowid FROM stars s JOIN repos r ON r.id = s.repo_id
  WHERE r.visibility != 'public' AND r.owner_id != s.user_id
    AND NOT EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = s.user_id)
    AND NOT EXISTS (SELECT 1 FROM org_members m WHERE m.org_id = r.owner_id AND m.user_id = s.user_id)
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = s.user_id AND u.is_admin = 1));

DELETE FROM watches WHERE rowid IN (
  SELECT w.rowid FROM watches w JOIN repos r ON r.id = w.repo_id
  WHERE r.visibility != 'public' AND r.owner_id != w.user_id
    AND NOT EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = w.user_id)
    AND NOT EXISTS (SELECT 1 FROM org_members m WHERE m.org_id = r.owner_id AND m.user_id = w.user_id)
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = w.user_id AND u.is_admin = 1));

DELETE FROM thread_subscriptions WHERE rowid IN (
  SELECT t.rowid FROM thread_subscriptions t JOIN issues i ON i.id = t.issue_id JOIN repos r ON r.id = i.repo_id
  WHERE r.visibility != 'public' AND r.owner_id != t.user_id
    AND NOT EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = t.user_id)
    AND NOT EXISTS (SELECT 1 FROM org_members m WHERE m.org_id = r.owner_id AND m.user_id = t.user_id)
    AND NOT EXISTS (SELECT 1 FROM users u WHERE u.account_id = t.user_id AND u.is_admin = 1));

-- AZ-A14: the orphan sweeper walks attachments oldest-first.
CREATE INDEX IF NOT EXISTS attachments_created ON attachments(created_at);