---

erDiagram
    PERSON ||--o| CITIZEN : "наследование"
    PERSON ||--o| EMPLOYEE : "наследование"

    ROLE ||--o{ USER : "назначается"
    USER o|--o| EMPLOYEE : "учётная запись"
    DEPARTMENT ||--o{ EMPLOYEE : "содержит"

    CITIZEN ||--o{ APPEAL : "подаёт"
    CATEGORY ||--o{ APPEAL : "определяет"
    STATUS ||--o{ APPEAL : "текущий статус"
    EMPLOYEE o|--o{ APPEAL : "обрабатывает"

    APPEAL ||--o{ RESPONSE : "имеет"
    EMPLOYEE ||--o{ RESPONSE : "подготавливает"

    APPEAL ||--o{ APPEAL_HISTORY : "имеет историю"
    STATUS ||--o{ APPEAL_HISTORY : "фиксируется"
    EMPLOYEE o|--o{ APPEAL_HISTORY : "изменяет"

    APPEAL ||--o{ ATTACHMENT : "содержит"

    PERSON {
        int PersonID PK
        string FullName
        string Phone
        string Email
        string Address
    }

    CITIZEN {
        int PersonID PK, FK
    }

    EMPLOYEE {
        int PersonID PK, FK
        string Position
        int DepartmentID FK
        int UserID FK
    }

    USER {
        int UserID PK
        string Login
        string PasswordHash
        int RoleID FK
    }

    ROLE {
        int RoleID PK
        string Name
        string Description
    }

    DEPARTMENT {
        int DepartmentID PK
        string Name
    }

    APPEAL {
        int AppealID PK
        string RegistrationNumber UK
        datetime CreatedAt
        string Content
        datetime Deadline
        int CitizenID FK
        int CategoryID FK
        int StatusID FK
        int EmployeeID FK
    }

    CATEGORY {
        int CategoryID PK
        string Name
        string Description
    }

    STATUS {
        int StatusID PK
        string Name
    }

    RESPONSE {
        int ResponseID PK
        int AppealID FK
        int EmployeeID FK
        string ResponseText
        datetime CreatedAt
    }

    APPEAL_HISTORY {
        int HistoryID PK
        int AppealID FK
        int StatusID FK
        int EmployeeID FK
        datetime ChangedAt
        string Comment
    }

    ATTACHMENT {
        int AttachmentID PK
        int AppealID FK
        string FileName
        string FilePath
    }