-- 005: módulo fiscal (estrutura; regras conforme documentação oficial serão implementadas na etapa fiscal)
CREATE TABLE fiscal_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  cnpj VARCHAR(14) NULL,
  legal_name VARCHAR(160) NULL,
  trade_name VARCHAR(160) NULL,
  state_registration VARCHAR(20) NULL,
  municipal_registration VARCHAR(20) NULL,
  address_street VARCHAR(160) NULL,
  address_number VARCHAR(20) NULL,
  address_district VARCHAR(100) NULL,
  address_city VARCHAR(100) NULL,
  address_city_ibge VARCHAR(7) NULL,
  address_state CHAR(2) NULL,
  address_zip VARCHAR(8) NULL,
  tax_regime VARCHAR(30) NULL,
  environment ENUM('HOMOLOGATION','PRODUCTION') NOT NULL DEFAULT 'HOMOLOGATION',
  csc_id VARCHAR(10) NULL,
  csc_secret_enc TEXT NULL,
  certificate_path VARCHAR(255) NULL,
  certificate_password_enc TEXT NULL,
  certificate_expires_at DATETIME NULL,
  extra JSON NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fiscal_settings_store (store_id),
  CONSTRAINT fk_fs_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_fs_store FOREIGN KEY (store_id) REFERENCES stores (id)
);
CREATE TABLE fiscal_sequences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NOT NULL,
  model VARCHAR(4) NOT NULL DEFAULT "65",
  series SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  environment ENUM('HOMOLOGATION','PRODUCTION') NOT NULL,
  next_number INT UNSIGNED NOT NULL DEFAULT 1,
  UNIQUE KEY uq_fiscal_seq (store_id, model, series, environment),
  CONSTRAINT fk_fseq_store FOREIGN KEY (store_id) REFERENCES stores (id)
);
CREATE TABLE fiscal_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  sale_id BIGINT UNSIGNED NOT NULL,
  model VARCHAR(4) NOT NULL DEFAULT "65",
  series SMALLINT UNSIGNED NOT NULL,
  number INT UNSIGNED NOT NULL,
  environment ENUM('HOMOLOGATION','PRODUCTION') NOT NULL,
  access_key CHAR(44) NULL,
  status ENUM('DRAFT','PENDING','AUTHORIZED','REJECTED','CANCELED','DENIED','CONTINGENCY','VOIDED') NOT NULL DEFAULT 'DRAFT',
  protocol VARCHAR(30) NULL,
  status_code VARCHAR(10) NULL,
  status_message VARCHAR(255) NULL,
  xml_path VARCHAR(255) NULL,
  qrcode_url TEXT NULL,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,
  issued_at DATETIME NULL,
  authorized_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fd_uuid (uuid),
  UNIQUE KEY uq_fd_number (store_id, model, series, environment, number),
  UNIQUE KEY uq_fd_key (access_key),
  KEY idx_fd_sale (sale_id),
  KEY idx_fd_status (company_id, status),
  CONSTRAINT fk_fd_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_fd_store FOREIGN KEY (store_id) REFERENCES stores (id),
  CONSTRAINT fk_fd_sale FOREIGN KEY (sale_id) REFERENCES sales (id)
);
CREATE TABLE fiscal_document_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fiscal_document_id BIGINT UNSIGNED NOT NULL,
  sale_item_id BIGINT UNSIGNED NULL,
  item_number SMALLINT UNSIGNED NOT NULL,
  description VARCHAR(190) NOT NULL,
  ncm VARCHAR(10) NULL,
  cfop VARCHAR(6) NULL,
  unit VARCHAR(10) NOT NULL,
  quantity DECIMAL(14,4) NOT NULL,
  unit_price DECIMAL(14,4) NOT NULL,
  total DECIMAL(14,2) NOT NULL,
  tax_data JSON NULL,
  CONSTRAINT fk_fdi_doc FOREIGN KEY (fiscal_document_id) REFERENCES fiscal_documents (id) ON DELETE CASCADE,
  CONSTRAINT fk_fdi_item FOREIGN KEY (sale_item_id) REFERENCES sale_items (id)
);
CREATE TABLE fiscal_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fiscal_document_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(30) NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  protocol VARCHAR(30) NULL,
  status_code VARCHAR(10) NULL,
  message VARCHAR(255) NULL,
  xml_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_fev_doc (fiscal_document_id),
  CONSTRAINT fk_fev_doc FOREIGN KEY (fiscal_document_id) REFERENCES fiscal_documents (id) ON DELETE CASCADE
);
CREATE TABLE fiscal_authorizations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fiscal_document_id BIGINT UNSIGNED NOT NULL,
  attempt SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  request_at DATETIME NOT NULL,
  response_at DATETIME NULL,
  status_code VARCHAR(10) NULL,
  message VARCHAR(255) NULL,
  protocol VARCHAR(30) NULL,
  raw_response_path VARCHAR(255) NULL,
  KEY idx_fa_doc (fiscal_document_id),
  CONSTRAINT fk_fa_doc FOREIGN KEY (fiscal_document_id) REFERENCES fiscal_documents (id) ON DELETE CASCADE
);
CREATE TABLE fiscal_contingency (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fiscal_document_id BIGINT UNSIGNED NOT NULL,
  reason VARCHAR(255) NOT NULL,
  entered_at DATETIME NOT NULL,
  status ENUM('ACTIVE','TRANSMITTED','FAILED') NOT NULL DEFAULT 'ACTIVE',
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NULL,
  authorized_at DATETIME NULL,
  KEY idx_fc_doc (fiscal_document_id),
  CONSTRAINT fk_fcont_doc FOREIGN KEY (fiscal_document_id) REFERENCES fiscal_documents (id) ON DELETE CASCADE
);