-- ─────────────────────────────────────────────────────────────────────────────
-- TOTPVault — Database initialisation for Docker
--
-- This script runs automatically on first MariaDB startup via
-- /docker-entrypoint-initdb.d/. It creates all tables with proper
-- PRIMARY KEYs and AUTO_INCREMENT that the application expects.
--
-- The MYSQL_DATABASE environment variable (set in docker-compose.yml)
-- tells the MariaDB image to CREATE the database and USE it before
-- running this script, so we do not need a CREATE DATABASE statement.
-- ─────────────────────────────────────────────────────────────────────────────

-- ── magic_links ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `magic_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `token_hash` varchar(64) NOT NULL,
  `used` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  INDEX `idx_magic_links_email` (`email`),
  INDEX `idx_magic_links_token_hash` (`token_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── otp_profiles ────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `otp_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `issuer` varchar(255) DEFAULT '',
  `secret_encrypted` text NOT NULL COMMENT 'AES-256-GCM encrypted base32 secret',
  `algorithm` enum('SHA1','SHA256','SHA512') DEFAULT 'SHA1',
  `digits` tinyint(4) DEFAULT 6 COMMENT '6, 8 or 10',
  `period` smallint(6) DEFAULT 30 COMMENT 'seconds',
  `color` varchar(7) DEFAULT '#6366f1',
  `icon` varchar(50) DEFAULT 'shield',
  `hide_code` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  INDEX `idx_otp_profiles_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── profile_shares ──────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `profile_shares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile_id` int(11) NOT NULL,
  `shared_by_user_id` int(11) NOT NULL,
  `shared_with_email` varchar(255) NOT NULL,
  `shared_with_user_id` int(11) DEFAULT NULL,
  `can_edit` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  INDEX `idx_profile_shares_profile_id` (`profile_id`),
  INDEX `idx_profile_shares_shared_with_email` (`shared_with_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── users ───────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `avatar_url` varchar(500) DEFAULT NULL,
  `google_id` varchar(255) DEFAULT NULL,
  `microsoft_id` varchar(255) DEFAULT NULL,
  `github_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── oauth_identities ────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `oauth_identities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider` varchar(64) NOT NULL,
  `provider_id` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uniq_provider_identity` (`provider`, `provider_id`),
  INDEX `idx_oauth_identities_user_id` (`user_id`),
  CONSTRAINT `fk_oauth_identities_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;