-- Uni Organiser — MariaDB schema (v1, phases 1–2)
-- Applied by the Phinx migration in db/migrations, or directly with:
--   mysql uni_organiser < db/sql/001_initial.sql
-- Collection tables mirror shared/schemas/*.json column for column. Times are UTC.

SET NAMES utf8mb4;

-- ---------------------------------------------------------------- server-only tables

CREATE TABLE users (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  email         VARCHAR(255) NOT NULL,
  display_name  VARCHAR(120) NOT NULL,
  timezone      VARCHAR(64)  NOT NULL DEFAULT 'Europe/London',
  password_hash VARCHAR(255) NULL,
  created_at    DATETIME(3)  NOT NULL,
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE spaces (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  created_at DATETIME(3)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE space_members (
  space_id CHAR(36) NOT NULL,
  user_id  CHAR(36) NOT NULL,
  role     ENUM('owner','editor','viewer') NOT NULL DEFAULT 'owner',
  PRIMARY KEY (space_id, user_id),
  KEY ix_space_members_user (user_id),
  CONSTRAINT fk_sm_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE,
  CONSTRAINT fk_sm_user  FOREIGN KEY (user_id)  REFERENCES users (id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per signed-in device. The cookie holds a random token; only its SHA-256 is stored.
CREATE TABLE devices (
  id           CHAR(36)     NOT NULL PRIMARY KEY,
  user_id      CHAR(36)     NOT NULL,
  name         VARCHAR(120) NOT NULL,
  user_agent   VARCHAR(255) NOT NULL DEFAULT '',
  session_hash CHAR(64)     NOT NULL,
  created_at   DATETIME(3)  NOT NULL,
  last_seen_at DATETIME(3)  NOT NULL,
  expires_at   DATETIME(3)  NOT NULL,
  revoked_at   DATETIME(3)  NULL,
  UNIQUE KEY uq_devices_session (session_hash),
  KEY ix_devices_user (user_id),
  CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE passkeys (
  id            CHAR(36)        NOT NULL PRIMARY KEY,
  user_id       CHAR(36)        NOT NULL,
  credential_id VARBINARY(1023) NOT NULL,
  cred_hash     CHAR(64)        NOT NULL,
  public_key    TEXT            NOT NULL,
  sign_count    INT UNSIGNED    NOT NULL DEFAULT 0,
  name          VARCHAR(120)    NOT NULL,
  created_at    DATETIME(3)     NOT NULL,
  last_used_at  DATETIME(3)     NULL,
  UNIQUE KEY uq_passkeys_cred (cred_hash),
  KEY ix_passkeys_user (user_id),
  CONSTRAINT fk_passkeys_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auth_challenges (
  id         CHAR(36)    NOT NULL PRIMARY KEY,
  purpose    ENUM('register','login') NOT NULL,
  user_id    CHAR(36)    NULL,
  challenge  VARBINARY(64) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  KEY ix_challenges_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
  id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip  VARCHAR(45)     NOT NULL,
  at  DATETIME(3)     NOT NULL,
  KEY ix_login_attempts_ip (ip, at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One-row counter: every synced write takes the next value as its server_seq.
CREATE TABLE seq_counter (
  id    TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  value BIGINT UNSIGNED  NOT NULL
) ENGINE=InnoDB;
INSERT INTO seq_counter (id, value) VALUES (1, 0);

-- Small key/value table for cron heartbeats and housekeeping timestamps.
CREATE TABLE meta (
  name  VARCHAR(64) NOT NULL PRIMARY KEY,
  value TEXT        NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- synced collections (12)

-- Shopping lists and to-do lists
CREATE TABLE `lists` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `kind` VARCHAR(8) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `colour` VARCHAR(16) NOT NULL,
  `module_code` VARCHAR(32) NULL,
  `position` VARCHAR(64) NOT NULL,
  KEY ix_lists_space_seq (space_id, server_seq),
  KEY ix_lists_seq (server_seq),
  CONSTRAINT fk_lists_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Items on a shopping list
CREATE TABLE `shopping_items` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `list_id` CHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `qty` DOUBLE NOT NULL,
  `unit` VARCHAR(16) NOT NULL,
  `category` VARCHAR(60) NOT NULL,
  `checked` TINYINT(1) NOT NULL,
  `note` TEXT NOT NULL,
  `recipe_id` CHAR(36) NULL,
  `position` VARCHAR(64) NOT NULL,
  KEY ix_shopping_items_space_seq (space_id, server_seq),
  KEY ix_shopping_items_seq (server_seq),
  CONSTRAINT fk_shopping_items_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saved recipes with their ingredients
CREATE TABLE `recipes` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `name` VARCHAR(200) NOT NULL,
  `servings` INT NOT NULL,
  `source_url` TEXT NOT NULL,
  `notes` MEDIUMTEXT NOT NULL,
  `ingredients` LONGTEXT NOT NULL CHECK (JSON_VALID(`ingredients`)),
  KEY ix_recipes_space_seq (space_id, server_seq),
  KEY ix_recipes_seq (server_seq),
  CONSTRAINT fk_recipes_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One meal in one slot of the week grid
CREATE TABLE `meal_entries` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `date` DATE NOT NULL,
  `slot` VARCHAR(60) NOT NULL,
  `recipe_id` CHAR(36) NULL,
  `text` TEXT NOT NULL,
  `servings` INT NOT NULL,
  `position` VARCHAR(64) NOT NULL,
  KEY ix_meal_entries_space_seq (space_id, server_seq),
  KEY ix_meal_entries_seq (server_seq),
  CONSTRAINT fk_meal_entries_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- To-dos, subtasks (parent_id set) and deadlines (is_deadline)
CREATE TABLE `tasks` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `list_id` CHAR(36) NOT NULL,
  `parent_id` CHAR(36) NULL,
  `title` TEXT NOT NULL,
  `notes` MEDIUMTEXT NOT NULL,
  `due_at` DATETIME(3) NULL,
  `all_day` TINYINT(1) NOT NULL,
  `priority` VARCHAR(6) NOT NULL,
  `status` VARCHAR(4) NOT NULL,
  `completed_at` DATETIME(3) NULL,
  `rrule` TEXT NULL,
  `is_deadline` TINYINT(1) NOT NULL,
  `module_code` VARCHAR(32) NULL,
  `note_id` CHAR(36) NULL,
  `tags` LONGTEXT NOT NULL CHECK (JSON_VALID(`tags`)),
  `position` VARCHAR(64) NOT NULL,
  KEY ix_tasks_space_seq (space_id, server_seq),
  KEY ix_tasks_seq (server_seq),
  CONSTRAINT fk_tasks_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Calendars: the app's own, iCloud ones and .ics feeds
CREATE TABLE `calendars` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `source` VARCHAR(6) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `colour` VARCHAR(16) NOT NULL,
  `visible` TINYINT(1) NOT NULL,
  `writable` TINYINT(1) NOT NULL,
  `remote_url` TEXT NULL,
  KEY ix_calendars_space_seq (space_id, server_seq),
  KEY ix_calendars_seq (server_seq),
  CONSTRAINT fk_calendars_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Calendar events, including ones synced from iCloud
CREATE TABLE `events` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `calendar_id` CHAR(36) NOT NULL,
  `title` TEXT NOT NULL,
  `start_at` DATETIME(3) NOT NULL,
  `end_at` DATETIME(3) NOT NULL,
  `all_day` TINYINT(1) NOT NULL,
  `location` TEXT NOT NULL,
  `rrule` TEXT NULL,
  `ical_uid` VARCHAR(255) NOT NULL,
  `etag` VARCHAR(255) NULL,
  KEY ix_events_space_seq (space_id, server_seq),
  KEY ix_events_seq (server_seq),
  CONSTRAINT fk_events_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reminders on tasks and events
CREATE TABLE `reminders` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `target_type` VARCHAR(5) NOT NULL,
  `target_id` CHAR(36) NOT NULL,
  `remind_at` DATETIME(3) NOT NULL,
  `offset_minutes` INT NULL,
  `snoozed_until` DATETIME(3) NULL,
  `done` TINYINT(1) NOT NULL,
  KEY ix_reminders_space_seq (space_id, server_seq),
  KEY ix_reminders_seq (server_seq),
  CONSTRAINT fk_reminders_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Meeting-note folders
CREATE TABLE `folders` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `parent_id` CHAR(36) NULL,
  `name` VARCHAR(200) NOT NULL,
  `pinned` TINYINT(1) NOT NULL,
  `position` VARCHAR(64) NOT NULL,
  KEY ix_folders_space_seq (space_id, server_seq),
  KEY ix_folders_seq (server_seq),
  CONSTRAINT fk_folders_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Meeting notes (Markdown)
CREATE TABLE `notes` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `folder_id` CHAR(36) NOT NULL,
  `title` TEXT NOT NULL,
  `body_md` MEDIUMTEXT NOT NULL,
  `meeting_at` DATETIME(3) NULL,
  `attendees` LONGTEXT NOT NULL CHECK (JSON_VALID(`attendees`)),
  `tags` LONGTEXT NOT NULL CHECK (JSON_VALID(`tags`)),
  `has_conflict` TINYINT(1) NOT NULL,
  KEY ix_notes_space_seq (space_id, server_seq),
  KEY ix_notes_seq (server_seq),
  CONSTRAINT fk_notes_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Files attached to notes (rows only; files stay on the server)
CREATE TABLE `attachments` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `note_id` CHAR(36) NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `mime` VARCHAR(120) NOT NULL,
  `size_bytes` BIGINT NOT NULL,
  `sha256` VARCHAR(64) NOT NULL,
  `storage_key` VARCHAR(120) NOT NULL,
  `keep_offline` TINYINT(1) NOT NULL,
  KEY ix_attachments_space_seq (space_id, server_seq),
  KEY ix_attachments_seq (server_seq),
  CONSTRAINT fk_attachments_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One settings document per space: meal slots, aisle order, reminder defaults, theme, shopping item history
CREATE TABLE `settings` (
  `id`         CHAR(36)        NOT NULL PRIMARY KEY,
  `space_id`   CHAR(36)        NOT NULL,
  `created_by` CHAR(36)        NOT NULL,
  `updated_at` DATETIME(3)     NOT NULL,
  `server_seq` BIGINT UNSIGNED NOT NULL,
  `_deleted`   TINYINT(1)      NOT NULL DEFAULT 0,
  `meal_slots` LONGTEXT NOT NULL CHECK (JSON_VALID(`meal_slots`)),
  `aisle_order` LONGTEXT NOT NULL CHECK (JSON_VALID(`aisle_order`)),
  `reminder_defaults` LONGTEXT NOT NULL CHECK (JSON_VALID(`reminder_defaults`)),
  `theme` VARCHAR(6) NOT NULL,
  `item_history` LONGTEXT NOT NULL CHECK (JSON_VALID(`item_history`)),
  KEY ix_settings_space_seq (space_id, server_seq),
  KEY ix_settings_seq (server_seq),
  CONSTRAINT fk_settings_space FOREIGN KEY (space_id) REFERENCES spaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;