CREATE TABLE tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  plan ENUM('free','pro','family','business') DEFAULT 'free',
  status ENUM('active','trialing','past_due','cancelled') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','owner','member','support') DEFAULT 'member',
  native_language_code VARCHAR(16) DEFAULT 'en-GB',
  xp INT DEFAULT 0,
  level INT DEFAULT 1,
  streak_days INT DEFAULT 0,
  last_seen DATE NULL,
  email_verified_at DATETIME NULL,
  deleted_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE TABLE languages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(16) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  native_name VARCHAR(80) NOT NULL,
  enabled TINYINT(1) DEFAULT 1
);

CREATE TABLE phrase_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  sort_order INT DEFAULT 0
);

CREATE TABLE phrases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  language_id INT NOT NULL,
  category_id INT NOT NULL,
  english_uk VARCHAR(255) NOT NULL,
  translated VARCHAR(255) NOT NULL,
  phonetic VARCHAR(255) NULL,
  difficulty ENUM('survival','easy','medium','advanced') DEFAULT 'survival',
  notes TEXT NULL,
  FOREIGN KEY (language_id) REFERENCES languages(id),
  FOREIGN KEY (category_id) REFERENCES phrase_categories(id)
);

CREATE TABLE trips (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  destination_country VARCHAR(80) NOT NULL,
  destination_city VARCHAR(80) NULL,
  language_id INT NOT NULL,
  departure_date DATE NOT NULL,
  trip_length VARCHAR(50) DEFAULT '1 Week',
  purpose VARCHAR(80) DEFAULT 'Holiday',
  mode ENUM('prep','landed','completed') DEFAULT 'prep',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (language_id) REFERENCES languages(id)
);

CREATE TABLE user_progress (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  phrase_id INT NOT NULL,
  status ENUM('new','learning','complete') DEFAULT 'new',
  correct_count INT DEFAULT 0,
  wrong_count INT DEFAULT 0,
  last_practised_at DATETIME NULL,
  UNIQUE KEY uniq_user_phrase (user_id, phrase_id)
);

CREATE TABLE badges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL,
  xp_reward INT DEFAULT 0
);

CREATE TABLE user_badges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  badge_id INT NOT NULL,
  awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_badge (user_id, badge_id)
);

CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  provider VARCHAR(50) DEFAULT 'stripe',
  provider_customer_id VARCHAR(190) NULL,
  provider_subscription_id VARCHAR(190) NULL,
  plan VARCHAR(50) DEFAULT 'free',
  status VARCHAR(50) DEFAULT 'active',
  renews_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NULL,
  user_id INT NULL,
  action VARCHAR(120) NOT NULL,
  payload JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
