schema.sql

-- ============================================================
--  Notion Clone — Full Database Schema
--  Compatible with cPanel shared hosting (MySQL 5.7+ / 8.0+)
--  Charset: utf8mb4
--  All UUIDs are generated by the application (Sequelize)
--  JSON column defaults handled by application layer
--
--  HOW TO IMPORT IN CPANEL:
--  1. Create a database + user in cPanel → MySQL Databases
--  2. Open phpMyAdmin, select your database
--  3. Click Import → choose this file → Go
--
--  UPDATE server/.env with your cPanel credentials:
--    DB_HOST=localhost
--    DB_NAME=cpanelusername_notion_clone
--    DB_USER=cpanelusername_dbuser
--    DB_PASSWORD=your_db_password
-- ============================================================

-- ------------------------------------------------------------
-- 1. users
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            CHAR(36)      NOT NULL,
  name          VARCHAR(255)  NOT NULL,
  email         VARCHAR(255)  NOT NULL,
  password_hash VARCHAR(255)  NOT NULL,
  avatar_url    VARCHAR(500)  DEFAULT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. workspaces
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS workspaces (
  id         CHAR(36)     NOT NULL,
  name       VARCHAR(255) NOT NULL,
  icon       VARCHAR(255) DEFAULT NULL,
  owner_id   CHAR(36)     NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_workspaces_owner (owner_id),
  CONSTRAINT fk_workspaces_owner
    FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. workspace_members
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS workspace_members (
  id           CHAR(36)                       NOT NULL,
  workspace_id CHAR(36)                       NOT NULL,
  user_id      CHAR(36)                       NOT NULL,
  role         ENUM('owner','editor','viewer') NOT NULL DEFAULT 'editor',
  created_at   DATETIME                       NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_ws_member (workspace_id, user_id),
  KEY fk_wm_workspace (workspace_id),
  KEY fk_wm_user      (user_id),
  CONSTRAINT fk_wm_workspace
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
  CONSTRAINT fk_wm_user
    FOREIGN KEY (user_id)      REFERENCES users (id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. pages
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pages (
  id              CHAR(36)     NOT NULL,
  workspace_id    CHAR(36)     NOT NULL,
  parent_id       CHAR(36)     DEFAULT NULL,
  created_by      CHAR(36)     NOT NULL,
  title           VARCHAR(500) NOT NULL DEFAULT 'Untitled',
  icon            VARCHAR(255) DEFAULT NULL,
  cover_image_url VARCHAR(500) DEFAULT NULL,
  content         LONGTEXT     DEFAULT NULL,
  is_template     TINYINT(1)   NOT NULL DEFAULT 0,
  is_archived     TINYINT(1)   NOT NULL DEFAULT 0,
  is_favorite     TINYINT(1)   NOT NULL DEFAULT 0,
  `order`         INT          NOT NULL DEFAULT 0,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_pages_workspace (workspace_id),
  KEY fk_pages_parent    (parent_id),
  KEY fk_pages_creator   (created_by),
  KEY idx_pages_archived (is_archived),
  KEY idx_pages_favorite (is_favorite),
  CONSTRAINT fk_pages_workspace
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
  CONSTRAINT fk_pages_parent
    FOREIGN KEY (parent_id)    REFERENCES pages (id)      ON DELETE SET NULL,
  CONSTRAINT fk_pages_creator
    FOREIGN KEY (created_by)   REFERENCES users (id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. databases  (database view attached to a page)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `databases` (
  id         CHAR(36)                                NOT NULL,
  page_id    CHAR(36)                                NOT NULL,
  name       VARCHAR(255)                            NOT NULL,
  view_type  ENUM('table','kanban','calendar','gallery') NOT NULL DEFAULT 'table',
  `schema`   JSON                                    DEFAULT NULL,
  created_at DATETIME                                NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME                                NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_db_page (page_id),
  CONSTRAINT fk_db_page
    FOREIGN KEY (page_id) REFERENCES pages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. database_rows
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS database_rows (
  id          CHAR(36) NOT NULL,
  database_id CHAR(36) NOT NULL,
  data        JSON     DEFAULT NULL,
  `order`     INT      NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_drow_database (database_id),
  CONSTRAINT fk_drow_database
    FOREIGN KEY (database_id) REFERENCES `databases` (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. templates
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS templates (
  id           CHAR(36)     NOT NULL,
  workspace_id CHAR(36)     NOT NULL,
  page_id      CHAR(36)     NOT NULL,
  name         VARCHAR(255) NOT NULL,
  description  TEXT         DEFAULT NULL,
  created_by   CHAR(36)     NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_tmpl_workspace (workspace_id),
  KEY fk_tmpl_page      (page_id),
  KEY fk_tmpl_creator   (created_by),
  CONSTRAINT fk_tmpl_workspace
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
  CONSTRAINT fk_tmpl_page
    FOREIGN KEY (page_id)      REFERENCES pages (id)      ON DELETE CASCADE,
  CONSTRAINT fk_tmpl_creator
    FOREIGN KEY (created_by)   REFERENCES users (id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. uploads
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS uploads (
  id            CHAR(36)      NOT NULL,
  uploader_id   CHAR(36)      NOT NULL,
  workspace_id  CHAR(36)      NOT NULL,
  original_name VARCHAR(500)  NOT NULL,
  stored_name   VARCHAR(500)  NOT NULL,
  file_url      VARCHAR(1000) NOT NULL,
  mime_type     VARCHAR(255)  NOT NULL,
  size_bytes    INT           NOT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_upload_uploader  (uploader_id),
  KEY fk_upload_workspace (workspace_id),
  CONSTRAINT fk_upload_uploader
    FOREIGN KEY (uploader_id)  REFERENCES users (id)      ON DELETE CASCADE,
  CONSTRAINT fk_upload_workspace
    FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. refresh_tokens
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS refresh_tokens (
  id         CHAR(36) NOT NULL,
  user_id    CHAR(36) NOT NULL,
  token      TEXT     NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY fk_rt_user (user_id),
  CONSTRAINT fk_rt_user
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Entity-Relationship Summary
-- ============================================================
--
--  users ──< workspaces          (owner_id)
--  users ──< workspace_members   (user_id)
--  workspaces ──< workspace_members
--  workspaces ──< pages          (workspace_id)
--  pages ──< pages               (parent_id — self ref, nested pages)
--  pages ──< databases           (page_id)
--  databases ──< database_rows   (database_id)
--  workspaces ──< templates      (workspace_id)
--  pages ──< templates           (page_id)
--  workspaces ──< uploads        (workspace_id)
--  users ──< refresh_tokens      (user_id)
--
-- ============================================================