CREATE DATABASE IF NOT EXISTS manifesto_fiscal
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE manifesto_fiscal;

CREATE TABLE companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  trade_name VARCHAR(160) NULL,
  cnpj CHAR(14) NOT NULL,
  state_registration VARCHAR(20) NULL,
  uf CHAR(2) NOT NULL,
  sefaz_environment TINYINT UNSIGNED NOT NULL DEFAULT 2,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY companies_cnpj_unique (cnpj)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fiscal_certificates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  password_encrypted TEXT NOT NULL,
  valid_from DATETIME NULL,
  valid_until DATETIME NULL,
  serial_number VARCHAR(120) NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fiscal_certificates_company_fk
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nsu_cursors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  ult_nsu VARCHAR(15) NOT NULL DEFAULT '0',
  max_nsu VARCHAR(15) NULL,
  last_query_at DATETIME NULL,
  last_status_code VARCHAR(10) NULL,
  last_status_message VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY nsu_cursors_company_unique (company_id),
  CONSTRAINT nsu_cursors_company_fk
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nfe_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  access_key CHAR(44) NOT NULL,
  nsu VARCHAR(15) NULL,
  schema_name VARCHAR(80) NULL,
  document_type VARCHAR(40) NOT NULL,
  issuer_cnpj CHAR(14) NULL,
  issuer_name VARCHAR(160) NULL,
  recipient_cnpj CHAR(14) NULL,
  issued_at DATETIME NULL,
  total_amount DECIMAL(15, 2) NULL,
  xml_storage_path VARCHAR(500) NULL,
  xml_content LONGTEXT NULL,
  raw_summary_xml LONGTEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'received',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY nfe_documents_company_access_key_unique (company_id, access_key),
  KEY nfe_documents_company_nsu_index (company_id, nsu),
  CONSTRAINT nfe_documents_company_fk
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nfe_manifestations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nfe_document_id BIGINT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  event_code VARCHAR(10) NOT NULL,
  event_name VARCHAR(80) NOT NULL,
  justification VARCHAR(255) NULL,
  protocol VARCHAR(80) NULL,
  response_status_code VARCHAR(10) NULL,
  response_status_message VARCHAR(255) NULL,
  request_xml_path VARCHAR(500) NULL,
  response_xml_path VARCHAR(500) NULL,
  request_xml LONGTEXT NULL,
  response_xml LONGTEXT NULL,
  manifested_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY nfe_manifestations_company_index (company_id),
  CONSTRAINT nfe_manifestations_document_fk
    FOREIGN KEY (nfe_document_id) REFERENCES nfe_documents(id)
    ON DELETE CASCADE,
  CONSTRAINT nfe_manifestations_company_fk
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;