ADR-0059: Issue-Tracker als Aufgaben-SSOT, Read-Mirror in Linetra

CREATE TABLE integration_connections (
  connection_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  provider       text NOT NULL,                 -- 'jira' | 'redmine' | 'whatsapp' | 'nextcloud'
  label          text NOT NULL,
  base_url       text NOT NULL,
  config         jsonb NOT NULL DEFAULT '{}',   -- nicht geheim: cloud_id, phone_number_id, root_path …
  secret_enc     bytea NOT NULL,                -- Fernet: api_token, access_token, app_password
  is_active      boolean NOT NULL DEFAULT true,
  last_ok_at     timestamptz,
  last_error     text,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider, label)
);

CREATE TABLE contact_identities (
  identity_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id    uuid NOT NULL REFERENCES persons(person_id) ON DELETE CASCADE,
  channel      text NOT NULL,                   -- 'jira' | 'whatsapp' | 'email'
  external_id  text NOT NULL,                   -- accountId | wa_id (E.164 ohne '+') | mail
  verified_at  timestamptz,
  opt_in_at    timestamptz,                     -- nur Messaging-Kanäle
  revoked_at   timestamptz,
  UNIQUE (channel, external_id)
);

ALTER TABLE projects ADD COLUMN tracker_project_key text;      -- 'LIN' | Redmine project identifier
ALTER TABLE projects ADD COLUMN briefing_time time;           -- lokal, NULL = kein Briefing
ALTER TABLE projects ADD COLUMN report_time   time;           -- lokal, NULL = kein Tagesbericht

CREATE TABLE tracker_issues (
  issue_id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  connection_id       uuid NOT NULL REFERENCES integration_connections(connection_id),
  project_id          uuid NOT NULL REFERENCES projects(project_id),
  external_id         text NOT NULL,            -- Jira id | Redmine id
  issue_key           text NOT NULL,            -- 'LIN-42' | '#4711'
  summary             text NOT NULL,
  description         text,
  issue_type          text,
  status              text NOT NULL,
  status_category     text NOT NULL,            -- 'todo' | 'in_progress' | 'done'
  priority            text,
  assignee_account_id text,
  assignee_person_id  uuid REFERENCES persons(person_id),
  reporter_account_id text,
  parent_key          text,
  labels              text[] NOT NULL DEFAULT '{}',
  due_date            date,
  tracker_updated_at  timestamptz NOT NULL,
  raw                 jsonb NOT NULL,
  synced_at           timestamptz NOT NULL DEFAULT now(),
  UNIQUE (connection_id, external_id)
);
CREATE INDEX ON tracker_issues (project_id, status_category, due_date);
CREATE INDEX ON tracker_issues (assignee_person_id, status_category);

CREATE TABLE tracker_sync_runs (
  run_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  connection_id uuid NOT NULL REFERENCES integration_connections(connection_id),
  trigger       text NOT NULL,                  -- 'webhook' | 'poll' | 'manual'
  started_at    timestamptz NOT NULL DEFAULT now(),
  finished_at   timestamptz,
  issues_seen   integer NOT NULL DEFAULT 0,
  issues_upserted integer NOT NULL DEFAULT 0,
  error         text
);

-- Tracker-Webhook trifft ohne Session ein; der Pfad-Token löst den Mandanten auf.
CREATE TABLE webhook_endpoints (
  endpoint_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
  provider    text NOT NULL,                    -- 'jira' | 'whatsapp'
  path_token  text NOT NULL UNIQUE,             -- /api/v1/webhooks/{provider}/{path_token}
  secret_enc  bytea,                            -- Signatur-Secret (Jira: Webhook-Secret)
  created_at  timestamptz NOT NULL DEFAULT now()
);