-- Deeva ScriptDesk — MySQL 8.0 / InnoDB / utf8mb4
CREATE DATABASE IF NOT EXISTS deeva_scriptdesk_db
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE deeva_scriptdesk_db;

CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  license_key VARCHAR(64) NULL,
  license_status ENUM('trial','active','expired','revoked') NOT NULL DEFAULT 'trial',
  license_expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE licenses (
  id CHAR(36) PRIMARY KEY,
  license_key VARCHAR(64) NOT NULL UNIQUE,
  user_id CHAR(36) NULL,
  seats_allowed INT NOT NULL DEFAULT 1,
  plan ENUM('trial','pro','studio') NOT NULL DEFAULT 'pro',
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_license_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sessions (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE projects (
  id CHAR(36) PRIMARY KEY,
  owner_id CHAR(36) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT 'Untitled Screenplay',
  logline TEXT NULL,
  title_page JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_project_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Each "node" is one screenplay element (scene_heading, action, character,
-- dialogue, parenthetical, transition). `sort_key` is a fractional-index
-- string (e.g. "a0", "a0V") so nodes can be reordered without renumbering siblings.
CREATE TABLE nodes (
  id CHAR(36) PRIMARY KEY,
  project_id CHAR(36) NOT NULL,
  node_type ENUM('scene_heading','action','character','dialogue','parenthetical','transition','shot','note') NOT NULL,
  content MEDIUMTEXT NOT NULL DEFAULT '',
  sort_key VARCHAR(64) NOT NULL,
  scene_number VARCHAR(16) NULL,
  metadata JSON NULL,
  client_timestamp BIGINT UNSIGNED NOT NULL COMMENT 'ms epoch, set by client, drives conflict resolution',
  server_updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  deleted_at DATETIME NULL,
  CONSTRAINT fk_node_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_project_sort (project_id, sort_key),
  INDEX idx_project_updated (project_id, server_updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sync_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id CHAR(36) NOT NULL,
  node_id CHAR(36) NOT NULL,
  action ENUM('upsert','delete','conflict_resolved') NOT NULL,
  client_timestamp BIGINT UNSIGNED NOT NULL,
  server_timestamp DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX idx_sync_project (project_id, server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;