SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS companies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 cnpj_root CHAR(8) NOT NULL UNIQUE,
 corporate_name VARCHAR(255) NOT NULL,
 legal_nature VARCHAR(20) NULL,
 responsible_qualification VARCHAR(20) NULL,
 responsible_entity VARCHAR(100) NULL,
 share_capital DECIMAL(18,2) NULL,
 company_size VARCHAR(20) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL,
 KEY idx_company_name (corporate_name), KEY idx_company_size (company_size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS establishments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 cnpj CHAR(14) NOT NULL UNIQUE,
 branch_order CHAR(4) NULL, check_digits CHAR(2) NULL,
 head_office TINYINT(1) NOT NULL DEFAULT 0,
 trade_name VARCHAR(255) NULL,
 registration_status VARCHAR(20) NULL,
 registration_date DATE NULL,
 status_reason VARCHAR(20) NULL,
 foreign_city VARCHAR(100) NULL,
 country_code VARCHAR(10) NULL,
 start_activity_date DATE NULL,
 cnae_main VARCHAR(10) NULL,
 street_type VARCHAR(40) NULL, street VARCHAR(255) NULL, number VARCHAR(40) NULL, complement VARCHAR(255) NULL,
 district VARCHAR(120) NULL, zip_code VARCHAR(12) NULL, state CHAR(2) NULL, city_code VARCHAR(20) NULL, city VARCHAR(160) NULL,
 ddd1 VARCHAR(4) NULL, phone1 VARCHAR(40) NULL, ddd2 VARCHAR(4) NULL, phone2 VARCHAR(40) NULL,
 fax_ddd VARCHAR(4) NULL, fax VARCHAR(40) NULL, email VARCHAR(190) NULL, website VARCHAR(255) NULL,
 special_status VARCHAR(100) NULL, special_status_date DATE NULL,
 latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL,
 KEY idx_est_company(company_id), KEY idx_est_state_city(state,city), KEY idx_est_cnae(cnae_main), KEY idx_est_status(registration_status), KEY idx_est_trade(trade_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cnaes (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS company_activities (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, establishment_id BIGINT UNSIGNED NOT NULL, cnae_code VARCHAR(10) NOT NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, UNIQUE KEY uq_est_cnae(establishment_id,cnae_code), KEY idx_act_cnae(cnae_code)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS company_partners (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, partner_type VARCHAR(10) NULL, name VARCHAR(255) NULL, document VARCHAR(30) NULL, qualification VARCHAR(20) NULL, joined_at DATE NULL, country_code VARCHAR(10) NULL, legal_representative_document VARCHAR(30) NULL, legal_representative_name VARCHAR(255) NULL, legal_representative_qualification VARCHAR(20) NULL, age_range VARCHAR(20) NULL, source_fingerprint CHAR(64) NULL, UNIQUE KEY uq_partner_fingerprint(source_fingerprint), KEY idx_partner_company(company_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS receita_datasets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference_period CHAR(7) NOT NULL, source_url VARCHAR(500) NULL, status VARCHAR(30) NOT NULL DEFAULT 'registered', discovered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, downloaded_at DATETIME NULL, imported_at DATETIME NULL, notes TEXT NULL, UNIQUE KEY uq_receita_period(reference_period), KEY idx_receita_dataset_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS receita_dataset_files (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dataset_id BIGINT UNSIGNED NOT NULL, file_name VARCHAR(255) NOT NULL, file_kind VARCHAR(40) NOT NULL, source_url VARCHAR(700) NULL, local_path VARCHAR(700) NULL, archive_path VARCHAR(700) NULL, file_size BIGINT UNSIGNED NULL, sha256 CHAR(64) NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', rows_read BIGINT UNSIGNED NOT NULL DEFAULT 0, rows_written BIGINT UNSIGNED NOT NULL DEFAULT 0, rows_failed BIGINT UNSIGNED NOT NULL DEFAULT 0, byte_offset BIGINT UNSIGNED NOT NULL DEFAULT 0, last_error TEXT NULL, started_at DATETIME NULL, finished_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, UNIQUE KEY uq_dataset_filename(dataset_id,file_name), KEY idx_dataset_file_kind(file_kind), KEY idx_dataset_file_status(status), KEY idx_dataset_file_dataset(dataset_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS company_tax_regimes (
 company_id BIGINT UNSIGNED PRIMARY KEY, simples_optant TINYINT(1) NOT NULL DEFAULT 0, simples_start_date DATE NULL, simples_end_date DATE NULL, mei_optant TINYINT(1) NOT NULL DEFAULT 0, mei_start_date DATE NULL, mei_end_date DATE NULL, source_code VARCHAR(60) NOT NULL DEFAULT 'receita_federal', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_tax_simples(simples_optant), KEY idx_tax_mei(mei_optant)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS legal_natures (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS partner_qualifications (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS countries (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS municipalities (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL, state CHAR(2) NULL, KEY idx_municipality_name(description)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS registration_status_reasons (code VARCHAR(10) PRIMARY KEY, description VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS data_sources (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, source_type VARCHAR(40) NOT NULL, trust_weight TINYINT UNSIGNED NOT NULL DEFAULT 70, is_active TINYINT(1) NOT NULL DEFAULT 1, last_sync_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS company_facts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, establishment_id BIGINT UNSIGNED NULL, field_name VARCHAR(80) NOT NULL, field_value TEXT NOT NULL, normalized_value VARCHAR(500) NULL, source_code VARCHAR(60) NOT NULL, confidence TINYINT UNSIGNED NOT NULL DEFAULT 50, is_public TINYINT(1) NOT NULL DEFAULT 1, is_current TINYINT(1) NOT NULL DEFAULT 1, first_seen_at DATETIME NOT NULL, last_seen_at DATETIME NOT NULL, KEY idx_fact_company(company_id), KEY idx_fact_field(field_name), KEY idx_fact_source(source_code), KEY idx_fact_current(is_current)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS api_clients (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, tenant_ref VARCHAR(160) NULL, token_hash CHAR(64) NOT NULL UNIQUE, plan VARCHAR(50) NOT NULL DEFAULT 'standard', monthly_limit INT UNSIGNED NOT NULL DEFAULT 1000, used_current_period INT UNSIGNED NOT NULL DEFAULT 0, usage_period CHAR(7) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, last_used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_client_tenant(tenant_ref)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS search_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, api_client_id INT UNSIGNED NOT NULL, filters_json JSON NULL, result_count INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_search_client(api_client_id), KEY idx_search_created(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tenant_signals (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, api_client_id INT UNSIGNED NOT NULL, company_id BIGINT UNSIGNED NULL, cnpj CHAR(14) NULL, field_name VARCHAR(80) NOT NULL, normalized_hash CHAR(64) NOT NULL, source_recency DATE NULL, confidence_hint TINYINT UNSIGNED NOT NULL DEFAULT 30, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_signal_company(company_id), KEY idx_signal_cnpj(cnpj), KEY idx_signal_field(field_name), KEY idx_signal_hash(normalized_hash)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS import_jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, source_code VARCHAR(60) NOT NULL, job_type VARCHAR(60) NOT NULL, file_name VARCHAR(255) NULL, dataset_file_id BIGINT UNSIGNED NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', rows_read BIGINT UNSIGNED NOT NULL DEFAULT 0, rows_written BIGINT UNSIGNED NOT NULL DEFAULT 0, rows_failed BIGINT UNSIGNED NOT NULL DEFAULT 0, byte_offset BIGINT UNSIGNED NOT NULL DEFAULT 0, started_at DATETIME NULL, heartbeat_at DATETIME NULL, checkpoint_json JSON NULL, finished_at DATETIME NULL, error_message TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_job_status(status), KEY idx_job_source(source_code), KEY idx_job_dataset_file(dataset_file_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO data_sources(code,name,source_type,trust_weight) VALUES ('receita_federal','Receita Federal','official',100),('tenant_network','Rede Starten','network_signal',60),('google_places','Google Places','public_enrichment',80),('official_website','Site oficial','public_enrichment',95);