-- Big Boyz Body — MySQL schema (run via npm run db:migrate)

CREATE TABLE IF NOT EXISTS users (
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  password VARCHAR(255) NULL,
  google_id VARCHAR(64) NULL,
  onboarding_completed TINYINT(1) NOT NULL DEFAULT 0,
  role ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (email),
  UNIQUE KEY uk_users_google_id (google_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Safe migrations for existing databases (ignored if columns exist)
-- ALTER TABLE users MODIFY password VARCHAR(255) NULL;
-- ALTER TABLE users ADD COLUMN google_id VARCHAR(64) NULL;
-- ALTER TABLE users ADD COLUMN onboarding_completed TINYINT(1) NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS orders (
  id CHAR(36) NOT NULL,
  user_email VARCHAR(255) NOT NULL,
  total_inr INT NOT NULL,
  discount_inr INT NOT NULL DEFAULT 0,
  coupon_code VARCHAR(64) NULL,
  status VARCHAR(32) NOT NULL,
  razorpay_order_id VARCHAR(64) NULL,
  razorpay_payment_id VARCHAR(64) NULL,
  razorpay_subscription_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_orders_user_email (user_email),
  INDEX idx_orders_razorpay_order (razorpay_order_id),
  INDEX idx_orders_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id CHAR(36) NOT NULL,
  order_id CHAR(36) NOT NULL,
  product_id VARCHAR(64) NOT NULL,
  title VARCHAR(512) NOT NULL,
  amount_inr INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX idx_order_items_order (order_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS entitlements (
  user_email VARCHAR(255) NOT NULL,
  product_id VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_email, product_id),
  INDEX idx_entitlements_email (user_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_views (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  path VARCHAR(512) NOT NULL,
  visitor_id CHAR(36) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_page_views_created (created_at),
  INDEX idx_page_views_visitor_created (visitor_id, created_at),
  INDEX idx_page_views_path_created (path(191), created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;