CREATE SCHEMA `action` ;

CREATE TABLE `action`.`goals` (
  `goal_id` INT NOT NULL AUTO_INCREMENT,
  `account_id` INT(11) NULL,
  `person_id` INT(11) NULL,
  `goal_title` VARCHAR(245) NULL DEFAULT NULL,
  `goal_description` VARCHAR(1000) NULL DEFAULT NULL,
  `goal_due_date` DATETIME NULL DEFAULT NULL,
  `goal_status` INT NULL DEFAULT 1,
  PRIMARY KEY (`goal_id`));

ALTER TABLE `action`.`goals` 
ADD INDEX `goal_person_id` (`account_id` ASC, `person_id` ASC) VISIBLE;
;

CREATE TABLE `action`.`goal_status` (
  `goal_status_id` INT NOT NULL AUTO_INCREMENT,
  `goal_status_label` VARCHAR(45) NULL,
  PRIMARY KEY (`goal_status_id`));

INSERT INTO `action`.`goal_status` (`goal_status_label`) VALUES ('Not Started');
INSERT INTO `action`.`goal_status` (`goal_status_label`) VALUES ('In Progress');
INSERT INTO `action`.`goal_status` (`goal_status_label`) VALUES ('Complete');

ALTER TABLE `action`.`goals` 
ADD COLUMN `goal_complete_percentage` INT NULL DEFAULT 0 AFTER `goal_status`;

CREATE TABLE `action`.`tasks` (
  `task_id` INT NOT NULL AUTO_INCREMENT,
  `goal_id` INT NOT NULL,
  `task_text` VARCHAR(245) NULL,
  `task_completed` TINYINT NULL DEFAULT 0,
  PRIMARY KEY (`task_id`));

ALTER TABLE `action`.`tasks` 
ADD INDEX `tasks_goal_id_fk_idx` (`goal_id` ASC) VISIBLE;
;
ALTER TABLE `action`.`tasks` 
ADD CONSTRAINT `tasks_goal_id_fk`
  FOREIGN KEY (`goal_id`)
  REFERENCES `action`.`goals` (`goal_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;
  
CREATE TABLE `action`.`goal_to_capture_item` (
  `goal_to_capture_item_id` INT NOT NULL AUTO_INCREMENT,
  `goal_id` INT NULL,
  `capture_type` VARCHAR(45) NULL,
  `item_type` VARCHAR(45) NULL,
  `item_id` INT NULL,
  PRIMARY KEY (`goal_to_capture_item_id`));

ALTER TABLE `action`.`goal_to_capture_item` 
ADD INDEX `goal_to_capture_goal_id_fk_idx` (`goal_id` ASC) VISIBLE;
;
ALTER TABLE `action`.`goal_to_capture_item` 
ADD CONSTRAINT `goal_to_capture_goal_id_fk`
  FOREIGN KEY (`goal_id`)
  REFERENCES `action`.`goals` (`goal_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

CREATE TABLE `action`.`translations_core` (
  `tx_core_id` INT NOT NULL AUTO_INCREMENT,
  `tx_core_loc_key` VARCHAR(245) NULL,
  `tx_core_language_code` VARCHAR(100) NULL,
  `tx_core_translation` TEXT NULL,
  PRIMARY KEY (`tx_core_id`));

  CREATE TABLE `action`.`assigned_items` (
  `assigned_item_id` INT NOT NULL AUTO_INCREMENT,
  `item_text` VARCHAR(500) NULL,
  `item_due_date` DATETIME NULL,
  PRIMARY KEY (`assigned_item_id`));

CREATE TABLE `action`.`assigned_items_to_people` (
  `assigned_items_to_people_id` int(11) NOT NULL,
  `assigned_item_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`assigned_items_to_people_id`)
);

ALTER TABLE `action`.`assigned_items_to_people` 
ADD COLUMN `status` INT NULL DEFAULT 1 AFTER `person_id`,
CHANGE COLUMN `assigned_items_to_people_id` `assigned_items_to_people_id` INT(11) NOT NULL AUTO_INCREMENT ;

CREATE TABLE `action`.`account_settings` (
  `account_setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `setting_key` varchar(500) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`account_setting_id`),
  UNIQUE KEY `unique_setting` (`account_id`,`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=810 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `action`.`logs` (
  `log_id` int(11) NOT NULL,
  `person_id` int(11) DEFAULT NULL,
  `category` varchar(45) DEFAULT NULL,
  `change_details` varchar(45) DEFAULT NULL,
  `change_date` DATETIME DEFAULT NOW(),
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `action`.`goals` 
ADD COLUMN `date_completed` DATETIME NULL DEFAULT NULL AFTER `goal_complete_percentage`;

ALTER TABLE `action`.`logs` 
ADD COLUMN `goal_id` INT(11) NULL DEFAULT NULL AFTER `change_date`,
ADD COLUMN `assigned_item_id` INT(11) NULL DEFAULT NULL AFTER `goal_id`, RENAME TO  `action`.`log_action_planning` ;

ALTER TABLE `action`.`log_action_planning` 
CHANGE COLUMN `change_date` `change_date` DATETIME NULL DEFAULT NOW() ;
ALTER TABLE `action`.`log_action_planning` 
ADD COLUMN `account_id` INT(11) NULL AFTER `log_id`;

ALTER TABLE `action`.`log_action_planning` 
ADD COLUMN `translation_key` VARCHAR(245) NULL AFTER `assigned_item_id`;


ALTER TABLE `action`.`assigned_items_to_people` 
ADD COLUMN `date_completed` DATETIME NULL DEFAULT NULL AFTER `status`;

ALTER TABLE `action`.`assigned_items_to_people` 
ADD UNIQUE INDEX `unique_item_to_person` (`assigned_item_id` ASC, `account_id` ASC, `person_id` ASC) VISIBLE;
;

ALTER TABLE `action`.`log_action_planning` 
CHANGE COLUMN `log_id` `log_id` INT(11) NOT NULL AUTO_INCREMENT ;