/* table definitions for SQL database */

/* everry request for email is stored here */
DROP TABLE IF EXISTS accesstokens;
CREATE TABLE accesstokens (
    id      INTEGER NOT NULL AUTO_INCREMENT UNIQUE,             /* id of the database entry */
    cdate	TIMESTAMP DEFAULT CURRENT_TIMESTAMP,                /* timestap of the entry creation */
    token 	CHAR(36) NOT NULL UNIQUE,	                        /* access token */

    email	VARCHAR(256),                                       /* email of the user */
    scode	INTEGER NOT NULL /*UNIQUE*/,	                        /* this is a short code for file retrieval (not yet implemented) */
    
    rip		VARCHAR(16),                                        /* remote ip*/
    rport	VARCHAR(6),                                         /* remote port */
    ragent	VARCHAR(256),                                       /* remote user agent */
    raddr	VARCHAR(16),					    /* server address */
    rtime	VARCHAR(20),				            /* request time float */

    expiry      TIMESTAMP,                                      /* expiry date of the token */
    deletion    TIMESTAMP,                                      /* deletion of the token for housekeeping purposes */

    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* each upload has an entry */
DROP TABLE IF EXISTS uploads;
CREATE TABLE uploads (
    id          INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
    cdate       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,        /* upload time */
    token 	CHAR(36) NOT NULL UNIQUE,	                        /* access token */

    name	VARCHAR(256),                                   /* name on request */
    email	VARCHAR(256),                                   /* user email */
    tel		VARCHAR(20),                                    /* telephone number of request */
    msg		VARCHAR(512),                                           /* message from sender */

    rip		VARCHAR(16),                                    /* remote ip*/
    rport	VARCHAR(6),                                     /* remote port */
    ragent	VARCHAR(256),                                   /* remote user agent */
    raddr	VARCHAR(16),					    /* server address */
    rtime	VARCHAR(20),				            /* request time float */

    expiry	TIMESTAMP,                                      /* expiration date of download */
    dnld	INTEGER DEFAULT 0,                              /* count of downloads */
    
    filename	VARCHAR(256),                               /* file name*/
    filesize    INTEGER UNSIGNED,                           /* file size */
    
    
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS downloads;
CREATE TABLE downloads (
    id      INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
    cdate   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uplid   INTEGER NOT NULL,				/* link to upload id entry */

    rip		VARCHAR(16),                             /* remote ip*/
    rport	VARCHAR(6),                              /* remote port */
    ragent	VARCHAR(256),                            /* remote user agent */
    raddr	VARCHAR(16),					    /* server address */
    rtime	VARCHAR(20),				            /* request time float */

        PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

