# SPDX-License-Identifier: Apache-2.0
# Copyright (C) 2021 OKTET Labs Ltd. All rights reserved.

#
# Usage: mysql diary <create.mysql
#

CREATE TABLE `director` (
    `nick`      varchar(30) NOT NULL
);
INSERT INTO director SET `nick`='director';

CREATE TABLE `project` (
    `id`        mediumint(9) unsigned NOT NULL auto_increment,
    `tag`       varchar(30) NOT NULL,
    `name`      varchar(70) NOT NULL,
    `prj_desc`  mediumtext NOT NULL,
    `prj_status`    enum('NEW','ACTIVE','TERMINATED') default 'ACTIVE' not NULL,
    `status_desc`   mediumtext,
    `issues`    mediumtext,
    `manager`   varchar(30) NOT NULL,
    `leader`    varchar(30),
    `customer`  varchar(30) NOT NULL,
    `cust_man`  varchar(30) NOT NULL,
    `cust_tech` varchar(30),
    `maillists` varchar(120),
    `svndir`    varchar(120),
    `storagedir`    varchar(120),
    `bugzilla_prod` varchar(64),
    `extra_rights`  varchar(255),
    `hide_hrs`  tinyint(1) default NULL,
    PRIMARY KEY (`id`),
    UNIQUE `name` (`name`),
    UNIQUE `tag` (`customer`, `tag`)
);

CREATE TABLE `cc_list` (
    `prj_id`    mediumint(9) unsigned NOT NULL default 0,
    `person`    varchar(30) NOT NULL
);

CREATE TABLE `diary` (
    `id`        mediumint(9) unsigned NOT NULL auto_increment,
    `who`       varchar(30) NOT NULL,
    `prj_id`    mediumint(9) unsigned NOT NULL default 0,
    `hours`     tinyint unsigned NOT NULL default 0,
    `ddate`     date NOT NULL default '0000-00-00',
    `descr`     mediumtext NOT NULL,
    `private`   tinyint(1) NOT NULL default 0,
    `bugid`     mediumint(9),
    `modified`  datetime,
    `created`   datetime,
    `state`     tinyint unsigned,
    PRIMARY KEY (`id`),
    KEY         `who` (`who`),
    KEY         `prj_id` (`prj_id`),
    KEY         `ddate` (`ddate`)
);

CREATE TABLE `sandbox` (
    `id`        int(11) default NULL,
    `modified`  datetime default NULL
);

CREATE TABLE `approval` (
    `who`       varchar(30) NOT NULL,
    `prj_id`    mediumint(9) unsigned NOT NULL,
    `approver`  varchar(30) NOT NULL,
    PRIMARY KEY (`who`, `prj_id`)
);

CREATE TABLE `approval_note` (
    `id`        mediumint(9) unsigned NOT NULL,
    `created`   datetime NOT NULL,
    `author`    varchar(30) NOT NULL,
    `note`      mediumtext
);