DIR : /home/kozerus/public_html/go/dgs0/specs/db/table-Bulletins.txt

/home/kozerus/public_html/go/dgs0/specs/db

###
### Description of mysql tables:  Bulletin
###

Here you'll find some infos about all the bulletin-board tables.

Legend:
   - B  = Bulletin
   - TD = Tournament Director
   - TP = Tournament Participant (registered player)
   - T  = Tournament, e.g. T-owner
   - UL = user-list
   - tid = tournament-id

Important Notes:
   - Life-Cycle of Bulletin (author = admin):
      - author = admin: NEW -> SHOW -> ARCHIVE (on expire-date or by admin)
      - author = non-admin:
         - (a)  NEW -> PENDING : ADM-ACK -> SHOW -> ARCHIVE (on expire-date or by admin)
         - (b1) NEW -> PENDING : ADM-NACK -> HIDDEN (back-to-author with AdminNote) -> PENDING
         - (b2) NEW -> PENDING : ADM-NACK -> DELETE

Permission-Matrix for writing bulletins:
   Who (user-type) is allowed to write what (Category) bulletins addressed
   to whom (TargetType) ?

   - NOTES:
     - normal user can NOT create bulletin (except PRIV_MSG for MP-games),
       but Admin must create and assign it to the author setting USER_EDIT-flag.
       Then user can update it!

   - Category = MAINT : server maintenance announcements
     - create allowed by Admin; Target: ALL, UL, MPG

   - Category = ADM_MSG : admin announcements, game-/user-admin-stuff, etc
     - create allowed by Admin; Target: ALL, UL, MPG

   - Category = TOURNEY : tournament announcement on DGS, for non-DGS tournaments use tournament-forum
     - create allowed by Admin; Target: ALL, UL, (MPG)
     - update allowed by user;  Target: ALL, UL

   - Category = TNEWS : tournament-specific news / announcements
     - create allowed by Admin, T-Admin, TD, T-owner; Target: TD (private T-news), TP (public T-news)

   - Category = FEATURE : feature-info
     - create allowed by Admin; Target: ALL, UL, TD, TP, MPG

   - Category = PRIV_MSG : user-to-users messages (non-Ads)
     - create allowed by Admin; Target: ALL, UL, TD, TP, MPG
     - create allowed by GP;    Target: MPG
     - update allowed by user;  Target: UL

   - Category = AD : user-to-users advertisements (=pay me money for something Go-related)
     - create allowed by Admin; Target: ALL, UL, (MPG)
     - update allowed by user;  Target: ALL, UL


###########################################################################
## --- Bulletin ---

Description:
   Table to store bulletins, mainly used for maintenance-announcements to all users,
   that shouldn't be handled with the private-message system and is more "prominently"
   than the forums.

| Field       | Type                                                                | Null | Key | Default             | Extra          |
+-------------+---------------------------------------------------------------------+------+-----+---------------------+----------------+
| ID          | int(11)                                                             | NO   | PRI | NULL                | auto_increment |
| uid         | int(11)                                                             | NO   |     | NULL                |                |
| LockVersion | tinyint(3) unsigned                                                 | NO   |     | 0                   |                |
| Category    | enum('MAINT','ADM_MSG','TOURNEY','TNEWS','FEATURE','PRIV_MSG','AD') | NO   |     | PRIV_MSG            |                |
| Status      | enum('NEW','PENDING','REJECTED','SHOW','ARCHIVE','DELETE')          | NO   | MUL | NEW                 |                |
| TargetType  | enum('ALL','TD','TP','UL','MPG')                                    | NO   |     | NULL                |                |
| Flags       | tinyint(3) unsigned                                                 | NO   |     | 0                   |                |
| PublishTime | datetime                                                            | NO   |     | 0000-00-00 00:00:00 |                |
| ExpireTime  | datetime                                                            | NO   |     | 0000-00-00 00:00:00 |                |
| tid         | int(11)                                                             | NO   |     | 0                   |                |
| gid         | int(11)                                                             | NO   |     | 0                   |                |
| CountReads  | mediumint(8) unsigned                                               | NO   |     | 0                   |                |
| AdminNote   | varchar(255)                                                        | NO   |     |                     |                |
| Subject     | varchar(255)                                                        | NO   |     | NULL                |                |
| Text        | text                                                                | NO   |     | NULL                |                |
| Lastchanged | datetime                                                            | NO   |     | 0000-00-00 00:00:00 |                |


## Fields:

* uid : author of bulletin, FK to Players.ID

* LockVersion : version-field for optimistic locking

* Category : bulletin-type, can be filtered upon by (future) user-profile
   - MAINT = maintenance announcement
   - ADM_MSG = message by admin
   - TOURNEY = announcements for new tournaments (no tid yet)
   - TNEWS = announcement for specific tournament, e.g. tournament-news
   - FEATURE = announcement/info about feature, e.g. "did you know this ..."
   - PRIV_MSG = private announcement
   - AD = advertisement
   - NOTE: also see permission-matrix above table-fields

* Status :
   - NEW = new bulletin-entry
   - PENDING = pending bulletin, needs ACK or NACK by admin to publish
   - REJECTED = hidden bulletin, user-change rejected by admin,
     only visible to admin and author on bulletin-editor,
     should be accompanied by admin-note about reason for rejection
   - SHOW = show bulletin publically to targeted group
   - ARCHIVE = archived bulletin, only showed on archive-page
   - DELETE = bulletin, that should be deleted

* TargetType :
   - ALL = bulletin shown to ALL DGS-users (Ref_ID := 0)
   - TD = bulletin shown to tournament-director of referenced tournament (Ref_ID := tid)
   - TP = bulletin shown to tournament-participants of referenced tournament (Ref_ID := tid)
   - UL = bulletin shown to User-List (Ref_ID := 0)
   - NOTE: no default defined in DB, because it MUST be set by application

* Flags : admin-flags of bulletin
   - 0x01 = created by admin
   - 0x02 = changeable by user

* PublishTime : defines order of bulletins

* ExpireTime : cron sets bulletin-entry to ARCHIVED-status if ExpireTime has past

* tid : FK to Tournament.ID
   - 0 = no reference to tournament (default)
   - >0 = tournament-id for Bulletin.TargetType=TP|TD + Bulletin.Category=TNEWS

* gid : FK to Games.ID
   - 0 = no reference to game (default)
   - >0 = game-id for Bulletin.TargetType=MPG

* CountReads : counter, increased by each user marking bulletin as read

* AdminNote : optional note by admin to bulletin-author on rejected bulletin
   - gives author chance to correct bulletin

* Subject : subject/title of bulletin

* Text : main-text of bulletin (optional)

* Lastchanged : date of last-update of bulletin



###########################################################################
## --- BulletinRead ---

Description:
   Table to store if user has read bulletin.

| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| bid   | int(11) | NO   | PRI | NULL    |       |
| uid   | int(11) | NO   | PRI | NULL    |       |


## Fields:

* bid : bulletin-id, FK to Bulletin.ID

* uid : user having marked bulletin as read, FK to Players.ID



###########################################################################
## --- BulletinTarget ---

Description:
   Table to store recipients for bulletin of TargetType=UL (user-list).

| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| bid   | int(11) | NO   | PRI | NULL    |       |
| uid   | int(11) | NO   | PRI | NULL    |       |


## Fields:

* bid : bulletin-id, FK to Bulletin.ID

* uid : recipient of bulletin of TargetType=UL (user-list), FK to Players.ID