-- Base v0.1.0: atualizações futuras serão acrescentadas em blocos versionados.
-- Não execute DROP TABLE ou alterações destrutivas em bancos de produção.
INSERT IGNORE INTO schema_migrations(version) VALUES('0.1.0');

-- v0.2.0: novas tabelas aditivas, sem exclusão das tabelas existentes.
CREATE TABLE IF NOT EXISTS digesters (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL,capacity_m3 DECIMAL(15,3) NOT NULL,
 status ENUM('planning','operating','paused','decommissioned') NOT NULL DEFAULT 'planning',
 created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 FOREIGN KEY(created_by) REFERENCES users(id), CHECK(capacity_m3>0),INDEX(tenant_id,plant_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS feeding_recipes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL,material VARCHAR(160) NOT NULL,share_percent DECIMAL(6,3) NOT NULL,
 notes TEXT,status ENUM('draft','under_review','approved','archived') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),CHECK(share_percent>0 AND share_percent<=100),INDEX(tenant_id,plant_id)
) ENGINE=InnoDB;
INSERT IGNORE INTO schema_migrations(version) VALUES('0.2.0');


-- v0.3.0: estruturas aditivas de silos e estoque; execução sob MySQL 8.
CREATE TABLE IF NOT EXISTS silos (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,
 plant_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL,
 capacity_kg DECIMAL(16,3) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_silo_identity (id,tenant_id,plant_id),
 UNIQUE KEY uq_silo_name (tenant_id,plant_id,name),
 FOREIGN KEY (plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 CHECK (capacity_kg>0)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS stock_movements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,
 plant_id BIGINT UNSIGNED NOT NULL,
 silo_id BIGINT UNSIGNED NOT NULL,
 material VARCHAR(160) NOT NULL,
 movement_type ENUM('receipt','consumption') NOT NULL,
 mass_kg DECIMAL(16,3) NOT NULL,
 receipt_id BIGINT UNSIGNED NULL,
 reference_code VARCHAR(90) NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_movement_receipt (receipt_id),
 INDEX idx_stock_balance (tenant_id,plant_id,silo_id,material),
 FOREIGN KEY (silo_id,tenant_id,plant_id) REFERENCES silos(id,tenant_id,plant_id),
 FOREIGN KEY (receipt_id) REFERENCES feedstock_receipts(id),
 FOREIGN KEY (created_by) REFERENCES users(id),
 CHECK(mass_kg>0)
) ENGINE=InnoDB;
INSERT IGNORE INTO schema_migrations(version) VALUES('0.3.0');


-- v0.4-v1.0: módulos internos para homologação (sem fiscal/ANP/SCADA oficial)
CREATE TABLE IF NOT EXISTS `digestate_records` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `volume_m3` DECIMAL(17,4) NOT NULL, `destination` VARCHAR(255) NOT NULL, `document_ref` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_digestate_records_scope(id,tenant_id), INDEX ix_digestate_records_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `industrial_costs` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `category` VARCHAR(255) NOT NULL, `amount_brl` DECIMAL(17,4) NOT NULL, `reference_month` DATE NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_industrial_costs_scope(id,tenant_id), INDEX ix_industrial_costs_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `commercial_settlements` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `counterparty` VARCHAR(255) NOT NULL, `reference` VARCHAR(255) NOT NULL, `amount_brl` DECIMAL(17,4) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_commercial_settlements_scope(id,tenant_id), INDEX ix_commercial_settlements_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `billing_documents` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `customer` VARCHAR(255) NOT NULL, `description` VARCHAR(255) NOT NULL, `amount_brl` DECIMAL(17,4) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_billing_documents_scope(id,tenant_id), INDEX ix_billing_documents_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `energy_records` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `kwh_consumed` DECIMAL(17,4) NOT NULL, `kwh_generated` DECIMAL(17,4) NOT NULL, `period_date` DATE NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_energy_records_scope(id,tenant_id), INDEX ix_energy_records_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `industrial_instruments` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `tag` VARCHAR(255) NOT NULL, `instrument_type` VARCHAR(255) NOT NULL, `calibration_due` DATE NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_industrial_instruments_scope(id,tenant_id), INDEX ix_industrial_instruments_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `sustainability_records` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `indicator` VARCHAR(255) NOT NULL, `metric_value` DECIMAL(17,4) NOT NULL, `methodology_ref` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_sustainability_records_scope(id,tenant_id), INDEX ix_sustainability_records_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `cgob_prechecks` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `period_ref` DATE NOT NULL, `check_name` VARCHAR(255) NOT NULL, `evidence_ref` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_cgob_prechecks_scope(id,tenant_id), INDEX ix_cgob_prechecks_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `plant_logbook` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `shift_ref` VARCHAR(255) NOT NULL, `event_type` VARCHAR(255) NOT NULL, `description` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_plant_logbook_scope(id,tenant_id), INDEX ix_plant_logbook_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `technical_rounds` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `asset_tag` VARCHAR(255) NOT NULL, `checklist` VARCHAR(255) NOT NULL, `observation` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_technical_rounds_scope(id,tenant_id), INDEX ix_technical_rounds_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `supplier_appointments` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `supplier` VARCHAR(255) NOT NULL, `appointment_at` DATETIME NOT NULL, `material` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_supplier_appointments_scope(id,tenant_id), INDEX ix_supplier_appointments_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `maintenance_events` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `asset_tag` VARCHAR(255) NOT NULL, `operating_hours` DECIMAL(17,4) NOT NULL, `service_description` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_maintenance_events_scope(id,tenant_id), INDEX ix_maintenance_events_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `incident_records` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `severity` VARCHAR(255) NOT NULL, `source_tag` VARCHAR(255) NOT NULL, `description` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_incident_records_scope(id,tenant_id), INDEX ix_incident_records_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `integration_intents` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, `system_name` VARCHAR(255) NOT NULL, `protocol_name` VARCHAR(255) NOT NULL, `notes` VARCHAR(255) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_integration_intents_scope(id,tenant_id), INDEX ix_integration_intents_scope(tenant_id,plant_id,created_at), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;

-- v1.0.2: títulos e baixas manuais. Apenas contas internas, sem gateway, sem NF-e.
CREATE TABLE IF NOT EXISTS billing_receivables (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 customer VARCHAR(180) NOT NULL,reference VARCHAR(100),due_date DATE NOT NULL,
 amount_brl DECIMAL(15,2) NOT NULL,created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_receivable_scope(id,tenant_id,plant_id),
 KEY ix_receivable_due(tenant_id,plant_id,due_date),
 FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),CHECK(amount_brl>0)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS billing_payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 receivable_id BIGINT UNSIGNED NOT NULL,payment_date DATE NOT NULL,
 amount_brl DECIMAL(15,2) NOT NULL,
 payment_method ENUM('pix','bank','cash','card','other') NOT NULL,
 reference VARCHAR(120),created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 KEY ix_payment_scope(tenant_id,plant_id,receivable_id),
 FOREIGN KEY(receivable_id,tenant_id,plant_id) REFERENCES billing_receivables(id,tenant_id,plant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),CHECK(amount_brl>0)
) ENGINE=InnoDB;
INSERT IGNORE INTO schema_migrations(version) VALUES('1.0.2');


