-- OTENEX QR Platform — schema
-- Every tenant-scoped table carries organization_id from day one so the
-- multi-tenant version is a signup flow, not a migration across live codes.

CREATE TABLE IF NOT EXISTS organizations (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  slug          VARCHAR(60)  NOT NULL UNIQUE,
  plan          VARCHAR(30)  NOT NULL DEFAULT 'internal',
  code_limit    INT UNSIGNED NOT NULL DEFAULT 100000,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  email           VARCHAR(190) NOT NULL UNIQUE,
  name            VARCHAR(120) NOT NULL,
  password_hash   VARCHAR(100) NOT NULL,
  role            ENUM('superadmin','owner','editor','viewer') NOT NULL DEFAULT 'viewer',
  totp_secret     VARCHAR(64)  NULL,
  totp_enabled    TINYINT(1)   NOT NULL DEFAULT 0,
  status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
  last_login_at   DATETIME     NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_users_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recovery_codes (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  code_hash  VARCHAR(100) NOT NULL,
  used_at    DATETIME NULL,
  CONSTRAINT fk_rc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invites (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  email           VARCHAR(190) NOT NULL,
  role            ENUM('owner','editor','viewer') NOT NULL DEFAULT 'editor',
  token_hash      VARCHAR(100) NOT NULL,
  invited_by      INT UNSIGNED NULL,
  expires_at      DATETIME NOT NULL,
  accepted_at     DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_invites_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  token_hash VARCHAR(100) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at    DATETIME NULL,
  INDEX idx_pr_token (token_hash),
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS folders (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  name            VARCHAR(120) NOT NULL,
  color           VARCHAR(9) NOT NULL DEFAULT '#ED1C24',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_folders_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_folders_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The core table. `code` is what is physically printed and can never change.
CREATE TABLE IF NOT EXISTS qr_codes (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  code            VARCHAR(40)  NOT NULL UNIQUE,   -- e.g. QR001-k7m2xp
  ref             VARCHAR(20)  NOT NULL,          -- human part, e.g. QR001
  name            VARCHAR(160) NOT NULL,
  folder_id       INT UNSIGNED NULL,
  type            ENUM('url','vcard','wifi','text','file','page','appstore','geo','email','sms','phone')
                  NOT NULL DEFAULT 'url',
  payload         JSON NOT NULL,                  -- type-specific content
  style           JSON NOT NULL,                  -- qr-code-styling config
  status          ENUM('active','paused','expired') NOT NULL DEFAULT 'active',
  password_hash   VARCHAR(100) NULL,              -- optional scan password
  expires_at      DATETIME NULL,
  scan_count      INT UNSIGNED NOT NULL DEFAULT 0,
  last_scan_at    DATETIME NULL,
  safety_state    ENUM('unchecked','clean','flagged','blocked') NOT NULL DEFAULT 'unchecked',
  safety_checked_at DATETIME NULL,
  created_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_qr_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  CONSTRAINT fk_qr_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE SET NULL,
  INDEX idx_qr_org (organization_id),
  INDEX idx_qr_org_status (organization_id, status),
  INDEX idx_qr_folder (folder_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Raw scan events. Rolled into scan_daily nightly, then pruned.
CREATE TABLE IF NOT EXISTS scans (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  qr_code_id  INT UNSIGNED NOT NULL,
  scanned_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_hash     CHAR(64) NULL,          -- salted SHA-256, never the raw IP
  country     CHAR(2) NULL,
  city        VARCHAR(80) NULL,
  device      VARCHAR(20) NULL,
  os          VARCHAR(40) NULL,
  browser     VARCHAR(40) NULL,
  referrer    VARCHAR(255) NULL,
  is_bot      TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_scans_qr FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE,
  INDEX idx_scans_qr_time (qr_code_id, scanned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scan_daily (
  qr_code_id   INT UNSIGNED NOT NULL,
  day          DATE NOT NULL,
  scans        INT UNSIGNED NOT NULL DEFAULT 0,
  unique_scans INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (qr_code_id, day),
  CONSTRAINT fk_sd_qr FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NULL,
  user_id         INT UNSIGNED NULL,
  action          VARCHAR(60) NOT NULL,
  entity          VARCHAR(40) NULL,
  entity_id       VARCHAR(60) NULL,
  detail          JSON NULL,
  ip              VARCHAR(45) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_org_time (organization_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(190) NOT NULL,
  ip         VARCHAR(45) NOT NULL,
  ok         TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_la (email, ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS system_flags (
  name       VARCHAR(60) PRIMARY KEY,
  value      VARCHAR(255) NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;