-- Migration for EXISTING installs upgrading to live collaborative editing.
-- Skip this if you're doing a fresh install from schema.sql (it already
-- includes these tables). Run this once via phpMyAdmin → SQL tab, or:
--   mysql -u user -p dbname < sql/migration_002_live_collab.sql

CREATE TABLE IF NOT EXISTS live_documents (
    document_id INT PRIMARY KEY,
    kind ENUM('sheet','doc') NOT NULL,
    base_version_id INT NOT NULL,
    state LONGTEXT NOT NULL,
    base_snapshot LONGTEXT NOT NULL,
    state_seq INT NOT NULL DEFAULT 0,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_checkpoint_at DATETIME NULL,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (base_version_id) REFERENCES document_versions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS live_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    user_id INT NOT NULL,
    participant_token VARCHAR(64) NOT NULL UNIQUE,
    color VARCHAR(7) NOT NULL,
    cursor LONGTEXT NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_doc (document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS live_ops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    participant_id INT NOT NULL,
    user_id INT NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    op_type VARCHAR(30) NOT NULL,
    payload LONGTEXT NOT NULL,
    client_op_id VARCHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    INDEX idx_doc_seq (document_id, id),
    UNIQUE KEY uniq_doc_clientop (document_id, client_op_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE editing_sessions ADD COLUMN contributors LONGTEXT NULL COMMENT 'JSON list of {user_id,name} who contributed edits during this session';