-- v1.0.3: contas a pagar e pagamentos manuais, sem integração bancária.
CREATE TABLE IF NOT EXISTS billing_payables (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 supplier VARCHAR(180) NOT NULL,reference VARCHAR(100),due_date DATE NOT NULL,
 amount_brl DECIMAL(15,2) NOT NULL,created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_payable_scope(id,tenant_id,plant_id),
 KEY ix_payable_due(tenant_id,plant_id,due_date),
 FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),CHECK(amount_brl>0)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS billing_disbursements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 payable_id BIGINT UNSIGNED NOT NULL,disbursement_date DATE NOT NULL,
 amount_brl DECIMAL(15,2) NOT NULL,
 disbursement_method ENUM('pix','bank','cash','card','other') NOT NULL,
 reference VARCHAR(120),created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 KEY ix_disbursement_scope(tenant_id,plant_id,payable_id),
 FOREIGN KEY(payable_id,tenant_id,plant_id) REFERENCES billing_payables(id,tenant_id,plant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),CHECK(amount_brl>0)
) ENGINE=InnoDB;
INSERT IGNORE INTO schema_migrations(version) VALUES('1.0.3');


-- v1.0.4: contratos comerciais e geração manual por competência, sem gateway/nota fiscal.
CREATE TABLE IF NOT EXISTS commercial_contracts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 customer VARCHAR(180) NOT NULL,reference VARCHAR(100),monthly_amount DECIMAL(15,2) NOT NULL,
 start_date DATE NOT NULL,end_date DATE NULL,due_day TINYINT UNSIGNED NOT NULL,
 status ENUM('active','paused','ended') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_contract_scope(id,tenant_id,plant_id),
 KEY ix_contract_tenant(tenant_id,plant_id,status),
 FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id),
 FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK(monthly_amount>0),CHECK(due_day BETWEEN 1 AND 28)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS contract_billings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,plant_id BIGINT UNSIGNED NOT NULL,
 contract_id BIGINT UNSIGNED NOT NULL,receivable_id BIGINT UNSIGNED NULL,
 competence DATE NOT NULL,due_date DATE NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_contract_competence(tenant_id,contract_id,competence),
 FOREIGN KEY(contract_id,tenant_id,plant_id) REFERENCES commercial_contracts(id,tenant_id,plant_id),
 FOREIGN KEY(receivable_id,tenant_id,plant_id) REFERENCES billing_receivables(id,tenant_id,plant_id),
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB;
INSERT IGNORE INTO schema_migrations(version) VALUES('1.0.4');

-- v1.0.5: utiliza tabelas plans/subscriptions existentes; sem alteração de esquema.
INSERT IGNORE INTO schema_migrations(version) VALUES('1.0.5');