DROP TABLE IF EXISTS softsat_locacao_plano_conta;
CREATE TABLE softsat_locacao_plano_conta (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` INT(11) NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL,
    conta VARCHAR(8) NOT NULL,
    nome VARCHAR(255) NOT NULL,
	CONSTRAINT `fk_softsat_locacao_plano_conta_hwapx_user_created_by` FOREIGN KEY (`created_by`) REFERENCES `hwapx_user` (`id`) ON DELETE SET NULL,
	CONSTRAINT `fk_softsat_locacao_plano_conta_hwapx_user_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `hwapx_user` (`id`) ON DELETE SET NULL
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

ALTER TABLE `softsat_locacao_plano_conta`
	ADD UNIQUE INDEX `un_softsat_loacao_plano_conta_conta` (`conta`);

DROP TABLE IF EXISTS softsat_locacao_despesa;
CREATE TABLE softsat_locacao_despesa (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` INT(11) NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL,
    plano_conta_id INTEGER NOT NULL,
    veiculo_id INTEGER,
    valor NUMERIC(12, 2) NOT NULL,
    data DATE NOT NULL,
    observacao VARCHAR(255),
	CONSTRAINT `fk_softsat_locacao_despesa_hwapx_user_created_by` FOREIGN KEY (`created_by`) REFERENCES `hwapx_user` (`id`) ON DELETE SET NULL,
	CONSTRAINT `fk_softsat_locacao_despesa_hwapx_user_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `hwapx_user` (`id`) ON DELETE SET NULL,
	CONSTRAINT `fk_softsat_locacao_despesa_softsat_locacao_plano_conta` FOREIGN KEY (`plano_conta_id`) REFERENCES `softsat_locacao_plano_conta` (`id`),
	CONSTRAINT `fk_softsat_locacao_despesa_softsat_locacao_veiculo` FOREIGN KEY (`veiculo_id`) REFERENCES `softsat_locacao_veiculo` (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

DROP VIEW IF EXISTS softsat_locacao_plano_conta_analitico;
CREATE VIEW softsat_locacao_plano_conta_analitico AS
SELECT 
    pn3.id, 
    CONCAT('[', pn3.conta, '] ', pn1.nome, ' -> ', pn2.nome, ' -> ', pn3.nome) AS descricao,
    pn1.id AS id1,
    pn1.conta AS conta1,
    pn1.nome AS nome1, 
    pn2.id AS id2,
    pn2.conta AS conta2, 
    pn2.nome AS nome2,
    pn3.id AS id3,
    pn3.conta AS conta3,
    pn3.nome AS nome3
FROM softsat_locacao_plano_conta AS pn3
LEFT JOIN softsat_locacao_plano_conta AS pn2 ON pn2.conta = LEFT(pn3.conta, 4)
LEFT JOIN softsat_locacao_plano_conta AS pn1 ON pn1.conta = LEFT(pn3.conta, 1)
WHERE LENGTH(pn3.conta) = 7;

DROP VIEW IF EXISTS softsat_locacao_despesa_detalhe;
CREATE VIEW softsat_locacao_despesa_detalhe AS
SELECT 
    des.*, 
    pn.descricao AS plano_conta, 
    pn.conta3 AS plano_conta_conta, 
    pn.conta1 AS plano_conta_id1, 
    pn.conta1 AS plano_conta_conta1, 
    pn.nome1 AS plano_conta_nome1, 
    pn.conta2 AS plano_conta_id2, 
    pn.conta2 AS plano_conta_conta2, 
    pn.nome2 AS plano_conta_nome2, 
    pn.conta3 AS plano_conta_id3, 
    pn.conta3 AS plano_conta_conta3, 
    pn.nome3 AS plano_conta_nome3, 
    vei.placa AS veiculo_placa, 
    vei.marca AS veiculo_marca, 
    vei.modelo AS veiculo_modelo, 
    vei.grupo AS veiculo_grupo,
    vei.descricao AS veiculo,
    vei.marca_id AS veiculo_marca_id,
    vei.grupo_id AS veiculo_grupo_id
FROM softsat_locacao_despesa AS des
LEFT JOIN softsat_locacao_plano_conta_analitico AS pn ON pn.id = des.plano_conta_id
LEFT JOIN softsat_locacao_veiculo_detalhe AS vei ON vei.id = des.veiculo_id;

INSERT INTO `softsat_locacao_plano_conta` (`id`, `created_on`, `updated_on`, `created_by`, `updated_by`, `conta`, `nome`) VALUES (1, '2018-05-22 16:41:21', '2018-05-22 16:41:21', NULL, NULL, '1', 'Despesas');
