CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','member') NOT NULL DEFAULT 'member',
  tier VARCHAR(80) NOT NULL DEFAULT 'none',
  subscription_status VARCHAR(80) NOT NULL DEFAULT 'inactive',
  stripe_customer_id VARCHAR(190) NULL,
  password_reset_required TINYINT(1) NOT NULL DEFAULT 0,
  credits INT NOT NULL DEFAULT 0,
  profile_json JSON NULL,
  collection_json JSON NULL,
  created_at DATETIME(3) NOT NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS books (
  id VARCHAR(64) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255) NULL,
  narrator VARCHAR(255) NULL,
  genre VARCHAR(120) NULL,
  description TEXT NULL,
  tier VARCHAR(80) NOT NULL DEFAULT 'starter',
  status VARCHAR(40) NOT NULL DEFAULT 'published',
  featured TINYINT(1) NOT NULL DEFAULT 0,
  cover_url TEXT NULL,
  book_url TEXT NULL,
  audio_url TEXT NULL,
  duration_seconds INT NOT NULL DEFAULT 0,
  content_json JSON NULL,
  extra_json JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS book_chapters (
  id VARCHAR(64) PRIMARY KEY,
  book_id VARCHAR(64) NOT NULL,
  chapter_number INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  audio_url TEXT NULL,
  duration_seconds INT NOT NULL DEFAULT 0,
  extra_json JSON NULL,
  CONSTRAINT fk_chapter_book FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  INDEX idx_chapter_book_number (book_id, chapter_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_progress (
  user_id VARCHAR(64) NOT NULL,
  book_id VARCHAR(64) NOT NULL,
  mode ENUM('read','audio') NOT NULL,
  position_seconds DECIMAL(14,3) NOT NULL DEFAULT 0,
  chapter_number INT NOT NULL DEFAULT 0,
  percent DECIMAL(7,3) NOT NULL DEFAULT 0,
  updated_at DATETIME(3) NOT NULL,
  PRIMARY KEY (user_id, book_id, mode),
  INDEX idx_progress_book (book_id),
  CONSTRAINT fk_progress_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_progress_book FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id VARCHAR(64) PRIMARY KEY,
  external_id VARCHAR(190) NULL UNIQUE,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(12) NOT NULL DEFAULT 'USD',
  email VARCHAR(190) NULL,
  tier VARCHAR(80) NULL,
  provider VARCHAR(80) NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'paid',
  created_at DATETIME(3) NOT NULL,
  extra_json JSON NULL,
  INDEX idx_payments_created (created_at),
  INDEX idx_payments_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_events (
  id VARCHAR(64) PRIMARY KEY,
  user_id VARCHAR(64) NULL,
  book_id VARCHAR(64) NULL,
  event_type VARCHAR(100) NOT NULL,
  chapter_number INT NULL,
  metadata_json JSON NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX idx_events_type_date (event_type, created_at),
  INDEX idx_events_book_date (book_id, created_at),
  INDEX idx_events_user_date (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(190) PRIMARY KEY,
  setting_value JSON NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id VARCHAR(64) PRIMARY KEY,
  user_id VARCHAR(64) NULL,
  email VARCHAR(190) NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  used_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX idx_reset_email (email),
  INDEX idx_reset_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;