-- BucketList — MySQL/MariaDB schema
-- Charset utf8mb4 for full Unicode (emoji-safe) · InnoDB · UTC timestamps
-- Tested on MySQL 8.0+ and MariaDB 10.6+

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------------------------------------------------------------------------
-- CATALOG
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS categories (
  code         CHAR(3) NOT NULL,
  name         VARCHAR(64) NOT NULL,
  emoji        VARCHAR(8) NOT NULL DEFAULT '',
  tagline      VARCHAR(160) NULL,
  position     TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subcategories (
  code             VARCHAR(16) NOT NULL,
  category_code    CHAR(3) NOT NULL,
  name             VARCHAR(64) NOT NULL,
  position         TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (code),
  KEY idx_cat (category_code),
  CONSTRAINT fk_sub_cat FOREIGN KEY (category_code) REFERENCES categories(code) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS items (
  id                VARCHAR(32) NOT NULL,
  title             VARCHAR(255) NOT NULL,
  category_1        CHAR(3) NOT NULL,
  category_2        CHAR(3) NULL,
  subcategory_1     VARCHAR(16) NULL,
  subcategory_2     VARCHAR(16) NULL,
  status            ENUM('validated','draft','rejected') NOT NULL DEFAULT 'validated',
  difficulty        TINYINT UNSIGNED NULL,
  duration_label    VARCHAR(32) NULL,
  budget_label      VARCHAR(16) NULL,
  notes             TEXT NULL,
  added_at          DATE NULL,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cat1 (category_1),
  KEY idx_cat2 (category_2),
  KEY idx_sub1 (subcategory_1),
  KEY idx_sub2 (subcategory_2),
  KEY idx_status (status),
  FULLTEXT KEY ft_title (title),
  CONSTRAINT fk_items_cat1 FOREIGN KEY (category_1)    REFERENCES categories(code)    ON UPDATE CASCADE,
  CONSTRAINT fk_items_cat2 FOREIGN KEY (category_2)    REFERENCES categories(code)    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_items_sub1 FOREIGN KEY (subcategory_1) REFERENCES subcategories(code) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_items_sub2 FOREIGN KEY (subcategory_2) REFERENCES subcategories(code) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- USERS · AUTH
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS users (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email            VARCHAR(190) NOT NULL,
  email_verified   TINYINT(1) NOT NULL DEFAULT 0,
  password_hash    VARCHAR(255) NULL,
  pseudo           VARCHAR(64) NOT NULL DEFAULT '',
  avatar_url       VARCHAR(255) NULL,
  google_sub       VARCHAR(64) NULL,
  is_private       TINYINT(1) NOT NULL DEFAULT 0,
  streak_count     INT UNSIGNED NOT NULL DEFAULT 0,
  streak_last_at   DATE NULL,
  settings_json    JSON 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 KEY uk_email (email),
  UNIQUE KEY uk_google (google_sub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_tokens (
  token         CHAR(64) NOT NULL,
  user_id       INT UNSIGNED NOT NULL,
  user_agent    VARCHAR(255) NULL,
  ip            VARCHAR(45) NULL,
  expires_at    DATETIME NOT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (token),
  KEY idx_user (user_id),
  KEY idx_expires (expires_at),
  CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- USER LIST · EMBLEMS · IDEAS · ORDERS
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS list_entries (
  user_id       INT UNSIGNED NOT NULL,
  item_id       VARCHAR(32) NOT NULL,
  done          TINYINT(1) NOT NULL DEFAULT 0,
  done_at       DATETIME NULL,
  position      INT UNSIGNED NOT NULL DEFAULT 0,
  added_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, item_id),
  KEY idx_user_done (user_id, done),
  CONSTRAINT fk_le_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_le_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS emblems (
  code           VARCHAR(32) NOT NULL,
  name           VARCHAR(64) NOT NULL,
  description    TEXT NULL,
  rule_type      ENUM('count_total','count_category','count_subcategory','streak') NOT NULL,
  rule_target    VARCHAR(32) NULL,
  threshold      INT UNSIGNED NOT NULL DEFAULT 1,
  rarity         ENUM('common','rare','epic','legendary') NOT NULL DEFAULT 'common',
  icon           VARCHAR(32) NOT NULL DEFAULT 'medal',
  position       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (code),
  KEY idx_rule (rule_type, rule_target)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS emblem_unlocks (
  user_id        INT UNSIGNED NOT NULL,
  emblem_code    VARCHAR(32) NOT NULL,
  unlocked_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, emblem_code),
  KEY idx_user (user_id),
  CONSTRAINT fk_eu_user   FOREIGN KEY (user_id)     REFERENCES users(id)   ON DELETE CASCADE,
  CONSTRAINT fk_eu_emblem FOREIGN KEY (emblem_code) REFERENCES emblems(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ideas (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         INT UNSIGNED NULL,
  text            TEXT NOT NULL,
  category_code   CHAR(3) NULL,
  status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_notes     TEXT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_status (status),
  KEY idx_user (user_id),
  CONSTRAINT fk_ideas_user FOREIGN KEY (user_id)       REFERENCES users(id)      ON DELETE SET NULL,
  CONSTRAINT fk_ideas_cat  FOREIGN KEY (category_code) REFERENCES categories(code) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  reference           VARCHAR(16) NOT NULL,
  user_id             INT UNSIGNED NULL,
  kind                ENUM('pdf','booklet') NOT NULL,
  amount_cents        INT UNSIGNED NOT NULL,
  currency            CHAR(3) NOT NULL DEFAULT 'EUR',
  status              ENUM('pending','paid','shipped','delivered','refunded','failed') NOT NULL DEFAULT 'pending',
  payment_provider    VARCHAR(32) NULL,
  payment_ref         VARCHAR(128) NULL,
  shipping_name       VARCHAR(128) NULL,
  shipping_address    TEXT NULL,
  meta_json           JSON 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 KEY uk_reference (reference),
  KEY idx_user (user_id),
  KEY idx_status (status),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;