USE ganghonsa;

-- 기존 DB에는 members.points 컬럼이 없을 때만 아래 ALTER를 한 번 실행하세요.
-- ALTER TABLE members ADD COLUMN points INT NOT NULL DEFAULT 0 AFTER grade;

CREATE TABLE IF NOT EXISTS point_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action_type VARCHAR(50) NOT NULL UNIQUE,
    label VARCHAR(100) NOT NULL,
    points INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS point_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    target_type VARCHAR(50) NULL,
    target_id BIGINT UNSIGNED NULL,
    points INT NOT NULL,
    reason VARCHAR(255) NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_point_logs_member_created (member_id, created_at),
    INDEX idx_point_logs_target (target_type, target_id),
    CONSTRAINT fk_point_logs_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    CONSTRAINT fk_point_logs_created_by FOREIGN KEY (created_by) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO point_rules (action_type, label, points, is_active, created_at, updated_at) VALUES
('post_create', '게시글 작성', 100, 1, NOW(), NOW()),
('comment_create', '댓글 작성', 5, 1, NOW(), NOW()),
('reply_create', '답글 작성', 3, 1, NOW(), NOW()),
('post_like_received', '게시글 추천 받음', 10, 1, NOW(), NOW()),
('comment_like_received', '댓글 추천 받음', 2, 1, NOW(), NOW()),
('register', '회원가입', 50, 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE label = VALUES(label);