-- Beezifi Workspace - Production Database Schema
-- MariaDB 10.6+

SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- ─────────────────────────────────────────────────────────────────────────────
-- ORGANIZATIONS (Tenants)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS organizations (
  id            CHAR(36)     NOT NULL DEFAULT (UUID()),
  name          VARCHAR(255) NOT NULL,
  slug          VARCHAR(100) NOT NULL,
  description   TEXT,
  avatar_url    VARCHAR(500),
  plan          ENUM('free','starter','growth','scale','custom') NOT NULL DEFAULT 'free',
  settings      JSON         NOT NULL DEFAULT ('{}'),
  is_active     BOOLEAN      NOT NULL DEFAULT TRUE,
  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 uq_slug (slug),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- USERS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  email           VARCHAR(255) NOT NULL,
  username        VARCHAR(50)  NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  display_name    VARCHAR(100),
  avatar_url      VARCHAR(500),
  status          ENUM('active','away','dnd','offline') NOT NULL DEFAULT 'offline',
  status_message  VARCHAR(255),
  timezone        VARCHAR(50)  NOT NULL DEFAULT 'UTC',
  totp_secret     VARCHAR(64)  NULL,
  totp_enabled    BOOLEAN      NOT NULL DEFAULT FALSE,
  dnd_enabled     BOOLEAN      NOT NULL DEFAULT FALSE,
  dnd_start_hour  TINYINT      NOT NULL DEFAULT 22,
  dnd_end_hour    TINYINT      NOT NULL DEFAULT 8,
  is_system_admin BOOLEAN      NOT NULL DEFAULT FALSE,
  email_verified  BOOLEAN      NOT NULL DEFAULT FALSE,
  last_seen_at    TIMESTAMP    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 uq_email (email),
  INDEX idx_username (username),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- MEMBERSHIPS (User ↔ Organization)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS memberships (
  id              CHAR(36)  NOT NULL DEFAULT (UUID()),
  user_id         CHAR(36)  NOT NULL,
  organization_id CHAR(36)  NOT NULL,
  role            ENUM('owner','admin','member','guest') NOT NULL DEFAULT 'member',
  invited_by      CHAR(36)  NULL,
  is_active       BOOLEAN   NOT NULL DEFAULT TRUE,
  joined_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_org (user_id, organization_id),
  FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by)      REFERENCES users(id)         ON DELETE SET NULL,
  INDEX idx_org_members (organization_id, role, is_active),
  INDEX idx_user_orgs (user_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- CHANNELS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS channels (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id CHAR(36)     NOT NULL,
  name            VARCHAR(100) NOT NULL,
  description     TEXT,
  topic           VARCHAR(255),
  type            ENUM('public','private','dm','group_dm') NOT NULL DEFAULT 'public',
  created_by      CHAR(36)     NOT NULL,
  is_archived     BOOLEAN      NOT NULL DEFAULT FALSE,
  is_default      BOOLEAN      NOT NULL DEFAULT FALSE,
  is_readonly     BOOLEAN      NOT NULL DEFAULT FALSE,
  settings        JSON         NOT NULL DEFAULT ('{}'),
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by)      REFERENCES users(id),
  UNIQUE KEY uq_org_channel_name (organization_id, name, type),
  INDEX idx_org_channels (organization_id, type, is_archived),
  INDEX idx_default_channels (organization_id, is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- CHANNEL MEMBERS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS channel_members (
  id                     CHAR(36)  NOT NULL DEFAULT (UUID()),
  channel_id             CHAR(36)  NOT NULL,
  user_id                CHAR(36)  NOT NULL,
  role                   ENUM('admin','moderator','member') NOT NULL DEFAULT 'member',
  notification_pref      ENUM('all','mentions','nothing')   NOT NULL DEFAULT 'all',
  last_read_message_id   CHAR(36)  NULL,
  is_muted               BOOLEAN   NOT NULL DEFAULT FALSE,
  joined_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_channel_user (channel_id, user_id),
  FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  INDEX idx_user_channels (user_id),
  INDEX idx_channel_users (channel_id, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- MESSAGES
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
  id                CHAR(36)   NOT NULL DEFAULT (UUID()),
  channel_id        CHAR(36)   NOT NULL,
  organization_id   CHAR(36)   NOT NULL,
  user_id           CHAR(36)   NOT NULL,
  parent_message_id CHAR(36)   NULL,
  content           TEXT       NOT NULL,
  type              ENUM('text','file','system','code','poll') NOT NULL DEFAULT 'text',
  is_edited         BOOLEAN    NOT NULL DEFAULT FALSE,
  is_deleted        BOOLEAN    NOT NULL DEFAULT FALSE,
  deleted_at        TIMESTAMP  NULL,
  edited_at         TIMESTAMP  NULL,
  reply_count       INT        NOT NULL DEFAULT 0,
  metadata          JSON       NOT NULL DEFAULT ('{}'),
  created_at        TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (channel_id)        REFERENCES channels(id)      ON DELETE CASCADE,
  FOREIGN KEY (organization_id)   REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)           REFERENCES users(id),
  FOREIGN KEY (parent_message_id) REFERENCES messages(id)      ON DELETE CASCADE,
  INDEX idx_channel_messages  (channel_id, created_at),
  INDEX idx_thread_messages   (parent_message_id, created_at),
  INDEX idx_org_messages      (organization_id, created_at),
  FULLTEXT INDEX ft_content   (content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- REACTIONS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reactions (
  id         CHAR(36)    NOT NULL DEFAULT (UUID()),
  message_id CHAR(36)    NOT NULL,
  user_id    CHAR(36)    NOT NULL,
  emoji      VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_msg_user_emoji (message_id, user_id, emoji),
  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  INDEX idx_message_reactions (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- FILES
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS files (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id CHAR(36)     NOT NULL,
  uploaded_by     CHAR(36)     NOT NULL,
  message_id      CHAR(36)     NULL,
  channel_id      CHAR(36)     NULL,
  filename        VARCHAR(255) NOT NULL,
  original_name   VARCHAR(255) NOT NULL,
  mime_type       VARCHAR(100) NOT NULL,
  size            BIGINT       NOT NULL DEFAULT 0,
  storage_path    VARCHAR(500) NOT NULL,
  is_public       BOOLEAN      NOT NULL DEFAULT FALSE,
  download_count  INT          NOT NULL DEFAULT 0,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by)     REFERENCES users(id),
  FOREIGN KEY (message_id)      REFERENCES messages(id) ON DELETE SET NULL,
  FOREIGN KEY (channel_id)      REFERENCES channels(id) ON DELETE SET NULL,
  INDEX idx_org_files     (organization_id, created_at),
  INDEX idx_channel_files (channel_id),
  INDEX idx_message_files (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- NOTIFICATIONS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
  id              CHAR(36)  NOT NULL DEFAULT (UUID()),
  user_id         CHAR(36)  NOT NULL,
  organization_id CHAR(36)  NOT NULL,
  type            ENUM('mention','reply','dm','channel_invite','system') NOT NULL,
  message_id      CHAR(36)  NULL,
  channel_id      CHAR(36)  NULL,
  from_user_id    CHAR(36)  NULL,
  content         TEXT,
  is_read         BOOLEAN   NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (message_id)      REFERENCES messages(id)      ON DELETE CASCADE,
  FOREIGN KEY (channel_id)      REFERENCES channels(id)      ON DELETE CASCADE,
  FOREIGN KEY (from_user_id)    REFERENCES users(id)         ON DELETE SET NULL,
  INDEX idx_user_notifications (user_id, is_read, created_at),
  INDEX idx_org_notifications  (organization_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- AUDIT LOGS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id CHAR(36)     NULL,
  user_id         CHAR(36)     NULL,
  action          VARCHAR(100) NOT NULL,
  resource_type   VARCHAR(50)  NULL,
  resource_id     VARCHAR(36)  NULL,
  ip_address      VARCHAR(45)  NULL,
  user_agent      TEXT,
  details         JSON         NOT NULL DEFAULT ('{}'),
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_org_audit    (organization_id, created_at),
  INDEX idx_user_audit   (user_id, created_at),
  INDEX idx_action_audit (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- REFRESH TOKENS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS refresh_tokens (
  id         CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id    CHAR(36)     NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP    NOT NULL,
  is_revoked BOOLEAN      NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_token_hash (token_hash),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_tokens (user_id, is_revoked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- WEBHOOKS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS webhooks (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id CHAR(36)     NOT NULL,
  channel_id      CHAR(36)     NULL,
  name            VARCHAR(100) NOT NULL,
  url             VARCHAR(500) NULL,
  secret          VARCHAR(255) NULL,
  events          JSON         NOT NULL DEFAULT ('[]'),
  is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
  created_by      CHAR(36)     NOT NULL,
  last_triggered  TIMESTAMP    NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (channel_id)      REFERENCES channels(id)      ON DELETE SET NULL,
  FOREIGN KEY (created_by)      REFERENCES users(id),
  INDEX idx_org_webhooks (organization_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PINNED MESSAGES
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pinned_messages (
  id         CHAR(36)  NOT NULL DEFAULT (UUID()),
  channel_id CHAR(36)  NOT NULL,
  message_id CHAR(36)  NOT NULL,
  pinned_by  CHAR(36)  NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_channel_message (channel_id, message_id),
  FOREIGN KEY (channel_id) REFERENCES channels(id)  ON DELETE CASCADE,
  FOREIGN KEY (message_id) REFERENCES messages(id)  ON DELETE CASCADE,
  FOREIGN KEY (pinned_by)  REFERENCES users(id)      ON DELETE CASCADE,
  INDEX idx_channel_pins (channel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PASSWORD RESET TOKENS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id         CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id    CHAR(36)     NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP    NOT NULL,
  used_at    TIMESTAMP    NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_token_hash (token_hash),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_reset_tokens (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- SAVED MESSAGES (Bookmarks)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS saved_messages (
  id         CHAR(36)  NOT NULL DEFAULT (UUID()),
  user_id    CHAR(36)  NOT NULL,
  message_id CHAR(36)  NOT NULL,
  org_id     CHAR(36)  NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_msg (user_id, message_id),
  FOREIGN KEY (user_id)    REFERENCES users(id)         ON DELETE CASCADE,
  FOREIGN KEY (message_id) REFERENCES messages(id)      ON DELETE CASCADE,
  FOREIGN KEY (org_id)     REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_user_saved (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- POLL VOTES
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS poll_votes (
  id           CHAR(36) NOT NULL DEFAULT (UUID()),
  message_id   CHAR(36) NOT NULL,
  user_id      CHAR(36) NOT NULL,
  option_index TINYINT  NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_poll (message_id, user_id),
  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  INDEX idx_poll_votes (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- SCHEDULED MESSAGES
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS scheduled_messages (
  id         CHAR(36)  NOT NULL DEFAULT (UUID()),
  channel_id CHAR(36)  NOT NULL,
  org_id     CHAR(36)  NOT NULL,
  user_id    CHAR(36)  NOT NULL,
  content    TEXT      NOT NULL,
  type       ENUM('text','poll') NOT NULL DEFAULT 'text',
  metadata   JSON      NOT NULL DEFAULT ('{}'),
  send_at    DATETIME  NOT NULL,
  sent_at    DATETIME  NULL,
  cancelled  BOOLEAN   NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (channel_id) REFERENCES channels(id)      ON DELETE CASCADE,
  FOREIGN KEY (org_id)     REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)         ON DELETE CASCADE,
  INDEX idx_pending (send_at, sent_at, cancelled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PUSH SUBSCRIPTIONS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id                CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id           CHAR(36)     NOT NULL,
  endpoint          TEXT         NOT NULL,
  subscription_json JSON         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),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_push_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- BILLING (one-time payment access tracking per organization)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS subscriptions (
  id                  CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id     CHAR(36)     NOT NULL,
  stripe_customer_id  VARCHAR(255) NOT NULL,
  plan                ENUM('starter','growth','scale') NOT NULL DEFAULT 'starter',
  status              ENUM('active','expired') NOT NULL DEFAULT 'expired',
  access_expires_at   TIMESTAMP    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 uq_org_subscription (organization_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_stripe_customer (stripe_customer_id),
  INDEX idx_access_expiry (organization_id, access_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PAYMENT HISTORY (Stripe PaymentIntent records per organization)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payment_history (
  id                        CHAR(36)     NOT NULL DEFAULT (UUID()),
  organization_id           CHAR(36)     NOT NULL,
  stripe_payment_intent_id  VARCHAR(255) NOT NULL,
  stripe_charge_id          VARCHAR(255) NULL,
  amount_cents              INT          NOT NULL,
  currency                  VARCHAR(3)   NOT NULL DEFAULT 'usd',
  status                    ENUM('paid','failed','pending') NOT NULL DEFAULT 'pending',
  plan                      VARCHAR(50)  NULL,
  description               VARCHAR(500),
  receipt_url               VARCHAR(500),
  access_start              TIMESTAMP    NULL,
  access_end                TIMESTAMP    NULL,
  created_at                TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_stripe_payment_intent (stripe_payment_intent_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_org_payment_history (organization_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- BANNED ACCOUNTS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS banned_accounts (
  id            CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id       CHAR(36)     NOT NULL,
  banned_by     CHAR(36)     NOT NULL,
  reason        TEXT         NOT NULL,
  is_permanent  BOOLEAN      NOT NULL DEFAULT FALSE,
  banned_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unbanned_at   TIMESTAMP    NULL,
  unbanned_by   CHAR(36)     NULL,
  unban_reason  TEXT         NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (banned_by)   REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (unbanned_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_banned_user   (user_id, unbanned_at),
  INDEX idx_banned_active (unbanned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- SYSTEM ADMIN LOGS
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS system_admin_logs (
  id             CHAR(36)     NOT NULL DEFAULT (UUID()),
  admin_id       CHAR(36)     NOT NULL,
  action         VARCHAR(100) NOT NULL,
  resource_type  VARCHAR(50)  NULL,
  resource_id    VARCHAR(36)  NULL,
  target_user_id CHAR(36)     NULL,
  details        JSON         NOT NULL DEFAULT ('{}'),
  ip_address     VARCHAR(45)  NULL,
  user_agent     TEXT,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sal_admin    (admin_id, created_at),
  INDEX idx_sal_action   (action, created_at),
  INDEX idx_sal_target   (target_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PROMO CODE BATCHES (Admin-generated batches of promo codes)
-- plan NULL = extend the organization's current plan; a specific value forces
-- that plan regardless of what the org currently holds.
-- duration_months: 6 or 12, enforced at the application layer.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS promo_code_batches (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  created_by      CHAR(36)     NOT NULL,
  name            VARCHAR(100) NOT NULL,
  plan            ENUM('starter','growth','scale') NULL,
  duration_months TINYINT      NOT NULL,
  quantity        SMALLINT     NOT NULL,
  expires_at      TIMESTAMP    NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_batch_admin (created_by, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- PROMO CODES
-- Each code is unique and single-use.  plan and duration are inherited from the
-- parent batch via JOIN; usage details are written here on redemption.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS promo_codes (
  id              CHAR(36)    NOT NULL DEFAULT (UUID()),
  batch_id        CHAR(36)    NOT NULL,
  code            VARCHAR(32) NOT NULL,
  is_used         BOOLEAN     NOT NULL DEFAULT FALSE,
  is_revoked      BOOLEAN     NOT NULL DEFAULT FALSE,
  used_by_org_id  CHAR(36)    NULL,
  used_by_user_id CHAR(36)    NULL,
  used_at         TIMESTAMP   NULL,
  created_at      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_code (code),
  FOREIGN KEY (batch_id)        REFERENCES promo_code_batches(id) ON DELETE CASCADE,
  FOREIGN KEY (used_by_org_id)  REFERENCES organizations(id)      ON DELETE SET NULL,
  FOREIGN KEY (used_by_user_id) REFERENCES users(id)              ON DELETE SET NULL,
  INDEX idx_code_status (code, is_used, is_revoked),
  INDEX idx_batch_codes (batch_id, is_used),
  INDEX idx_org_promos  (used_by_org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- BANNED ACCOUNTS (Account suspension/banning system)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS banned_accounts (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id         CHAR(36)     NOT NULL,
  banned_by       CHAR(36)     NOT NULL,
  reason          TEXT         NOT NULL,
  is_permanent    BOOLEAN      NOT NULL DEFAULT FALSE,
  banned_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unbanned_at     TIMESTAMP    NULL,
  unbanned_by     CHAR(36)     NULL,
  unban_reason    TEXT         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 uq_user_active_ban (user_id),
  FOREIGN KEY (user_id)     REFERENCES users(id)         ON DELETE CASCADE,
  FOREIGN KEY (banned_by)   REFERENCES users(id),
  FOREIGN KEY (unbanned_by) REFERENCES users(id)         ON DELETE SET NULL,
  INDEX idx_banned_users (user_id, banned_at),
  INDEX idx_active_bans (unbanned_at, banned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- SYSTEM ADMIN LOGS (Audit trail for system admin actions)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS system_admin_logs (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  admin_id        CHAR(36)     NOT NULL,
  action          VARCHAR(100) NOT NULL,
  resource_type   VARCHAR(50)  NULL,
  resource_id     CHAR(36)     NULL,
  target_user_id  CHAR(36)     NULL,
  details         JSON         NOT NULL DEFAULT ('{}'),
  ip_address      VARCHAR(45)  NULL,
  user_agent      TEXT,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (admin_id)     REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_admin_logs (admin_id, created_at),
  INDEX idx_action_logs (action, created_at),
  INDEX idx_target_logs (target_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- SYSTEM ADMIN SESSIONS (Track system admin login sessions)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS system_admin_sessions (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  admin_id        CHAR(36)     NOT NULL,
  session_token   VARCHAR(255) NOT NULL,
  ip_address      VARCHAR(45)  NOT NULL,
  user_agent      TEXT,
  totp_verified   BOOLEAN      NOT NULL DEFAULT FALSE,
  expires_at      TIMESTAMP    NOT NULL,
  last_activity   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_session_token (session_token),
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_admin_sessions (admin_id, expires_at),
  INDEX idx_active_sessions (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- CRYPTO PAYMENT SESSIONS (MooChedda — B2B / USDC)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS crypto_payment_sessions (
  id               CHAR(36)      NOT NULL DEFAULT (UUID()),
  organization_id  CHAR(36)      NOT NULL,
  invoice_id       VARCHAR(255)  NOT NULL,
  plan             VARCHAR(50)   NOT NULL,
  duration_months  TINYINT       NOT NULL,
  currency         VARCHAR(10)   NOT NULL,
  amount           DECIMAL(14,4) NOT NULL,
  status           ENUM('pending','paid','expired') NOT NULL DEFAULT 'pending',
  payment_url      TEXT          NULL,
  created_at       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoice (invoice_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_org_status (organization_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
