학생 민원/고충 관리 시스템
테이블 (10)
admin_replies
- id — BIGINT
- complaint_id — BIGINT — NOT NULL
- admin_id — BIGINT — NOT NULL
- content — TEXT — NOT NULL
- created_at — DATETIME — NOT NULL
attachments
- id — BIGINT
- complaint_id — BIGINT — NOT NULL
- file_name — VARCHAR(255) — NOT NULL
- file_path — VARCHAR(500) — NOT NULL
- created_at — DATETIME — NOT NULL
categories
- id — INT
- code — VARCHAR(30) — NOT NULL
- name — VARCHAR(50) — NOT NULL
complaints
- id — BIGINT
- submitter_id — BIGINT
- title — VARCHAR(100) — NOT NULL
- content — TEXT — NOT NULL
- category_id — INT — NOT NULL
- target_scope — ENUM('PERSONAL','ALL') — NOT NULL
- is_anonymous — TINYINT(1) — NOT NULL
- status — ENUM('REGISTERED','IN_PROGRESS','RESOLVED','DELETED') — NOT NULL
- votes_up — INT — NOT NULL
- votes_down — INT — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- INDEX — idx_category_status
- INDEX — idx_created
emergency_chat_messages
- id — BIGINT
- session_id — BIGINT — NOT NULL
- sender_id — BIGINT — NOT NULL
- sender_role — ENUM('STUDENT','ADMIN') — NOT NULL
- message — TEXT — NOT NULL
- created_at — DATETIME — NOT NULL
emergency_chat_sessions
- id — BIGINT
- student_id — BIGINT — NOT NULL
- admin_id — BIGINT
- related_complaint_id — BIGINT
- status — ENUM('OPEN','CLOSED') — NOT NULL
- created_at — DATETIME — NOT NULL
lost_item_details
- complaint_id — BIGINT
- place — VARCHAR(100)
- lost_at — DATETIME
- found_status — ENUM('NOT_FOUND','FOUND') — NOT NULL
users
- id — BIGINT
- login_id — VARCHAR(50) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- name — VARCHAR(50) — NOT NULL
- role — ENUM('STUDENT','ADMIN','OP') — NOT NULL
- class_name — VARCHAR(50)
- created_at — DATETIME — NOT NULL
votes
- id — BIGINT
- complaint_id — BIGINT — NOT NULL
- voter_id — BIGINT — NOT NULL
- value — TINYINT — NOT NULL
- created_at — DATETIME — NOT NULL