CREATE TABLE IF NOT EXISTS app_meta (meta_key VARCHAR(80) PRIMARY KEY, meta_value VARCHAR(255) NOT NULL);
INSERT INTO app_meta VALUES ('app_version','0.4.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','operator') NOT NULL DEFAULT 'operator', active TINYINT NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS companies (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, legal_name VARCHAR(190) NOT NULL, trade_name VARCHAR(190), tax_id VARCHAR(20) NOT NULL UNIQUE, state_registration VARCHAR(30), municipal_registration VARCHAR(30), state_code CHAR(2) NOT NULL, city_ibge CHAR(7), tax_regime VARCHAR(20), environment ENUM('homologacao','producao') NOT NULL DEFAULT 'homologacao', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_companies (user_id BIGINT UNSIGNED NOT NULL, company_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(user_id,company_id), FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_documents (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, doc_type ENUM('NFE','NFCE','NFSE','CTE','MDFE') NOT NULL, series VARCHAR(6) NOT NULL DEFAULT '1', doc_number BIGINT UNSIGNED NULL, recipient_name VARCHAR(190), recipient_tax_id VARCHAR(20), description TEXT, gross_amount DECIMAL(15,2) NOT NULL DEFAULT 0, status ENUM('rascunho','validando','fila','enviado','autorizado','rejeitado','cancelado','encerrado') NOT NULL DEFAULT 'rascunho', access_key VARCHAR(80), protocol VARCHAR(80), rejection_reason TEXT, xml_path VARCHAR(255), created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(company_id,doc_type,status),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fiscal_document_id BIGINT UNSIGNED NOT NULL, event_type VARCHAR(50) NOT NULL, event_status VARCHAR(30) NOT NULL, event_payload JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fiscal_document_id BIGINT UNSIGNED NOT NULL, operation VARCHAR(30) NOT NULL, status ENUM('pending','processing','failed','done') NOT NULL DEFAULT 'pending', attempts INT NOT NULL DEFAULT 0, scheduled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_error TEXT, UNIQUE KEY uq_doc_operation (fiscal_document_id,operation), FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED, company_id BIGINT UNSIGNED, action VARCHAR(80) NOT NULL, context_json JSON, ip VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_parameters (company_id BIGINT UNSIGNED NOT NULL, doc_type VARCHAR(10) NOT NULL, enabled TINYINT NOT NULL DEFAULT 0, series VARCHAR(6) NOT NULL DEFAULT '1', last_number BIGINT UNSIGNED NOT NULL DEFAULT 0, environment ENUM('homologacao','producao') NOT NULL DEFAULT 'homologacao', PRIMARY KEY(company_id,doc_type),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Amasystem Emissor Fiscal: v0.1.0 -> v0.2.0 (executar uma vez)
CREATE TABLE IF NOT EXISTS fiscal_catalog (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL,
 kind ENUM('produto','servico') NOT NULL, sku VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL, unit VARCHAR(10) NOT NULL DEFAULT 'UN',
 ncm VARCHAR(8) NULL, cest VARCHAR(7) NULL, cfop VARCHAR(4) NULL,
 service_code VARCHAR(20) NULL, nbs VARCHAR(12) NULL,
 cst_ibs_cbs VARCHAR(3) NULL, class_trib VARCHAR(6) NULL,
 unit_price DECIMAL(15,2) NOT NULL DEFAULT 0, active TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_catalog_company_sku(company_id,sku),
 FOREIGN KEY(company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_document_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fiscal_document_id BIGINT UNSIGNED NOT NULL,
 catalog_id BIGINT UNSIGNED NOT NULL, description VARCHAR(190) NOT NULL,
 quantity DECIMAL(15,4) NOT NULL, unit_price DECIMAL(15,2) NOT NULL,
 total DECIMAL(15,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(fiscal_document_id), FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id),
 FOREIGN KEY(catalog_id) REFERENCES fiscal_catalog(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_preflight_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fiscal_document_id BIGINT UNSIGNED NOT NULL,
 result ENUM('ok','blocked') NOT NULL, details_json JSON NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','0.4.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);


-- v0.4.0 API interna e reforma tributaria parametrizavel
CREATE TABLE IF NOT EXISTS api_clients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL,
 key_prefix VARCHAR(24) NOT NULL,
 key_hash CHAR(64) NOT NULL UNIQUE,
 allowed_models VARCHAR(120) NOT NULL DEFAULT 'NFE,NFCE,NFSE,CTE,MDFE',
 enabled TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 last_used_at TIMESTAMP NULL DEFAULT NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id), INDEX(company_id,enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS api_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 company_id BIGINT UNSIGNED NOT NULL,
 idempotency_key VARCHAR(128) NOT NULL,
 request_hash CHAR(64) NOT NULL,
 fiscal_document_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_client_idempotency(client_id,idempotency_key),
 FOREIGN KEY(client_id) REFERENCES api_clients(id),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tax_reform_settings (
 company_id BIGINT UNSIGNED NOT NULL,
 doc_type VARCHAR(10) NOT NULL,
 mode ENUM('desativado','simulacao','producao') NOT NULL DEFAULT 'desativado',
 valid_from DATE NULL,
 valid_until DATE NULL,
 layout_version VARCHAR(35) NULL,
 changed_by BIGINT UNSIGNED NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(company_id,doc_type),FOREIGN KEY(company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tax_reform_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 doc_type VARCHAR(10) NOT NULL,
 cst_ibs_cbs CHAR(3) NOT NULL,
 class_trib CHAR(6) NOT NULL,
 valid_from DATE NOT NULL,
 valid_until DATE NULL,
 ibs_uf_rate DECIMAL(9,5) NOT NULL DEFAULT 0,
 ibs_mun_rate DECIMAL(9,5) NOT NULL DEFAULT 0,
 cbs_rate DECIMAL(9,5) NOT NULL DEFAULT 0,
 reduction_percent DECIMAL(9,5) NOT NULL DEFAULT 0,
 legal_reference VARCHAR(255) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id),INDEX(company_id,doc_type,valid_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','0.4.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);

-- v0.5.0 Central fiscal compartilhada / API de integracoes
CREATE TABLE IF NOT EXISTS fiscal_integrations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 api_client_id BIGINT UNSIGNED NOT NULL UNIQUE,
 source_system VARCHAR(60) NOT NULL,
 source_instance VARCHAR(90) NOT NULL DEFAULT 'default',
 callback_url VARCHAR(500) NULL,
 callback_secret_hash CHAR(64) NULL,
 callback_enabled TINYINT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(api_client_id) REFERENCES api_clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_origin_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 api_client_id BIGINT UNSIGNED NOT NULL,
 fiscal_document_id BIGINT UNSIGNED NOT NULL,
 source_system VARCHAR(60) NOT NULL,
 source_instance VARCHAR(90) NOT NULL,
 source_reference VARCHAR(128) NOT NULL,
 payload_sha256 CHAR(64) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_origin (api_client_id,source_system,source_instance,source_reference),
 UNIQUE KEY uq_document (fiscal_document_id),
 INDEX idx_empresa_origem (company_id,source_system,source_reference),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(api_client_id) REFERENCES api_clients(id),
 FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_event_outbox (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 api_client_id BIGINT UNSIGNED NOT NULL,
 fiscal_document_id BIGINT UNSIGNED NOT NULL,
 event_type VARCHAR(80) NOT NULL,
 event_payload_json JSON NOT NULL,
 delivery_status ENUM('pending','disabled','delivered','failed') NOT NULL DEFAULT 'disabled',
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 delivered_at TIMESTAMP NULL,
 INDEX idx_integration_events (api_client_id,id),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(api_client_id) REFERENCES api_clients(id),
 FOREIGN KEY(fiscal_document_id) REFERENCES fiscal_documents(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fiscal_legal_minimums (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 doc_type VARCHAR(10) NOT NULL,
 taxpayer_profile VARCHAR(40) NOT NULL DEFAULT 'general',
 obligation_code VARCHAR(80) NOT NULL,
 effective_from DATE NOT NULL,
 reference_url VARCHAR(500) NOT NULL,
 enabled TINYINT NOT NULL DEFAULT 1,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_legal_obligation (doc_type,taxpayer_profile,obligation_code,effective_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','0.6.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);

-- v0.8.0: controle de licenca / mensalidade por empresa. Historico permanente.
ALTER TABLE users MODIFY role ENUM('admin','operator','support') NOT NULL DEFAULT 'operator';
CREATE TABLE IF NOT EXISTS company_billing_control (
 company_id BIGINT UNSIGNED PRIMARY KEY, blocked TINYINT NOT NULL DEFAULT 0,
 reason VARCHAR(500) NULL, due_date DATE NULL,changed_by BIGINT UNSIGNED NULL,
 changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS company_billing_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,
 action ENUM('block','release') NOT NULL,reason VARCHAR(500) NULL,
 changed_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(company_id,created_at),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','0.9.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);


-- v0.9.0: cobranças por competência, controle de tolerância e pagamentos verificados
CREATE TABLE IF NOT EXISTS billing_subscriptions (
 company_id BIGINT UNSIGNED PRIMARY KEY,
 monthly_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 grace_days INT UNSIGNED NOT NULL DEFAULT 5,
 auto_block TINYINT NOT NULL DEFAULT 0,
 active TINYINT NOT NULL DEFAULT 1,
 updated_by BIGINT UNSIGNED NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS billing_invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 reference_month CHAR(7) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 due_date DATE NOT NULL,
 status ENUM('open','paid','void') NOT NULL DEFAULT 'open',
 paid_at DATETIME NULL,
 payment_reference VARCHAR(120) NULL,
 verified_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_billing_month(company_id,reference_month),
 INDEX idx_billing_due(status,due_date),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(verified_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS billing_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NULL,
 action VARCHAR(50) NOT NULL,
 details_json JSON NOT NULL,
 performed_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_billing_action(company_id,created_at),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(invoice_id) REFERENCES billing_invoices(id),
 FOREIGN KEY(performed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','0.9.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);

-- Atualização exclusiva 0.9.0 -> 1.0.0. Sem alteração estrutural de tabelas.
-- A governança dos cadastros foi transferida da administração da empresa para o suporte oculto.
INSERT INTO app_meta(meta_key,meta_value) VALUES ('app_version','1.0.0'),('last_database_update','2026-10-12') ON DUPLICATE KEY UPDATE meta_value=VALUES(meta_value);