CREATE TABLE IF NOT EXISTS workspace_memberships (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  membership_role VARCHAR(30) NOT NULL DEFAULT 'member',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workspace_memberships_law_firm_user (law_firm_id, user_id),
  KEY idx_workspace_memberships_user (user_id),
  KEY idx_workspace_memberships_law_firm_role (law_firm_id, membership_role),
  KEY idx_workspace_memberships_created_by (created_by_user_id),
  CONSTRAINT fk_workspace_memberships_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workspace_memberships_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_workspace_memberships_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO workspace_memberships (
  id,
  law_firm_id,
  user_id,
  membership_role,
  is_default,
  created_by_user_id,
  joined_at
)
SELECT
  UUID(),
  u.law_firm_id,
  u.id,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM user_roles ur
      WHERE ur.user_id = u.id
        AND ur.role_code = 'admin'
    ) THEN 'owner'
    ELSE 'member'
  END,
  1,
  u.id,
  u.created_at
FROM users u
WHERE u.deleted_at IS NULL
  AND u.law_firm_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM workspace_memberships wm
    WHERE wm.law_firm_id = u.law_firm_id
      AND wm.user_id = u.id
  );
