/* FORM CYCLE CRON - run every ~5 minutes (or however often we run email crons...quite often)
    1) IDENTIFY COMPLETE CYCLES: form_cycles.end_datetime <= NOW() and form_cycles.closed_datetime IS NULL
        -- set closed_datetime = NOW()
    2) CREATE NEW CYCLE RECORD: start_date = DATE(end_date) + INTERVAL 1 DAY of previous cycle, end_date = start_date + calculated cycle duration
    3) CREATE NEW RESPONDENTS: for each person assigned to a form, create new respondent record tied to new cycle and form
*/

/* on team form assignment changes:
    check all current cycle respondent records for affected teams; update form assignments where needed and delete previous responses
    "SELECT fr.respondent_id,
            nhtfi.form_id AS form_id,
            fr.form_id AS old_form_id
        FROM action.form_respondents fr
        JOIN action.form_cycles fc ON (fr.cycle_id = fc.cycle_id)
        LEFT JOIN action.hierarchy_node_to_form_id hntfi ON (hntfi.node_id = fr.node_id AND hntfi.account_id = fc.account_id)
        WHERE 
            fc.account_id = ?
            AND fc.closed_datetime IS NULL
            AND fr.form_id != nhtfi.form_id;";
        -- delete from response tables where respondent_id IN (ids from above query)
        -- update respondent records to set form_id = nhtfi.form_id where respondent_id IN (ids from above query)
*/

/* on hierarchy publishing:
    check all current cycle respondent records; update form assignments where needed and delete previous responses
      -- check for any respondent where form_respondents.node_id !== new hierarchy node_id
        -- update form_respondents.node_id to new node_id WHERE respondent_id IN (ids from above query) AND form_respondents.cycle_id = current open cycle
        -- check team form assignment changes using above functionality, as hierarchy publishing may trigger team form assignment changes
        -- check manager-specific content and reset if necessary - contribution manager_ratings/comments, form respondent comments
*/

/* 
on respondent access:
    --check that all form items are present in the data tables
    --delete any form items from the data tables that are not present in the form
    --pre-populate any priorities items that are in the backlog/existing priorities but not included in the form yet
*/

CREATE TABLE action.form_cycles (
    cycle_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    account_id INT(11) NOT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME DEFAULT NULL, /* designated timestamp of cycle end based on account settings */
    closed_datetime DATETIME DEFAULT NULL, /* timestamp of cron closing cycle - if NULL, cycle is still open */
    CONSTRAINT form_cycle_to_account FOREIGN KEY (account_id) REFERENCES configure.accounts(account_id)
        ON DELETE CASCADE
)

CREATE TABLE action.form_respondents (
    respondent_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    submitted_datetime DATETIME DEFAULT NULL, /* updated when respondent submits form */
    cycle_id INT(11) NOT NULL,
    form_id INT(11) NOT NULL,
    person_id INT(11) NOT NULL,
    hierarchy_id INT(11) NOT NULL,
    node_id INT(11) NOT NULL,
    manager_ids mediumtext DEFAULT '[]',
    priority_count INT(11) DEFAULT 0, 
    CONSTRAINT respondent_to_cycle FOREIGN KEY (cycle_id) REFERENCES action.form_cycles(cycle_id)
        ON DELETE CASCADE,
    CONSTRAINT respondent_to_form FOREIGN KEY (form_id) REFERENCES action.forms(form_id)
        ON DELETE CASCADE,
    CONSTRAINT respondent_to_person UNIQUE (cycle_id, person_id)
)

/* LIBRARY ITEM RESPONSES */

CREATE TABLE action.single_select_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    respondent_id INT(11) NOT NULL, /* ties to respondent_id in form_respondents table */
    item_id INT(11) NOT NULL,
    response INT(11) DEFAULT NULL,
    CONSTRAINT single_select_response_to_respondent FOREIGN KEY (respondent_id) REFERENCES action.form_respondents(respondent_id)
        ON DELETE CASCADE,
    CONSTRAINT single_select_item_to_person UNIQUE (respondent_id, item_id)
)

CREATE TABLE action.open_text_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    respondent_id INT(11) NOT NULL, /* ties to respondent_id in form_respondents table */
    item_id INT(11) NOT NULL,
    response mediumtext DEFAULT NULL,
    CONSTRAINT open_text_response_to_respondent FOREIGN KEY (respondent_id) REFERENCES action.form_respondents(respondent_id)
        ON DELETE CASCADE,
    CONSTRAINT open_text_response_to_person UNIQUE (respondent_id, item_id)
)

/* CONTRIBUTION */

CREATE TABLE action.contribution_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    respondent_id INT(11) NOT NULL, /* ties to respondent_id in form_respondents table */
    library_scale_id INT(11) NOT NULL, /* ties to library_scale_id of contribution item */
    response INT(11) DEFAULT NULL,
    CONSTRAINT contribution_response_to_respondent FOREIGN KEY (respondent_id) REFERENCES action.form_respondents(respondent_id)
        ON DELETE CASCADE,
    CONSTRAINT contribution_response_to_person UNIQUE (respondent_id)
)

CREATE TABLE action.contribution_manager_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    respondent_id INT(11) NOT NULL, /* ties to respondent_id in form_respondents table */
    manager_id INT(11) DEFAULT NULL,
    response INT(11) DEFAULT NULL,
    CONSTRAINT manager_contribution_response_to_respondent FOREIGN KEY (respondent_id) REFERENCES action.form_respondents(respondent_id)
        ON DELETE CASCADE,
    CONSTRAINT contribution_response_to_manager UNIQUE (respondent_id, manager_id)
)

/* ACCOMPLISHMENTS */

