-- LetterMgr Schema (MySQL/MariaDB, InnoDB, utf8mb4)
SET NAMES utf8mb4;

CREATE TABLE users (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(64)  NOT NULL UNIQUE,
  email         VARCHAR(190) NOT NULL UNIQUE,
  display_name  VARCHAR(190) NOT NULL DEFAULT '',
  password_hash VARCHAR(255) NULL,            -- NULL = nur Passkey-Login
  totp_secret   VARCHAR(255) NULL,            -- verschlüsseltes Base32-Secret
  totp_enabled  TINYINT(1)   NOT NULL DEFAULT 0,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  is_admin      TINYINT(1)   NOT NULL DEFAULT 0,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webauthn_credentials (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT UNSIGNED NOT NULL,
  credential_id VARCHAR(512) NOT NULL,        -- base64url der Credential-ID
  public_key    MEDIUMTEXT   NOT NULL,        -- serialisierte PublicKeyCredentialSource (JSON)
  sign_count    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  transports    VARCHAR(190) NOT NULL DEFAULT '',
  aaguid        VARCHAR(64)  NOT NULL DEFAULT '',
  name          VARCHAR(120) NOT NULL DEFAULT '',
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at  TIMESTAMP    NULL,
  UNIQUE KEY uq_cred (credential_id),
  KEY idx_user (user_id),
  CONSTRAINT fk_cred_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_email_settings (
  user_id       BIGINT UNSIGNED PRIMARY KEY,
  smtp_host     VARCHAR(190) NOT NULL DEFAULT '',
  smtp_port     SMALLINT UNSIGNED NOT NULL DEFAULT 587,
  smtp_security ENUM('none','starttls','tls') NOT NULL DEFAULT 'starttls',
  smtp_user     VARCHAR(190) NOT NULL DEFAULT '',
  smtp_pass_enc TEXT         NULL,            -- verschlüsseltes SMTP-Passwort
  from_email    VARCHAR(190) NOT NULL DEFAULT '',
  from_name     VARCHAR(190) NOT NULL DEFAULT '',
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contacts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(190) NOT NULL,
  company    VARCHAR(190) NOT NULL DEFAULT '',
  street     VARCHAR(190) NOT NULL DEFAULT '',
  zip        VARCHAR(20)  NOT NULL DEFAULT '',
  city       VARCHAR(120) NOT NULL DEFAULT '',
  email      VARCHAR(190) NOT NULL DEFAULT '',
  phone      VARCHAR(60)  NOT NULL DEFAULT '',
  notes      TEXT         NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  CONSTRAINT fk_contact_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Atomarer, globaler Zähler (z.B. name = 'letter_2026')
CREATE TABLE counters (
  name  VARCHAR(40) PRIMARY KEY,
  value BIGINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE letters (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  number           VARCHAR(20)  NOT NULL UNIQUE,   -- global eindeutig: YYYY-#####
  user_id          BIGINT UNSIGNED NOT NULL,
  contact_id       BIGINT UNSIGNED NULL,
  sender_name      VARCHAR(190) NOT NULL,
  sender_street    VARCHAR(190) NOT NULL,
  sender_zip       VARCHAR(20)  NOT NULL,
  sender_city      VARCHAR(120) NOT NULL,
  sender_email     VARCHAR(190) NULL,
  sender_tel       VARCHAR(60)  NULL,
  recipient_name   VARCHAR(190) NOT NULL,
  recipient_street VARCHAR(190) NOT NULL,
  recipient_zip    VARCHAR(20)  NOT NULL,
  recipient_city   VARCHAR(120) NOT NULL,
  subject          VARCHAR(255) NOT NULL,
  body_text        MEDIUMTEXT   NOT NULL,
  generated_file   VARCHAR(255) NOT NULL DEFAULT '',
  send_method      ENUM('post','email','onedrive') NOT NULL DEFAULT 'post',
  send_status      ENUM('offen','gesendet','fehler') NOT NULL DEFAULT 'offen',
  created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  CONSTRAINT fk_letter_user    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE RESTRICT,
  CONSTRAINT fk_letter_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
