-- DBN DMS migration 001 — document versioning
-- Adds client_document_versions table + current_version/storage_path columns on client_documents.
-- Safe to re-run: uses IF NOT EXISTS / INFORMATION_SCHEMA guards.

CREATE TABLE IF NOT EXISTS client_document_versions (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id       INT UNSIGNED NOT NULL,
  client_id         INT UNSIGNED NOT NULL,
  version_number    INT UNSIGNED NOT NULL,
  title             VARCHAR(500) NOT NULL,
  content           LONGTEXT NOT NULL,
  file_size_bytes   INT UNSIGNED DEFAULT 0,
  original_filename VARCHAR(255) NULL,
  storage_path      VARCHAR(500) NULL,
  word_count        INT UNSIGNED DEFAULT 0,
  uploaded_by       INT UNSIGNED NULL,
  notes             VARCHAR(500) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_doc_ver (document_id, version_number),
  KEY idx_client (client_id),
  KEY idx_uploaded_by (uploaded_by),
  CONSTRAINT fk_cdv_doc    FOREIGN KEY (document_id) REFERENCES client_documents(id) ON DELETE CASCADE,
  CONSTRAINT fk_cdv_client FOREIGN KEY (client_id)   REFERENCES clients(id)          ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Per-document version history. Latest = client_documents.current_version.';

-- current_version column (guarded against re-run)
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'client_documents'
    AND COLUMN_NAME = 'current_version'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE client_documents ADD COLUMN current_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER chunk_count',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- storage_path column (guarded)
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'client_documents'
    AND COLUMN_NAME = 'storage_path'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE client_documents ADD COLUMN storage_path VARCHAR(500) NULL AFTER original_filename',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;