CREATE TABLE action.accomplishment_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    account_id INT(11) NOT NULL,
    person_id INT(11) NOT NULL,
    respondent_id INT(11) DEFAULT NULL, /* if accomplishment created via form, populate this value also */
    accomplishment_text mediumtext DEFAULT NULL,
    is_deleted TINYINT(1) DEFAULT 0,
    CONSTRAINT accomplishment_response_to_respondent FOREIGN KEY (respondent_id) REFERENCES action.form_respondents(respondent_id)
        ON DELETE CASCADE
)

RENAME TABLE action.accomplishment_responses TO action.accomplishments;


CREATE TABLE action.accomplishment_actions (
    action_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    response_id INT(11) NOT NULL, /*ties to response_id in accomplishments */
    reporter_id INT(11) NOT NULL, /* person_id of user who performed the action */
    action_type VARCHAR(300) NOT NULL, /* 'reaction'|'comment'|'share' */
    action_text mediumtext DEFAULT NULL, /* populated if 'comment' */
    shared_to INT(11) DEFAULT NULL, /* person_id of user who receives share; populated if  action_type = 'share' */
    CONSTRAINT accomplishment_action_to_accomplishment FOREIGN KEY (response_id) REFERENCES action.accomplishments(response_id)
        ON DELETE CASCADE
)

/* PRIORITIES */

CREATE TABLE action.priority_responses ( /* this table stores form-specific priorities that have not been submitted yet */
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    cycle_id INT(11) DEFAULT NULL,
    form_id INT(11) DEFAULT NULL,
    respondent_id INT(11) DEFAULT NULL,
    priority_text mediumtext DEFAULT NULL,
    priority_id INT(11) DEFAULT NULL, /* if priority comes from backlog/existing priority, include id here */
    priority_status_id VARCHAR(50) DEFAULT NULL /* the priority status selected by respondent on form; ties to priority_status_id in priority_statuses table */
    is_new_priority TINYINT(1) DEFAULT 1 /* if priority is new (not from backlog/existing priority), set to 1; if priority comes from backlog/existing priority, set to 0 */
)

DROP TABLE action.priority_responses;

    CREATE TABLE action.priority_statuses (
        priority_status_id VARCHAR(50) PRIMARY KEY,
        priority_status VARCHAR(200) NOT NULL,
        active_only TINYINT(1) DEFAULT 1 /* any active-only status = 1 */
    )

    INSERT INTO action.priority_statuses (priority_status_id, priority_status, active_only)
    VALUES
    ('active', 'Active', 0),
    ('blocked', 'Blocked', 1),
    ('complete', 'Completed', 0),
    ('backlog', 'Backlog', 0);

    CREATE TABLE action.priorities ( /* this table includes priorities that are created independently OR are submitted via completed form */
        priority_id INT(11) AUTO_INCREMENT PRIMARY KEY,
        created_datetime DATETIME DEFAULT NOW(),
        modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
        status_update_datetime DATETIME DEFAULT NULL,
        start_datetime DATETIME DEFAULT NULL, /* modified to current timestamp every time the priority is moved from the backlog to active, or created */
        account_id INT(11) NOT NULL,
        person_id INT(11) NOT NULL,
        respondent_id INT(11) DEFAULT NULL, /* if priority is created via form submission, tie to respondent_id in form_respondents table */
        priority_text mediumtext DEFAULT NULL,
        priority_status_id VARCHAR(50) DEFAULT NULL, /* ties to priority_status_id in priority_statuses table */
        is_deleted TINYINT(1) DEFAULT 0,
        CONSTRAINT priority_response_to_account FOREIGN KEY (account_id) REFERENCES configure.accounts(account_id)
            ON DELETE CASCADE
    )

    CREATE TABLE action.priority_log (
        priority_id INT(11) NOT NULL, /* references priorities.priority_id */
        created_datetime DATETIME DEFAULT NOW(),
        person_id INT(11) NOT NULL,
        method varchar(50) NOT NULL, /* 'form' | 'manual' */
        translation_code VARCHAR(265) NOT NULL, /* translation code used to represent change in log */
        old_value mediumtext DEFAULT NULL,
        new_value mediumtext DEFAULT NULL,
        CONSTRAINT priority_log_to_priority FOREIGN KEY (priority_id) REFERENCES action.priorities(priority_id)
            ON DELETE CASCADE
    )



/* NEXT STEPS */
CREATE TABLE action.recognition_responses (
    response_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    account_id INT(11) NOT NULL, /* the account_id of the reporter and person */
    reporter_id INT(11) NOT NULL, /* the person_id of the user who provides recognition */
    person_id INT(11) NOT NULL, /* the person_id of the user who receives recognition */
    recognition_text mediumtext DEFAULT NULL,
    CONSTRAINT recognition_response_to_account FOREIGN KEY (account_id) REFERENCES configure.accounts(account_id)
        ON DELETE CASCADE
)

CREATE TABLE action.recognition_actions (
    action_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    created_datetime DATETIME DEFAULT NOW(),
    modified_datetime DATETIME DEFAULT NOW() ON UPDATE NOW(),
    response_id INT(11) NOT NULL, /* ties to response_id in recognition_responses */
    reporter_id INT(11) NOT NULL, /* person_id of user who performed the action */
    action_type VARCHAR(300) NOT NULL, /* 'reaction'|'comment'|'share' */
    action_text mediumtext DEFAULT NULL, /* populated if 'comment' */
    shared_to INT(11) DEFAULT NULL, /* person_id of user who receives share; populated if 'share' */
    CONSTRAINT recognition_action_to_recognition FOREIGN KEY (response_id) REFERENCES action.recognition_responses(response_id)
        ON DELETE CASCADE
)