--
-- Table structure for table `module_billing_billings`
--

CREATE TABLE IF NOT EXISTS `module_billing_billings` (
    `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `idaccount` INT(11) NOT NULL,
    `expiry` DATE NOT NULL,
    `value` DECIMAL(10,2) NOT NULL,
    `emission_date` DATE NOT NULL,
    `document_number` VARCHAR(255),
    `historic` TEXT DEFAULT NULL,
    `idcategory` INT(11) NOT NULL,
    `idpayment_method` INT(11) NOT NULL,
    `occurrence` ENUM('Única', 'Mensal', 'Parcelada') NOT NULL,
    `occurrence_expiry` INT(2) DEFAULT NULL,
    `occurrence_plots` INT(2) DEFAULT NULL,
    `competence` VARCHAR(20) DEFAULT NULL,
    `attachment` VARCHAR(255) DEFAULT NULL,
    `markers` VARCHAR(255) DEFAULT NULL,
    `type` ENUM('Income','Expense') NOT NULL,
    CONSTRAINT `fk_billings_crm_accounts`
        FOREIGN KEY (`idaccount`)
        REFERENCES `crm_accounts` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_billings_sys_cats`
        FOREIGN KEY (`idcategory`)
        REFERENCES `sys_cats` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_billings_sys_pmethods`
        FOREIGN KEY (`idpayment_method`)
        REFERENCES `sys_pmethods` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Delete all data in table `sys_cats`
--

DELETE FROM `sys_cats` WHERE `id`>=1;

--
-- Insert data in table `sys_cats`
--

INSERT INTO `sys_cats` (`id`, `name`, `type`, `sorder`) VALUES
('77', 'Água, Luz', 'Expense', '0'),
('78', 'Aluguéis, Condomínio', 'Expense', '0'),
('79', 'Compras', 'Expense', '0'),
('80', 'Impostos, Taxas', 'Expense', '0'),
('81', 'Serviços Gerais', 'Expense', '0'),
('82', 'Telecomunicação, Internet', 'Expense', '0'),
('83', 'Veículos, Transportes', 'Expense', '0'),
('84', 'Pro-labore', 'Income', '0'),
('85', 'Salário', 'Income', '0'),
('86', 'Venda de produto', 'Income', '0'),
('87', 'Empréstimo bancário', 'Income', '0'),
('88', 'Prestação de serviço', 'Income', '0');

--
-- Delete all data in table `sys_pmethods`
--
-- Correção de erro 25/03/2017
-- DELETE FROM `sys_pmethods` WHERE `id`>=1;

--
-- Insert data in table `sys_pmethods`
--
-- Correção de erro 25/03/2017
-- INSERT INTO `sys_pmethods` (`id`, `name`, `sorder`) VALUES
-- ('1', 'Dinheiro', '1'),
-- ('2', 'Cheque', '4'),
-- ('3', 'Cartão de Crédito', '5'),
-- ('5', 'Transferência Eletrônica', '7'),
-- ('9', 'Paypal', '2');

--
-- Alter column `type` and change enum values to capitalize
--

ALTER TABLE `module_billing_billings`
MODIFY COLUMN type ENUM('Income','Expense');

--
-- Alter name of foreign key columns
--

ALTER TABLE `module_billing_billings`
DROP FOREIGN KEY `fk_billings_crm_accounts`,
CHANGE COLUMN `idaccount` `id_account` INT(11);

ALTER TABLE `module_billing_billings`
DROP FOREIGN KEY `fk_billings_sys_cats`,
CHANGE COLUMN `idcategory` `id_category` INT(11);

ALTER TABLE `module_billing_billings`
DROP FOREIGN KEY `fk_billings_sys_pmethods`,
CHANGE COLUMN `idpayment_method` `id_payment_method` INT(11);

-- ALTER TABLE `module_billing_billings`
-- CHANGE COLUMN idaccount id_account INT(11),
-- CHANGE COLUMN idcategory id_category INT(11),
-- CHANGE COLUMN idpayment_method id_payment_method INT(11);

ALTER TABLE `module_billing_billings`
ADD CONSTRAINT `fk_billings_crm_accounts`
	FOREIGN KEY (`id_account`)
	REFERENCES `crm_accounts` (`id`)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
ADD CONSTRAINT `fk_billings_sys_cats`
	FOREIGN KEY (`id_category`)
	REFERENCES `sys_cats` (`id`)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
ADD CONSTRAINT `fk_billings_sys_pmethods`
	FOREIGN KEY (`id_payment_method`)
	REFERENCES `sys_pmethods` (`id`)
	ON DELETE CASCADE
	ON UPDATE CASCADE;

--
-- Alter table name of `module_billing_billings` to `module_billing_records`
--

RENAME TABLE `module_billing_billings` TO `module_billing_records`;

--
-- Alter table `sys_transactions`. Add columns to the billing module
--

ALTER TABLE `sys_transactions`
ADD COLUMN billing_id_record INTEGER(11) DEFAULT NULL,
ADD COLUMN billing_id_account INTEGER(11) DEFAULT NULL,
ADD COLUMN billing_balance DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN billing_tax DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN billing_interest DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN billing_discount DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN billing_addition DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN billing_payed DECIMAL(10,2) NOT NULL,
ADD CONSTRAINT `fk_sys_transactions_module_billing_records`
	FOREIGN KEY (`billing_id_record`)
    REFERENCES `module_billing_records` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sys_transactions_sys_accounts`
	FOREIGN KEY (`billing_id_account`)
    REFERENCES `sys_accounts`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

--
-- Alter table `module_billing_records`. Change column name
--

ALTER TABLE `module_billing_records`
CHANGE COLUMN occurrence_plots occurrence_installments INT(2);

--
-- Table structure for table `module_billing_records_tags`, table many to many.
--

CREATE TABLE IF NOT EXISTS `module_billing_records_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_record` int(11) NOT NULL,
  `id_tag` int(11) NOT NULL,
  CONSTRAINT `fk_billing_records` FOREIGN KEY (`id_record`) REFERENCES `module_billing_records` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_billing_sys_tags` FOREIGN KEY (`id_tag`) REFERENCES `sys_tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--
-- Alter table `module_billing_records`, Add self-relationship column
--

ALTER TABLE `module_billing_records`
ADD COLUMN id_parent_record INT(11) DEFAULT NULL AFTER `id`,
ADD CONSTRAINT `fk_module_billing_records_records`
	FOREIGN KEY (`id_parent_record`)
  REFERENCES `module_billing_records` (`id`)
  ON DELETE SET NULL;

--
-- Alter table `sys_transactions`, drop columns
--

ALTER TABLE `sys_transactions`
DROP COLUMN `billing_balance`,
DROP COLUMN `billing_tax`,
DROP COLUMN `billing_interest`,
DROP COLUMN `billing_discount`,
DROP COLUMN `billing_addition`,
DROP COLUMN `billing_payed`;

--
-- Alter table `module_billing_records`, add columns
--

ALTER TABLE `module_billing_records`
CHANGE `value` `value` DECIMAL(10,2) AFTER `historic`,
ADD COLUMN `balance` DECIMAL(10,2) DEFAULT 0 AFTER `value`,
ADD COLUMN `tax` DECIMAL(10,2) DEFAULT 0 AFTER `balance`,
ADD COLUMN `interest` DECIMAL(10,2) DEFAULT 0 AFTER `tax`,
ADD COLUMN `discount` DECIMAL(10,2) DEFAULT 0 AFTER `interest`,
ADD COLUMN `increase` DECIMAL(10,2) DEFAULT 0 AFTER `discount`,
ADD COLUMN `payed` DECIMAL(10,2) DEFAULT 0 AFTER `increase`,
ADD COLUMN `status` ENUM('Em aberto','Paga','Parcial','Atrasada') DEFAULT 'Em aberto';

--
-- Drop Foreign Key
--

ALTER TABLE `module_billing_records`
DROP FOREIGN KEY `fk_module_billing_records_records`;

--
-- Drop Foreign Key
--

ALTER TABLE `module_billing_records`
DROP FOREIGN KEY `fk_billings_sys_pmethods`;

--
-- Table structure for table `module_billing_history`.
--

CREATE TABLE IF NOT EXISTS `module_billing_history` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `target` varchar(255) DEFAULT NULL COMMENT 'table_name',
    `url` text NOT NULL,
    `user_id` int(11) NOT NULL,
    `ip` varchar(60) NOT NULL,
    `description` text NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `module_billing_cost_center`
--

CREATE TABLE IF NOT EXISTS `module_billing_cost_center` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Alter Table `module_billing_records`
--

ALTER TABLE `module_billing_records`
ADD COLUMN id_cost_center INT(11) NOT NULL AFTER `id_category`;

--
-- Alter Table `sys_transactions`
--

ALTER TABLE `sys_transactions`
ADD COLUMN billing_id_cost_center INT(11) NOT NULL;