-- EXEMPLO E ORIENTAÇÕES BASE
-- CREATE TABLE IF NOT EXISTS `module_default` ( --  criar tabela assim
--   `id` int(11) NOT NULL AUTO_INCREMENT,      -- PRIMARY KEY deve ser sempre ID
--  PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;        -- necessário definir engine e charset

DROP TABLE IF EXISTS `module_crm_business`;
CREATE TABLE `module_crm_business` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `currencie_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `business` varchar(255) DEFAULT NULL,
  `business_phase` ENUM('novo','qualificacao','proposta','fechamento','ganhou','perdeu') NOT NULL DEFAULT 'Novo',
  `price` decimal(10,2) DEFAULT NULL,
  `expected_at` DATE DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sys_currencies_idx` (`currencie_id` ASC),
  INDEX `fk_crm_account_idx` (`account_id` ASC),
  CONSTRAINT `fk_sys_currencies1`
  FOREIGN KEY (`currencie_id`)
  REFERENCES `sys_currencies` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_crm_accounts1`
  FOREIGN KEY (`account_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_contacts`;
CREATE TABLE `module_crm_business_contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),

  INDEX `fk_module_crm_business_idx` (`business_id` ASC),
  INDEX `fk_crm_account_idx` (`account_id` ASC),

  CONSTRAINT `fk_crm_accounts2`
  FOREIGN KEY (`account_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_module_crm_business1`
  FOREIGN KEY (`business_id`)
  REFERENCES `module_crm_business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_companies`;
CREATE TABLE `module_crm_business_companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) DEFAULT NULL,
  `companies_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_module_crm_business_idx` (`business_id` ASC),
  INDEX `fk_crm_companies_idx` (`companies_id` ASC),

  CONSTRAINT `fk_crm_accounts5`
  FOREIGN KEY (`companies_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_module_crm_business2`
  FOREIGN KEY (`business_id`)
  REFERENCES `module_crm_business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_notes`;
CREATE TABLE `module_crm_business_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) DEFAULT NULL,
  `notes` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_module_crm_business_idx` (`business_id` ASC),
  CONSTRAINT `fk_module_crm_business3`
  FOREIGN KEY (`business_id`)
  REFERENCES `module_crm_business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_type_activity`;
CREATE TABLE `module_crm_business_type_activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activity` VARCHAR(255) DEFAULT NULL,
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_activity`;
CREATE TABLE `module_crm_business_activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_id` int(11) DEFAULT NULL,
  `type_activity_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `subject` VARCHAR(255) DEFAULT NULL,
  `date` DATE DEFAULT NULL,
  `hour` TIME DEFAULT NULL,
  `duration` VARCHAR(50) DEFAULT NULL,
  `note` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_module_crm_business_idx` (`business_id` ASC),
  INDEX `fk_module_crm_business_type_activity_idx` (`type_activity_id` ASC),
  INDEX `fk_crm_account_idx` (`account_id` ASC),
  CONSTRAINT `fk_module_crm_business_type_activity1`
  FOREIGN KEY (`type_activity_id`)
  REFERENCES `module_crm_business_type_activity` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_module_crm_business4`
  FOREIGN KEY (`business_id`)
  REFERENCES `module_crm_business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_crm_accounts3`
  FOREIGN KEY (`account_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_activity_contacts`;
CREATE TABLE `module_crm_business_activity_contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activity_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),

  INDEX `fk_module_crm_activity_idx` (`activity_id` ASC),
  INDEX `fk_crm_account_idx` (`account_id` ASC),

  CONSTRAINT `fk_crm_accounts4`
  FOREIGN KEY (`account_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_module_crm_activity2`
  FOREIGN KEY (`activity_id`)
  REFERENCES `module_crm_business_activity` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_activity_companies`;
CREATE TABLE `module_crm_business_activity_companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activity_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),

  INDEX `fk_module_crm_activity_idx` (`activity_id` ASC),
  INDEX `fk_sys_companies_idx` (`company_id` ASC),

  CONSTRAINT `fk_sys_companies`
  FOREIGN KEY (`company_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_module_crm_activity3`
  FOREIGN KEY (`activity_id`)
  REFERENCES `module_crm_business_activity` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_crm_business_activity_business`;
CREATE TABLE `module_crm_business_activity_business` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activity_id` int(11) DEFAULT NULL,
  `business_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),

  INDEX `fk_module_crm_activity_idx` (`activity_id` ASC),
  INDEX `fk_module_crm_business_idx` (`business_id` ASC),

  CONSTRAINT `fk_module_crm_activity4`
  FOREIGN KEY (`activity_id`)
  REFERENCES `module_crm_business_activity` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_module_crm_business5`
  FOREIGN KEY (`business_id`)
  REFERENCES `module_crm_business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


ALTER TABLE `module_crm_business_notes` ADD `user_id` INT(11) NOT NULL AFTER `business_id`, ADD INDEX `fk_sys_users_idx` (`user_id`);

ALTER TABLE `module_crm_business_activity` ADD `user_id` INT(11) NOT NULL AFTER `account_id`, ADD INDEX `fk_sys_users_idx` (`user_id`);