-- ------------------------------------------------------------------------------------------------------------------------
-- REGISTRATION

create table user (
    email VARCHAR(50) PRIMARY KEY ,
    first_name VARCHAR(20) NOT NULL ,
    last_name VARCHAR(20) NOT NULL ,
    birth_date DATE NOT NULL ,
    password VARCHAR(20) NOT NULL ,
    registered_date DATETIME DEFAULT current_timestamp
);

CREATE TABLE user_role (
    user VARCHAR(50) NOT NULL ,
    role VARCHAR(50) NOT NULL ,

    PRIMARY KEY (user, role) ,
    CONSTRAINT FOREIGN KEY (user) REFERENCES user(email) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------------------------------------------------------------------------------
-- PROJECTS

create table project (
    project VARCHAR(50) PRIMARY KEY ,
    created_by VARCHAR(50) NOT NULL ,
    created_date DATETIME DEFAULT current_timestamp ,
    finished_date DATETIME ,

    CONSTRAINT FOREIGN KEY (created_by) REFERENCES user(email) ON UPDATE CASCADE
);

-- ------------------------------------------------------------------------------------------------------------------------
-- MEMBERS

create table member_project (
    member VARCHAR(50) NOT NULL ,
    project VARCHAR(50) NOT NULL ,
    role VARCHAR(30) NOT NULL ,
    joined_date DATETIME DEFAULT current_timestamp ,
    last_open_date DATETIME DEFAULT current_timestamp ,

    PRIMARY KEY (member, project) ,
    CONSTRAINT FOREIGN KEY (member) REFERENCES user(email) ON DELETE CASCADE ON UPDATE CASCADE ,
    CONSTRAINT FOREIGN KEY (project) REFERENCES project(project) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------------------------------------------------------------------------------
-- SPRINTS

create table sprint (
    id INT(4) NOT NULL ,
    project VARCHAR(50) NOT NULL ,
    start_date DATETIME ,
    end_date DATETIME ,
    created_by VARCHAR(50) ,
    total_capacity NUMERIC(3) DEFAULT 0 NOT NULL ,
    members NUMERIC(2) DEFAULT 0 NOT NULL ,
    state CHAR ,

    CONSTRAINT PRIMARY KEY (id, project) ,
    CONSTRAINT FOREIGN KEY (project) REFERENCES project(project) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------------------------------------------------------------------------------
-- TASKS

create table task (
    id INT(7) PRIMARY KEY AUTO_INCREMENT NOT NULL ,
    project VARCHAR(50) NOT NULL ,
    priority INT(4) DEFAULT 9999,
    type VARCHAR(30) NOT NULL ,
    title VARCHAR(150) NOT NULL ,
    state VARCHAR(30) DEFAULT 'New' NOT NULL ,
    points NUMERIC(3) ,
    description VARCHAR(1000) ,
    sprint INT(4) ,
    created_by VARCHAR(50) NOT NULL ,
    assigned_to VARCHAR(50) NULL ,
    created_date DATETIME DEFAULT current_timestamp NOT NULL ,
    update_date DATETIME DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP ,
    close_date DATETIME ,

    CONSTRAINT FOREIGN KEY (project) REFERENCES project(project) ON DELETE CASCADE ON UPDATE CASCADE ,
    CONSTRAINT FOREIGN KEY (assigned_to) REFERENCES user(email) ON DELETE CASCADE ON UPDATE CASCADE ,
    CONSTRAINT FOREIGN KEY (sprint) REFERENCES sprint(id) ON DELETE SET NULL
);

create table task_tags (
    task INT(7) NOT NULL ,
    tag VARCHAR(90) NOT NULL ,
    created_date DATETIME DEFAULT current_timestamp NOT NULL ,

    PRIMARY KEY (task, tag) ,
    CONSTRAINT FOREIGN KEY (task) REFERENCES task(id) ON DELETE CASCADE ON UPDATE CASCADE
);

commit;

-- ------------------------------------------------------------------------------------------------------------------------