-- TaskMe: ejecutar dentro de la base creada en cPanel. No crea ni borra bases.
-- MySQL 5.7+/8.x o MariaDB 10.3+, InnoDB y utf8mb4.
CREATE TABLE tm_users (
  id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  email VARCHAR(254) NOT NULL,
  name VARCHAR(80) NOT NULL,
  password VARCHAR(255) NOT NULL,
  totp TEXT NOT NULL,
  enabled TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_step BIGINT NOT NULL DEFAULT -1,
  created BIGINT NOT NULL,
  UNIQUE KEY uq_tm_users_email (email),
  KEY idx_tm_users_enabled_created (enabled,created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tm_sessions (
  id CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  user_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  kind VARCHAR(16) NOT NULL,
  expires BIGINT NOT NULL,
  KEY idx_tm_sessions_user (user_id), KEY idx_tm_sessions_expires (expires),
  CONSTRAINT fk_tm_sessions_user FOREIGN KEY (user_id) REFERENCES tm_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tm_recovery (
  code_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  user_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  KEY idx_tm_recovery_user (user_id),
  CONSTRAINT fk_tm_recovery_user FOREIGN KEY (user_id) REFERENCES tm_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tm_limits (
  rate_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  attempts INT UNSIGNED NOT NULL,
  expires BIGINT NOT NULL,
  KEY idx_tm_limits_expires (expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tm_tasks (
  id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  user_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(200) NOT NULL,
  notes TEXT NOT NULL,
  priority VARCHAR(10) NOT NULL DEFAULT 'normal',
  due DATE NULL,
  today DATE NULL,
  completed TINYINT UNSIGNED NOT NULL DEFAULT 0,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  created BIGINT NOT NULL,
  KEY idx_tm_tasks_user_created (user_id,created),
  CONSTRAINT fk_tm_tasks_user FOREIGN KEY (user_id) REFERENCES tm_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;