<?xml version="1.0" encoding="UTF-8"?>
<database>

    <global>

        <table name="users">
            <field type="INT(11) NOT NULL">id</field>
            <field type="VARCHAR(50) NOT NULL">uuid</field>
            <field type="VARCHAR(50) NOT NULL DEFAULT ''">username</field>
            <field type="VARCHAR(255) NOT NULL DEFAULT ''">password</field>
            <field type="TEXT NULL">usergroup</field>
            <field type="VARCHAR(255) DEFAULT NULL">email</field>
            <field type="INT(1) NOT NULL DEFAULT '0'">active</field>
            <field type="INT(11) NOT NULL DEFAULT '0'">regdate</field>
            <field type="INT(11) NOT NULL DEFAULT '0'">lastvisit</field>
            <field type="TINYINT(1) NOT NULL DEFAULT 0">su</field>
            <field type="DATETIME NULL DEFAULT NULL">expire</field>
            <field type="DATETIME NULL DEFAULT NULL">lastedit</field>
            <field type="TEXT NULL">user_agent</field>
            <field type="VARCHAR(255) DEFAULT NULL">firstname</field>
            <field type="VARCHAR(255) DEFAULT NULL">lastname</field>
            <field type="VARCHAR(255) DEFAULT NULL">usertitle</field>
            <field type="INT(1) NOT NULL DEFAULT '0'">company</field>
            <field type="DATE NULL DEFAULT NULL">birthday</field>
            <field type="TEXT NULL">avatar</field>
            <field type="LONGTEXT NULL">extra</field>
            <field type="VARCHAR(2) DEFAULT NULL">lang</field>
            <field type="VARCHAR(50) NULL DEFAULT NULL">address</field>
            <field type="TINYTEXT NULL">secHash</field>
            <field type="TEXT NULL">toolbar</field>
            <field type="TEXT NULL">assigned_toolbar</field>
            <field type="VARCHAR(200) NULL">referal</field>
            <field type="VARCHAR(20) NULL">activation</field>
            <field type="VARCHAR(5) NULL">shortcuts</field>
            <field type="TEXT NULL">authenticator</field>
            <field type="DATETIME NULL DEFAULT NULL">lastLoginAttempt</field>
            <field type="SMALLINT UNSIGNED NOT NULL DEFAULT '0'">failedLogins</field>
            <field type="TEXT NULL">verifiableAttributes</field>

            <primary>id</primary>
            <index>username,password</index>
            <index>username</index>
            <index>uuid</index>
            <index>active</index>
            <index>activation</index>
            <index>regdate</index>
            <index>firstname</index>
            <index>lastname</index>
            <index>company</index>
            <index>birthday</index>
            <index>lang</index>
            <index>address</index>
            <auto_increment>id</auto_increment>

            <comment>This table contains the users of QUIQQER.</comment>
        </table>


        <table name="users_workspaces">
            <field type="INT(11) NOT NULL">id</field>
            <field type="varchar(50) NOT NULL">uid</field>
            <field type="TEXT NULL">title</field>
            <field type="LONGTEXT NULL">data</field>
            <field type="INT(11) DEFAULT 0">minHeight</field>
            <field type="INT(11) DEFAULT 0">minWidth</field>
            <field type="INT(1) DEFAULT 0">standard</field>

            <primary>id</primary>
            <auto_increment>id</auto_increment>
            <index>uid</index>

            <comment>This table contains the workspaces of the QUIQQER users.</comment>
        </table>

        <table name="users_webauthn_credentials">
            <field type="integer" autoincrement="true" primary="true">id</field>
            <field type="string" length="50">userUuid</field>
            <field type="string" length="128">userHandle</field>
            <field type="text">credentialId</field>
            <field type="string" length="64">credentialIdHash</field>
            <field type="text">publicKey</field>
            <field type="integer" nullable="true" default="NULL">signCount</field>
            <field type="string" length="64" nullable="true" default="NULL">aaguid</field>
            <field type="string" length="255" default="">name</field>
            <field type="json" nullable="true">transports</field>
            <field type="boolean" default="0">backupEligible</field>
            <field type="boolean" default="0">backedUp</field>
            <field type="integer" default="0">created</field>
            <field type="integer" nullable="true" default="NULL">lastUsed</field>

            <index>userUuid</index>
            <index>userHandle</index>
            <unique>credentialIdHash</unique>

            <comment>This table contains WebAuthn / passkey credentials of QUIQQER users.</comment>
        </table>

        <table name="groups">
            <field type="INT(11) NOT NULL DEFAULT 0">id</field>
            <field type="VARCHAR(50) NOT NULL">uuid</field>
            <field type="VARCHAR(50) NOT NULL DEFAULT ''">name</field>
            <field type="VARCHAR(50) NOT NULL DEFAULT 0">parent</field>
            <field type="TINYINT(1) NOT NULL DEFAULT '0'">active</field>
            <field type="VARCHAR(128) DEFAULT ''">toolbar</field>
            <field type="TEXT NULL">assigned_toolbar</field>
            <field type="TEXT NULL">avatar</field>
            <field type="TEXT NULL">extra</field>
            <field type="TEXT NULL">rights</field>

            <primary>id</primary>
            <index>parent</index>
            <index>uuid</index>
            <index>active</index>
            <index>name</index>
            <index>toolbar</index>

            <comment>This table contains the groups of QUIQQER.</comment>
        </table>

        <table name="permissions">
            <field type="VARCHAR(100) NOT NULL DEFAULT ''">name</field>
            <field type="VARCHAR(20) NOT NULL DEFAULT ''">type</field>
            <field type="VARCHAR(20) NOT NULL DEFAULT ''">area</field>
            <field type="VARCHAR(255) NULL">title</field>
            <field type="TEXT NULL">desc</field>
            <field type="VARCHAR(200) NULL">src</field>
            <field type="TEXT NULL">defaultvalue</field>

            <primary>name</primary>
            <index>name</index>
            <index>type</index>
            <index>area</index>
        </table>

        <table name="permissions2users">
            <field type="VARCHAR(50) NOT NULL DEFAULT 0">user_id</field>
            <field type="MEDIUMTEXT NULL">permissions</field>

            <primary>user_id</primary>
            <index>user_id</index>
        </table>

        <table name="permissions2groups">
            <field type="VARCHAR(50) NOT NULL DEFAULT 0">group_id</field>
            <field type="MEDIUMTEXT NULL">permissions</field>

            <primary>group_id</primary>
            <index>group_id</index>
        </table>

        <table name="permissions2sites">
            <field type="VARCHAR(200) NOT NULL DEFAULT ''">project</field>
            <field type="VARCHAR(2) NOT NULL DEFAULT ''">lang</field>
            <field type="INT(20) NOT NULL">id</field>
            <field type="VARCHAR(100) NOT NULL">permission</field>
            <field type="TEXT NULL">value</field>

            <primary>project,lang,id,permission</primary>
        </table>

        <table name="permissions2projects">
            <field type="VARCHAR(200) NOT NULL DEFAULT ''">project</field>
            <field type="VARCHAR(2) NOT NULL DEFAULT ''">lang</field>
            <field type="VARCHAR(100) NOT NULL">permission</field>
            <field type="TEXT NULL">value</field>

            <primary>project,lang,permission</primary>
            <index>project</index>
            <index>lang</index>
            <index>permission</index>
        </table>

        <table name="permissions2media">
            <field type="VARCHAR(200) NOT NULL DEFAULT ''">project</field>
            <field type="INT(20) NOT NULL">id</field>
            <field type="VARCHAR(100) NOT NULL">permission</field>
            <field type="TEXT NULL">value</field>

            <primary>project,id,permission</primary>
            <index>project</index>
            <index>id</index>
            <index>permission</index>
        </table>

        <table name="sessions">
            <field type="VARCHAR(255) NOT NULL DEFAULT ''">session_id</field>
            <field type="TEXT NULL ">session_value</field>
            <field type="INT(11) NOT NULL DEFAULT 0">session_time</field>
            <field type="INT(11) NOT NULL DEFAULT 0">session_lifetime</field>

            <primary>session_id</primary>
            <index>session_id</index>
            <index>session_time</index>
            <index>session_lifetime</index>
            <comment>
                This table contains the user session. This table is only used when sessions run over the database.
                Default is the file system.
            </comment>
        </table>

        <table name="updateChecks">
            <field type="INT(11) NOT NULL">id</field>
            <field type="INT(11) NULL">date</field>
            <field type="TEXT NULL">result</field>
            <field type="TEXT NULL">error</field>

            <primary>id</primary>
            <auto_increment>id</auto_increment>
            <index>date</index>
        </table>

        <!-- Addresses -->
        <table name="users_address">
            <field type="INT(11) NOT NULL">id</field>
            <field type="VARCHAR(50) NOT NULL">uuid</field>
            <field type="VARCHAR(50) NOT NULL">uid</field>
            <field type="VARCHAR(50) NOT NULL">userUuid</field>
            <field type="VARCHAR(255) DEFAULT ''">salutation</field>
            <field type="VARCHAR(255) DEFAULT ''">title</field>
            <field type="VARCHAR(255) DEFAULT ''">firstname</field>
            <field type="VARCHAR(255) DEFAULT ''">lastname</field>
            <field type="TEXT NULL">phone</field>
            <field type="TEXT NULL">mail</field>
            <field type="VARCHAR(100) DEFAULT ''">company</field>
            <field type="TEXT NULL">delivery</field>
            <field type="TEXT NULL">street_no</field>
            <field type="TEXT NULL">zip</field>
            <field type="TEXT NULL">city</field>
            <field type="TEXT NULL">country</field>
            <field type="TEXT NULL">custom_data</field>
            <field type="DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP">c_date</field>
            <field type="DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP">e_date</field>

            <primary>id</primary>
            <auto_increment>id</auto_increment>
            <index>id</index>
            <index>uid</index>
            <index>uuid</index>
            <index>userUuid</index>
        </table>

    </global>

    <projects>
        <table name="paths" no-auto-update="1">
            <field type="TEXT NULL">path</field>
        </table>
    </projects>

</database>
