-- Leafzine Database Schema
-- Run: mysql -u root -p leafzine < schema.sql

CREATE DATABASE IF NOT EXISTS leafzine CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE leafzine;

-- Users
CREATE TABLE IF NOT EXISTS users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255)  NOT NULL,
  email       VARCHAR(255)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  stripe_customer_id VARCHAR(255),
  bio         TEXT,
  avatar_url  VARCHAR(500),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Books
CREATE TABLE IF NOT EXISTS books (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  user_id          INT NOT NULL,
  title            VARCHAR(500) NOT NULL,
  author           VARCHAR(255),
  description      TEXT,
  cover_image      VARCHAR(500),
  is_published     TINYINT(1) DEFAULT 0,
  is_subscription_required TINYINT(1) DEFAULT 0,
  book_size        VARCHAR(50) DEFAULT 'trade',
  pages_json       LONGTEXT NOT NULL DEFAULT '[]',
  page_count       INT DEFAULT 0,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Subscription plans (authors set this up so readers can subscribe)
CREATE TABLE IF NOT EXISTS subscription_plans (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  user_id          INT NOT NULL UNIQUE,
  stripe_product_id VARCHAR(255),
  stripe_price_id  VARCHAR(255),
  name             VARCHAR(255) NOT NULL,
  description      TEXT,
  price_monthly    DECIMAL(10,2) NOT NULL,
  is_active        TINYINT(1) DEFAULT 1,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Reader subscriptions to author libraries
CREATE TABLE IF NOT EXISTS subscriptions (
  id                     INT AUTO_INCREMENT PRIMARY KEY,
  subscriber_id          INT NOT NULL,
  author_id              INT NOT NULL,
  stripe_subscription_id VARCHAR(255),
  status                 VARCHAR(50) DEFAULT 'active',
  created_at             TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at             TIMESTAMP NULL,
  UNIQUE KEY unique_sub (subscriber_id, author_id),
  FOREIGN KEY (subscriber_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id)     REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Sessions (for express-mysql-session)
CREATE TABLE IF NOT EXISTS sessions (
  session_id VARCHAR(128) NOT NULL PRIMARY KEY,
  expires    INT(11) UNSIGNED NOT NULL,
  data       MEDIUMTEXT
) ENGINE=InnoDB;
