CREATE TABLE `task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `condo_id` int(11) DEFAULT NULL,
  `workflow_id` varchar(32) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `description` text,
  `assigned_date` datetime DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `priority` varchar(45) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `created_datetime` datetime DEFAULT NULL,
  `updated_datetime` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `is_star` bit(1) DEFAULT NULL,
  `assignee_revert_date` date DEFAULT NULL,
  `assignee_revert_time` varchar(11) DEFAULT NULL,
  `original_assigned_to` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `assigned_to_role` varchar(25) DEFAULT NULL,
  `original_assigned_to_role` varchar(25) DEFAULT NULL,
  `creator_role` varchar(25) DEFAULT NULL,
  `prev_assigned_to` int(11) DEFAULT NULL,
  `prev_assigned_to_role` varchar(25) DEFAULT NULL,
  `cc` text,
  `bcc` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8;

CREATE TABLE `task_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(100) DEFAULT NULL,
  `status` varchar(1) DEFAULT 'A',
  `condo_id` int(11) DEFAULT NULL,
  `is_default` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

CREATE TABLE `task_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `status` varchar(255) NOT NULL,
  `created_datetime` datetime NOT NULL,
  `updated_datetime` datetime NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `action` varchar(1) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `assignee_revert_date` date DEFAULT NULL,
  `assignee_revert_time` varchar(11) DEFAULT NULL,
  `creator_role` varchar(25) DEFAULT NULL,
  `assigned_to_role` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=645 DEFAULT CHARSET=utf8;

CREATE TABLE `task_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) DEFAULT NULL,
  `sender_id` int(11) DEFAULT NULL,
  `message` text,
  `attachment` text,
  `created_datetime` datetime DEFAULT NULL,
  `updated_datetime` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `creator_role` varchar(25) DEFAULT NULL,
  `assigned_to_role` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=596 DEFAULT CHARSET=utf8;

CREATE TABLE `task_property_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trial_startdate` date DEFAULT NULL,
  `trial_enddate` date DEFAULT NULL,
  `subscription_startdate` date DEFAULT NULL,
  `subscription_enddate` date DEFAULT NULL,
  `deactivation_date` timestamp NULL DEFAULT NULL,
  `apply_to_condo_id` int(11) DEFAULT NULL,
  `is_subscribed` varchar(1) DEFAULT 'N',
  `bcc` text,
  `status` varchar(45) DEFAULT NULL,
  `created_datetime` timestamp NULL DEFAULT NULL,
  `updated_datetime` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8;

CREATE TABLE `task_recurrence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) DEFAULT NULL,
  `last_run` datetime DEFAULT NULL,
  `next_run` datetime DEFAULT NULL,
  `last_result` varchar(45) DEFAULT NULL,
  `created_datetime` datetime DEFAULT NULL,
  `updated_datetime` datetime DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `updated_by` varchar(45) DEFAULT NULL,
  `startDay` date DEFAULT NULL,
  `limitNum` int(11) DEFAULT NULL,
  `repeatNum` int(11) DEFAULT NULL,
  `frequencyType` varchar(45) DEFAULT NULL,
  `repeatType` varchar(45) DEFAULT NULL,
  `limitEnable` int(11) DEFAULT NULL,
  `endDay` date DEFAULT NULL,
  `run` int(11) DEFAULT NULL,
  `weeklyRepeat` varchar(100) DEFAULT NULL,
  `dayOfMonth1` int(11) DEFAULT NULL,
  `dayOfMonth2` varchar(45) DEFAULT NULL,
  `month_opt` tinyint(4) DEFAULT NULL,
  `cron` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;

CREATE TABLE `service_provider` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `condo_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `business_address` varchar(500) DEFAULT NULL,
  `resgistration_number` text,
  `services` text,
  `cost` double DEFAULT NULL,
  `contract_startdate` date DEFAULT NULL,
  `contract_enddate` date DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `frequency` varchar(100) DEFAULT NULL,
  `office_no` varchar(15) DEFAULT NULL,
  `mobile_no` varchar(15) DEFAULT NULL,
  `fax_no` varchar(15) DEFAULT NULL,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `created_datetime` datetime NOT NULL,
  `updated_datetime` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;



CREATE TABLE `sw_workflow` (
  `id` varchar(32) NOT NULL,
  `initial_status_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `initial_status_id` (`initial_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `sw_metadata` (
  `workflow_id` varchar(32) NOT NULL,
  `status_id` varchar(32) NOT NULL,
  `key` varchar(64) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  UNIQUE KEY `workflow_status_id` (`workflow_id`,`status_id`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `sw_status` (
  `id` varchar(32) NOT NULL,
  `workflow_id` varchar(32) NOT NULL,
  `label` varchar(64) DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`workflow_id`),
  KEY `workflow_id` (`workflow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sw_transition` (
  `workflow_id` varchar(32) NOT NULL,
  `start_status_id` varchar(32) NOT NULL,
  `end_status_id` varchar(32) NOT NULL,
  PRIMARY KEY (`workflow_id`,`start_status_id`,`end_status_id`),
  KEY `workflow_start_status_id` (`workflow_id`,`start_status_id`),
  KEY `workflow_end_status_id` (`workflow_id`,`end_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sw_status` VALUES ('Cancel','task','Cancelled',3),('Close','task','Completed',4),('Deferred','task','Deferred',2),('Open','task','In Progress',1);

INSERT INTO `sw_transition` VALUES ('task','Cancel','Close'),('task','Deferred','Cancel'),('task','Deferred','Close'),('task','Open','Cancel'),('task','Open','Close'),('task','Open','Deferred');

INSERT INTO `sw_workflow` VALUES ('task','Open');

INSERT INTO `task_category` VALUES (1,'Administrative ','A',33,1),(2,'Technical ','A',33,1),(3,'Service Provider','A',33,1),(4,'Committee ','A',33,1),(5,'Technical ','A',33,1),(6,'House Keeping','A',33,1),(7,'HQ ','A',33,1),(8,'Work Order','A',33,1),(9,'Preventive Maintenance','A',33,1),(10,'Others','A',33,1);