-- Phase D follow-up: employee document vault (storage-backed).

CREATE TABLE IF NOT EXISTS employee_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  doc_type VARCHAR(64) NOT NULL DEFAULT 'other',
  description_html MEDIUMTEXT NULL,
  storage_key VARCHAR(512) NOT NULL,
  original_name VARCHAR(512) NOT NULL,
  mime_type VARCHAR(128) NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  expires_at DATE NULL,
  uploaded_by_user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_emp_docs_emp (employee_id, deleted_at),
  CONSTRAINT fk_emp_docs_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_emp_docs_user FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_documents', 'HR Documents', 'HR', 31);

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT wr.id, 'hr_documents', pa.action_key, 1
FROM workspace_roles wr
CROSS JOIN permission_actions pa
WHERE wr.slug = 'super-admin'
  AND pa.action_key IN ('read', 'create', 'update', 'delete', 'export');