CREATE TABLE IF NOT EXISTS `app_fiscal_note` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number_note` int(11) DEFAULT NULL,
  `series` int(11) DEFAULT NULL,
  `type` enum('NFC-e','NFe') DEFAULT NULL,
  `issuance_date` datetime DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `account` varchar(100) DEFAULT NULL,
  `id_account` int(11) DEFAULT NULL,
  `key` varchar(60) DEFAULT NULL,
  `status` enum('Autorizada','Cancelada','Inutilizada') DEFAULT NULL,
  `receipt_number` varchar(30) DEFAULT NULL,
  `protocol_number` varchar(30) DEFAULT NULL,
  `pasta` varchar(45) DEFAULT NULL COMMENT 'AAAA/MM',
  `code_status` int(11) DEFAULT NULL,
  `justify` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- define a estrutura de configurações 
CREATE TABLE `app_fiscal_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` text,
  `value` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_name');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_fantasy_name');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_cnpj');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_ie');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_iest');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_im');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_address');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_number');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_complement');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_neighborhood');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_state_id');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_city_id');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_phone');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_email');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_crt');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_cnae');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('company_zip');
INSERT INTO `app_fiscal_config` (`key`, `value`) VALUES ('company_code_country', '1058');
INSERT INTO `app_fiscal_config` (`key`, `value`) VALUES ('company_country_name', 'Brasil');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_img_logo');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_number_nfe');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_number_nfce');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_series_nfe');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_series_nfce');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_environment');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('certificate');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('password_certificate');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_token_nfce');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_token_nfce_id');



-- adiciona dados que identifiquei para melhorar o gerenciador
ALTER TABLE `app_fiscal_note` 
ADD COLUMN `invoice_id` INT(11) NULL AFTER `justify`;


ALTER TABLE `app_fiscal_note` 
ADD COLUMN `environment` ENUM('homologacao', 'producao') NULL AFTER `invoice_id`;

-- Armazenamento das notas importadas.
CREATE TABLE IF NOT EXISTS `app_fiscal_nfe_imported` (
  `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `key` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- Atualização para funcionar nfc-e
ALTER TABLE `app_fiscal_note` 
CHANGE COLUMN `type` `type` ENUM('NFCe', 'NFe') NULL DEFAULT NULL ;


--
-- LEONARDO LOPES RAMOS - 20/09/2017
--
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_obs_nf');

--
-- LEONARDO LOPES RAMOS - 03/10/2017
--
INSERT INTO `app_fiscal_config` (`key`) VALUES ('aliq_icms');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('aliq_pis');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('aliq_cofins');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('aliq_iss');

--
-- ANDRÉ CAMARGO - 08/10/2017  
--
ALTER TABLE `app_fiscal_nfe_imported` 
ADD COLUMN `type` VARCHAR(45) NULL AFTER `key`,
ADD COLUMN `series` VARCHAR(45) NULL AFTER `type`,
ADD COLUMN `numero` VARCHAR(45) NULL AFTER `series`,
ADD COLUMN `crm_account_id` INT NULL COMMENT 'Fornecedor';

ALTER TABLE `app_fiscal_nfe_imported` 
ADD COLUMN `json` TEXT NULL AFTER `crm_account_id`;

ALTER TABLE `app_fiscal_nfe_imported` 
CHANGE COLUMN `numero` `number` INT NULL DEFAULT NULL ;

--
-- LEONARDO LOPES RAMOS - 09/10/2017  
--
ALTER TABLE `app_fiscal_nfe_imported` 
ADD COLUMN `emission_date` VARCHAR(45) NULL,
ADD COLUMN `input_date` DATE NULL;

ALTER TABLE `app_fiscal_nfe_imported` 
ADD COLUMN `import` ENUM('manual', 'imported') NULL DEFAULT 'imported' AFTER `input_date`;

-- marca todos os registros com json gravado como sendo importação manual, correção
-- caso já exista algum lançamento onde for atualizar
update app_fiscal_nfe_imported set import='manual' where json is not null and id>=1;


--
-- LEONARDO LOPES RAMOS - 08/11/2010  
--
ALTER TABLE `app_fiscal_note` 
CHANGE COLUMN `type` `type` ENUM('NFCe','NFe','NFSe') NULL DEFAULT NULL ,
CHANGE COLUMN `status` `status` ENUM('Autorizada','Cancelada','Inutilizada','Emitido') NULL DEFAULT NULL ;

-- Inseri os dados para NFSe
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_number_nfse');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_series_nfse');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('aliq_issqn');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('note_chave_nfse');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('unidadegestora_nfse');
INSERT INTO `app_fiscal_config` (`key`) VALUES ('cnes');