# Version SVN: $Id: 30_users_schema.yml 3749 2015-04-27 12:16:37Z sven.poeche $
propel:
  user:
    _attributes:                { phpName: User, allowPkInsert: true }
    uid:                        { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    hidden:                     { type: boolean, size:   1, required: true, default: false }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=validate, 1=verify, 2=registered, 3=denied, 4=deleted
    culture:                    { type: varchar, size:   7, required: true }
    username:                   { type: varchar, size:  40, required: true }
    password:                   { type: varchar, size: 128, required: true }
    new_password:               { type: varchar, size: 128, required: false }
    email:                      { type: varchar, size: 255, required: true }
    validation_code:            { type: varchar, size:  40, required: true }
    deletion_code:              { type: varchar, size:  40, required: false }
    avatar_filename:            { type: varchar, size: 255 }
    cover_filename:             { type: varchar, size: 255 }
    last_login_at:              { type: timestamp }
    last_access_at:             { type: timestamp }
    count_views:                { type: integer, size:  11, required: true, default: 0 } # sums up user_view table
    count_comments:             { type: integer, size:  11, required: true, default: 0 } # sums up user_comment table
    count_flags:                { type: integer, size:  11, required: true, default: 0 } # sums up user_flag table
    count_media_views:          { type: integer, size:  11, required: true, default: 0 } # sums up the views this user has seen
    count_video_views:          { type: integer, size:  11, required: true, default: 0 } # sums up the videos this user has seen
    count_photo_views:          { type: integer, size:  11, required: true, default: 0 } # sums up the photos this user has seen
    count_audio_views:          { type: integer, size:  11, required: true, default: 0 } # sums up the audios this user has seen
    count_document_views:       { type: integer, size:  11, required: true, default: 0 } # sums up the documents this user has seen
    count_own_media_views:      { type: integer, size:  11, required: true, default: 0 } # sums up how often all own medias have been viewed by others
    count_friends:              { type: integer, size:  11, required: true, default: 0 } # sums up all confirmed friends by using user_friend table
    count_favorites:            { type: integer, size:  11, required: true, default: 0 } # sums up user_favorite table
    count_blog_comments:        { type: integer, size:  11, required: true, default: 0 } # sums up all user blog comments by using blog_comment table
    count_own_blog_comments:    { type: integer, size:  11, required: true, default: 0 }
    count_forum_comments:       { type: integer, size:  11, required: true, default: 0 } # sums up all user forum comments by using forum_comment table
    count_own_forum_comments:   { type: integer, size:  11, required: true, default: 0 }
    count_groups:               { type: integer, size:  11, required: true, default: 0 }
    count_own_groups:           { type: integer, size:  11, required: true, default: 0 }
    count_own_media:            { type: integer, size:  11, required: true, default: 0 } # sums up all user media by using media table
    count_own_media_videos:     { type: integer, size:  11, required: true, default: 0 } # sums up all user videos by using media table
    count_own_media_photos:     { type: integer, size:  11, required: true, default: 0 } # sums up all user photos by using media table
    count_own_media_audios:     { type: integer, size:  11, required: true, default: 0 } # sums up all user audios by using media table
    count_own_media_documents:  { type: integer, size:  11, required: true, default: 0 } # sums up all user documents by using media table
    count_rating:               { type: integer, size:  11, required: true, default: 0 } # sums up media_rating table
    sum_rating:                 { type: float,   size:   8, scale: 2, required: true, default: 0.00 } # sums up media_rating table
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    _foreignKeys:
    _indexes:
      u_i_username:             [username]
      u_i_email:                [email]
      u_i_validation_code:      [validation_code]
      u_i_created_at:           [created_at]
      u_i_status:               [status]
      u_i_status_created:       [status, created_at]


  user_role:
    _attributes:            { phpName: UserRole, allowPkInsert: true }
    uid:                    { type: integer, size:  11, required: true, primaryKey: true }
    rid:                    { type: integer, size:  11, required: true, primaryKey: true }
    _foreignKeys:
      ur_fk_uid:            { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      ur_fk_rid:            { foreignTable: role, references: [ {local: rid, foreign: rid} ] }
    _indexes:
      ur_i_uid:             [uid]
      ur_i_rid:             [rid]


  user_friend:
    _attributes:            { phpName: UserFriend, allowPkInsert: true }
    fid:                    { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    uid:                    { type: integer, size:  11, required: true }
    friend_uid:             { type: integer, size:  11, required: true }
    status:                 { type: integer, size:  11, required: true } # 0=requested, 1=confirmed, 2=denied
    created_at:             { type: timestamp, required: true }
    updated_at:             { type: timestamp, required: true }
    _foreignKeys:
      uf_fk_uid:            { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      uf_fk_friend_uid:     { foreignTable: user, references: [ {local: friend_uid, foreign: uid} ] }
    _indexes:
      uf_i_uid:             [uid]
      uf_i_friend_uid:      [friend_uid]


  user_invite:
    _attributes:            { phpName: UserInvite, allowPkInsert: true }
    fid:                    { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    friend_uid:             { type: integer, size:  11, reguired: true }
    name:                   { type: varchar, size:  40, required: true }
    email:                  { type: varchar, size: 255, required: true }
    message_sid:            { type: integer, size:  11, required: true }
    validation_code:        { type: varchar, size:  40, required: true }
    status:                 { type: integer, size:  11, required: true } # 0=pending, 1=invited
    created_at:             { type: timestamp, required: true }
    updated_at:             { type: timestamp, required: true }
    _foreignKeys:
      ui_fk_friend_uid:     { foreignTable: user, references: [ {local: friend_uid, foreign: uid} ] }
      ui_fk_message_sid:    { foreignTable: statictext, references: [ {local: message_sid, foreign: sid} ] }
    _indexes:


  user_comment:
    _attributes:            { phpName: UserComment, allowPkInsert: true }
    ucid:                   { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    uid:                    { type: integer, size:  11, required: true }
    pid:                    { type: integer, size:  11, required: true, default: 0 }
    annotator_uid:          { type: integer, size:  11, required: true }
    ano_username:           { type: varchar, size: 255 }
    text:                   { type: longvarchar, required: true }
    status:                 { type: integer, size:  11, required: true, default: 0 } # 0=created, 1=verify, 2=illegal, 3=legal, 4=deleted
    created_at:             { type: timestamp, required: true }
    updated_at:             { type: timestamp, required: true }
    _foreignKeys:
      uc_fk_uid:            { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      uc_fk_annotator_uid:  { foreignTable: user, references: [ {local: annotator_uid, foreign: uid} ] }
    _indexes:
      uc_i_uid:             [uid]
      uc_i_pid:             [pid]
      uc_i_annotator_uid:   [annotator_uid]


  user_view:
    _attributes:            { phpName: UserView, allowPkInsert: true }
    uvid:                   { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    uid:                    { type: integer, size:  11, required: true}
    viewer_uid:             { type: integer, size:  11, required: true }
    sessid:                 { type: varchar, size: 128, required: true }
    hostname:               { type: varchar, size: 244, required: true }
    external:               { type: boolean, size:   1, required: true, default: false }
    external_url:           { type: varchar, size: 1024 }
    created_at:             { type: timestamp, required: true }
    _foreignKeys:
      uv_fk_uid:            { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      uv_fk_viewer_uid:     { foreignTable: user, references: [ {local: viewer_uid, foreign: uid} ] }
    _indexes:
      uv_i_vuid_sessid:     [viewer_uid, sessid]
      uv_i_uid:             [uid]
      uv_i_viewer_uid:      [viewer_uid]


  user_flag:
    _attributes:            { phpName: UserFlag, allowPkInsert: true }
    flagid:                 { type: integer, size: 11, required: true, primaryKey: true, autoIncrement: true }
    uid:                    { type: integer, size: 11, required: true }
    flagged_uid:            { type: integer, size: 11, required: true }
    reason:                 { type: longvarchar, required: true }
    status:                 { type: integer, size:  11, required: true, default: 0 } # 0=flagged
    created_at:             { type: timestamp, required: true }
    updated_at:             { type: timestamp, required: true }
    _foreignKeys:
       ufl_fk_uid:          { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
       ufl_fk_flagged_uid:  { foreignTable: user, references: [ {local: flagged_uid, foreign: uid} ] }
    _indexes:
      ufl_i_uid:            [uid]
      ufl_i_flagged_uid:    [flagged_uid]
      ufl_i_status:         [status]


