🎓 OPUS (SW프로젝트관리시스템)

erDiagram
    %% ── Contest 모듈 ──
    contest_category ||--o{ contest : "category_id"
    contest ||--o{ contest_award : "contest_id"
    contest ||--|| contest_template : "contest_id (UK)"
    contest ||--|| contest_sort : "contest_id (UK)"
    contest ||--o{ contest_track : "contest_id"
    contest ||--o{ contest_submission_item : "contest_id"
    contest ||--o{ contest_member : "contest_id"
    contest ||--o{ notice : "contest_id"
    contest_track ||--o{ contest_submission_item : "contest_track_id"
    contest_submission_item ||--o{ contest_submission_item_file_formats : "element collection"
    contest_submission_item ||--o{ contest_submission : "contest_submission_item_id"
    contest_submission ||--o| contest_submission_memo : "contest_submission_id (UK)"
    contest_submission ||--o{ contest_submission_feedback : "contest_submission_id"
    contest_member ||--o{ contest_member_team_ids : "element collection"

    %% ── Team 모듈 ──
    contest ||--o{ team : "contest_id"
    contest_track ||--o{ team : "track_id"
    team ||--o{ team_member : "team_id"
    team ||--o{ team_comment : "team_id"
    team ||--o{ team_like : "team_id"
    team ||--o{ team_vote : "team_id"
    team ||--o{ team_contest_award : "team_id"
    team ||--o{ contest_submission : "team_id"
    team ||--o{ contest_member_team_ids : "team_id"
    team_member ||--o{ team_member_roles : "element collection"
    contest_award ||--o{ team_contest_award : "contest_award_id"

    %% ── Member 모듈 ──
    member ||--o{ member_roles : "element collection"
    member ||--o{ contest_member : "member_id"
    member ||--o{ contest_submission_feedback : "member_id"
    member ||--o{ team_member : "member_id"
    member ||--o{ team_comment : "member_id"
    member ||--o{ team_like : "member_id"
    member ||--o{ team_vote : "member_id"
    member ||--o{ notification : "member_id"
    member ||--o{ member_withdrawal_history : "member_id"

    %% ── File 모듈 ──
    file ||--|| file_image : "file_id (UK)"
    file ||--|| file_feedback : "file_id (UK)"
    file ||--|| file_document : "file_id (UK)"
    contest_submission ||--o{ file_document : "submission_id"
    contest_submission_feedback ||--o{ file_feedback : "feedback_id"

    contest_category {
        bigint id PK
        varchar category_name
        bit is_deleted
    }
    contest {
        bigint id PK
        bigint category_id FK
        varchar contest_name
        bit is_current
        int max_votes_limit
        datetime vote_start_at
        datetime vote_end_at
    }
    contest_award {
        bigint id PK
        bigint contest_id FK
        varchar award_name
        varchar award_color
    }
    contest_template {
        bigint id PK
        bigint contest_id FK "UK"
        bit track_required
        bit team_name_required
        bit overview_required
    }
    contest_sort {
        bigint id PK
        bigint contest_id FK "UK"
        enum mode "ASC, CUSTOM, RANDOM"
    }
    contest_track {
        bigint id PK
        bigint contest_id FK
        varchar track_name
    }
    contest_submission_item {
        bigint id PK
        bigint contest_id FK
        bigint contest_track_id FK "nullable"
        varchar name
        datetime start_at
        datetime end_at
        bit allow_late_submission
        int max_file_count
        int max_file_size_mb
        enum visibility "TEAM, STAFF, MEMBER, PUBLIC"
    }
    contest_submission_item_file_formats {
        bigint contest_submission_item_id PK
        enum file_format PK
    }
    contest_submission {
        bigint id PK
        bigint contest_submission_item_id FK
        bigint team_id FK
        datetime first_submitted_at
    }
    contest_submission_memo {
        bigint id PK
        bigint contest_submission_id FK "UK (active)"
        varchar content
    }
    contest_submission_feedback {
        bigint id PK
        bigint contest_submission_id FK
        bigint member_id FK
        varchar description
        tinyint is_read
    }
    contest_member {
        bigint id PK
        bigint contest_id FK
        bigint member_id FK
    }
    contest_member_team_ids {
        bigint contest_member_id PK
        bigint team_id PK
    }
    notice {
        bigint id PK
        bigint contest_id FK "nullable"
        varchar title
        varchar description
    }

    team {
        bigint id PK
        bigint contest_id FK
        bigint track_id FK "nullable"
        varchar team_name
        varchar project_name
        varchar professor_name
        varchar overview
        bit is_submitted
        int item_order
    }
    team_member {
        bigint id PK
        bigint team_id FK
        bigint member_id FK
    }
    team_member_roles {
        bigint team_member_id PK
        enum role PK "ROLE_팀원, ROLE_팀장"
    }
    team_comment {
        bigint id PK
        bigint team_id FK
        bigint member_id FK
        varchar description
    }
    team_like {
        bigint id PK
        bigint team_id FK
        bigint member_id FK
    }
    team_vote {
        bigint id PK
        bigint team_id FK
        bigint member_id FK
    }
    team_contest_award {
        bigint id PK
        bigint team_id FK
        bigint contest_award_id FK
    }

    member {
        bigint id PK
        varchar email "UK"
        varchar student_id "UK, nullable"
        varchar name
        varchar password "nullable"
        varchar social_type "nullable"
        varchar social_id "nullable"
        varchar github_url
        bit is_profile_public
        bit is_fake
    }
    member_roles {
        bigint member_id PK
        enum role PK "ROLE_학생, ROLE_관리자, ROLE_교수, ROLE_직원, ROLE_외부멘토"
    }
    staff_info {
        bigint id PK
        varchar name
        varchar email
        enum role "ROLE_교수, ROLE_직원"
    }
    notification {
        bigint id PK
        bigint member_id FK
        varchar title
        varchar content
        enum type "TEAM, TEAM_COMMENT, TEAM_AWARDS"
        bigint target_id
        bit is_read
    }
    member_withdrawal_history {
        bigint id PK
        bigint member_id FK
        enum reason "FRAUDULENT_USE, DUPLICATE_ACCOUNT, ETC"
        varchar detail
    }

    file {
        bigint id PK
        varchar name
        varchar file_path
        bigint file_size
        varchar mime_type
    }
    file_image {
        bigint id PK
        bigint file_id FK "UK"
        enum image_type "BANNER, PREVIEW, THUMBNAIL, POSTER, PROFILE"
        bigint reference_id
        enum reference_type "CONTEST, TEAM, TRACK, MEMBER"
        bit is_webp_converted
    }
    file_document {
        bigint id PK
        bigint file_id FK "UK"
        bigint submission_id FK
        int file_order
    }
    file_feedback {
        bigint id PK
        bigint file_id FK "UK"
        bigint feedback_id FK
        int file_order
    }