-- Level-2 migrations (run once on existing DB)
-- MySQL 8+ recommended.

-- 1) projects: planner_email (optional)
ALTER TABLE projects ADD COLUMN planner_email VARCHAR(255) NULL;

-- 2) ACL table
CREATE TABLE IF NOT EXISTS project_access (
  project_id INT NOT NULL,
  user_id INT NOT NULL,
  access_role VARCHAR(20) NOT NULL DEFAULT 'owner',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (project_id, user_id),
  INDEX idx_pa_user (user_id),
  INDEX idx_pa_project (project_id),
  CONSTRAINT fk_pa_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional: backfill access for existing projects
INSERT IGNORE INTO project_access (project_id, user_id, access_role)
SELECT p.id, p.owner_user_id, 'owner'
FROM projects p
WHERE p.owner_user_id IS NOT NULL;

-- 3) measurements: idempotency + soft delete + status
ALTER TABLE measurements
  ADD COLUMN client_uuid CHAR(36) NULL,
  ADD COLUMN status VARCHAR(16) NOT NULL DEFAULT 'final',
  ADD COLUMN deleted_at DATETIME NULL;

CREATE UNIQUE INDEX uq_meas_client_uuid ON measurements(client_uuid);

-- 4) measurement_files (new table; replaces measurement_photos)
CREATE TABLE IF NOT EXISTS measurement_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  measurement_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_mf_meas (measurement_id),
  CONSTRAINT fk_mf_meas FOREIGN KEY (measurement_id) REFERENCES measurements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- If you previously used measurement_photos, optionally migrate rows:
INSERT INTO measurement_files (measurement_id, file_name, original_name, mime, created_at)
SELECT measurement_id, file_name, original_name, NULL, created_at
FROM measurement_photos
WHERE measurement_id NOT IN (SELECT measurement_id FROM measurement_files);

