소프트 삭제

DROP TABLE IF EXISTS member_delete_bad;
CREATE TABLE member_delete_bad (
  member_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT uq_member_delete_bad_email UNIQUE (email)
) ENGINE=InnoDB;

INSERT INTO member_delete_bad (email, name)
VALUES ('[email protected]', '첫 계정');
UPDATE member_delete_bad SET is_deleted = TRUE
WHERE email = '[email protected]';
INSERT INTO member_delete_bad (email, name)
VALUES ('[email protected]', '재가입 계정');

SELECT member_id, email, is_deleted FROM member_delete_bad;

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'member_delete_bad.uq_member_delete_bad_email'

member_id | email             | is_deleted
1         | [email protected] | 1

DROP PROCEDURE IF EXISTS soft_delete_member;
DROP VIEW IF EXISTS active_members_v;
DROP TABLE IF EXISTS member_delete_request;

ALTER TABLE members
  DROP INDEX uq_members_email,
  DROP CHECK ck_members_status,
  ADD COLUMN deleted_at DATETIME(6) NULL,
  ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
  ADD COLUMN delete_reason VARCHAR(240) NULL,
  ADD COLUMN active_email VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, LOWER(email), NULL)) STORED,
  ADD CONSTRAINT uq_members_active_email UNIQUE (active_email),
  ADD CONSTRAINT fk_members_deleted_by FOREIGN KEY (deleted_by)
    REFERENCES members (id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_members_delete_bundle CHECK (
    (deleted_at IS NULL AND deleted_by IS NULL AND delete_reason IS NULL)
    OR
    (deleted_at IS NOT NULL AND deleted_by IS NOT NULL
      AND delete_reason IS NOT NULL
      AND CHAR_LENGTH(TRIM(delete_reason)) >= 10)
  ),
  ADD INDEX ix_members_deleted_at (deleted_at, id);

ALTER TABLE members
  ADD CONSTRAINT ck_members_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED'));

CREATE TABLE member_delete_request (
  request_id BINARY(16) NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  deleted_at DATETIME(6) NOT NULL,
  PRIMARY KEY (request_id),
  CONSTRAINT fk_delete_request_member FOREIGN KEY (member_id)
    REFERENCES members (id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE VIEW active_members_v AS
SELECT id AS member_id, email, name, created_at
FROM members
WHERE deleted_at IS NULL;

DELIMITER //
CREATE PROCEDURE soft_delete_member (
  IN p_request BINARY(16),
  IN p_member BIGINT UNSIGNED,
  IN p_actor BIGINT UNSIGNED,
  IN p_reason VARCHAR(240),
  IN p_at DATETIME(6)
)
proc: BEGIN
  DECLARE v_existing BIGINT UNSIGNED;
  DECLARE v_deleted DATETIME(6);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT member_id INTO v_existing
  FROM member_delete_request
  WHERE request_id = p_request
  FOR UPDATE;
  IF v_existing IS NOT NULL THEN
    COMMIT;
    SELECT v_existing AS member_id, 'REPLAY' AS outcome;
    LEAVE proc;
  END IF;

  SELECT deleted_at INTO v_deleted
  FROM members
  WHERE id = p_member
  FOR UPDATE;
  IF v_deleted IS NOT NULL THEN
    INSERT INTO member_delete_request VALUES (p_request, p_member, v_deleted);
    COMMIT;
    SELECT p_member AS member_id, 'ALREADY_DELETED' AS outcome;
    LEAVE proc;
  END IF;

  IF CHAR_LENGTH(TRIM(p_reason)) < 10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'delete reason too short';
  END IF;
  UPDATE members
  SET deleted_at = p_at, deleted_by = p_actor, delete_reason = p_reason
  WHERE id = p_member;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'member not found';
  END IF;
  INSERT INTO member_delete_request VALUES (p_request, p_member, p_at);
  COMMIT;
  SELECT p_member AS member_id, 'DELETED' AS outcome;
END//
DELIMITER ;

SET @member_id = (
  SELECT id FROM members
  WHERE email = '[email protected]' AND deleted_at IS NULL
  ORDER BY id LIMIT 1
);
SET @delete_request = UUID_TO_BIN('77777777-7777-4777-8777-777777777777');
CALL soft_delete_member(@delete_request, @member_id, @member_id,
  '사용자가 직접 탈퇴를 확정한 요청', '2026-07-16 09:00:00');
CALL soft_delete_member(@delete_request, @member_id, @member_id,
  '재전송된 동일 탈퇴 요청', '2026-07-16 09:01:00');

SELECT
  (SELECT COUNT(*) FROM active_members_v WHERE member_id = @member_id) AS active_rows,
  (SELECT COUNT(*) FROM member_delete_request) AS request_rows,
  (SELECT COUNT(*) FROM posts WHERE author_id = @member_id) AS kept_posts;

member_id | outcome
1         | DELETED

member_id | outcome
1         | REPLAY

active_rows | request_rows | kept_posts
0           | 1            | 1

INSERT INTO members (email, password_hash, name, status, created_at)
VALUES ('[email protected]', '$2b$12$rejoined', '재가입 회원', 'ACTIVE',
        CURRENT_TIMESTAMP(6));
SET @rejoined_id = LAST_INSERT_ID();

INSERT INTO members (email, password_hash, name, status, created_at)
VALUES ('[email protected]', '$2b$12$duplicate', '중복 활성 계정', 'ACTIVE',
        CURRENT_TIMESTAMP(6));

UPDATE members SET deleted_at = '2026-07-16 10:00:00'
WHERE id = @rejoined_id;

SELECT id AS member_id FROM members
WHERE (deleted_at IS NULL) <> (deleted_by IS NULL)
   OR (deleted_at IS NULL) <> (delete_reason IS NULL);

SELECT active_email, COUNT(*) FROM members
WHERE active_email IS NOT NULL
GROUP BY active_email HAVING COUNT(*) > 1;

SELECT s.id AS post_id FROM posts AS s
LEFT JOIN members AS l ON l.id = s.author_id
WHERE l.id IS NULL;

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'members.uq_members_active_email'
ERROR 3819 (HY000): Check constraint 'chk_members_delete_bundle' is violated.