CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(30) PRIMARY KEY, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS plans (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(50) UNIQUE NOT NULL, title VARCHAR(100) NOT NULL, price_cents INT UNSIGNED NULL, max_plants INT UNSIGNED NULL, max_users INT UNSIGNED NULL, description VARCHAR(255), active TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB;
INSERT INTO plans(slug,title,price_cents,max_plants,max_users,description) VALUES
('start','Biogás Start',49900,1,3,'Operação de uma usina e lançamentos manuais'),('pro','Biometano Pro',149900,3,10,'Qualidade, custos e dossiê CGOB'),('industrial','Industrial',299900,10,30,'Gestão multiusinas e integração planejada'),('enterprise','Enterprise',NULL,NULL,NULL,'Projeto e contratação sob proposta')
ON DUPLICATE KEY UPDATE title=VALUES(title),description=VALUES(description);
CREATE TABLE IF NOT EXISTS tenants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, tax_id VARCHAR(14) NOT NULL UNIQUE, email VARCHAR(180) NOT NULL, status ENUM('trial','active','suspended') NOT NULL DEFAULT 'trial', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS subscriptions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plan_id INT UNSIGNED NOT NULL, state ENUM('trial','pending','active','past_due','suspended','cancelled') NOT NULL DEFAULT 'trial', trial_ends_at DATETIME NOT NULL, started_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(plan_id) REFERENCES plans(id), INDEX(tenant_id,state)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NULL, name VARCHAR(140) NOT NULL, email VARCHAR(180) NOT NULL UNIQUE, pass_hash VARCHAR(255) NOT NULL, role ENUM('admin','manager','operator','support') NOT NULL DEFAULT 'operator', active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(tenant_id), FOREIGN KEY(tenant_id) REFERENCES tenants(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS plants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, state_code CHAR(2) NOT NULL, city VARCHAR(120) NOT NULL, kind ENUM('biogas','biomethane','both') NOT NULL DEFAULT 'biogas', status ENUM('planning','operating','paused') NOT NULL DEFAULT 'planning', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_plant_tenant(id,tenant_id), INDEX(tenant_id), FOREIGN KEY(tenant_id) REFERENCES tenants(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS plant_users (tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (plant_id,user_id), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS feedstock_receipts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, received_at DATETIME NOT NULL, material VARCHAR(160) NOT NULL, origin VARCHAR(180) NOT NULL, mass_kg DECIMAL(15,3) NOT NULL, reference_code VARCHAR(90), 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(mass_kg > 0), INDEX(tenant_id,received_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS meters (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, tag VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, calibration_due DATE NULL, UNIQUE KEY uq_meter(tenant_id,tag), FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS production_readings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, meter_id BIGINT UNSIGNED NULL, measured_at DATETIME NOT NULL, product ENUM('biogas','biomethane') NOT NULL, volume_m3 DECIMAL(17,4) NOT NULL, volume_basis ENUM('actual_m3','normal_m3','standard_m3') NOT NULL, source_type ENUM('manual','csv','integration') NOT NULL DEFAULT 'manual', source_ref VARCHAR(190) NULL, quality_status ENUM('unverified','verified','rejected') NOT NULL DEFAULT 'unverified', recorded_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(meter_id) REFERENCES meters(id), FOREIGN KEY(recorded_by) REFERENCES users(id), CHECK(volume_m3 >= 0), INDEX(tenant_id,measured_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS gas_analyses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, sampled_at DATETIME NOT NULL, methane_pct DECIMAL(6,3), co2_pct DECIMAL(6,3), h2s_ppm DECIMAL(12,3), lab_name VARCHAR(160), document_ref VARCHAR(190), approval ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', created_by BIGINT UNSIGNED NOT NULL, FOREIGN KEY(plant_id,tenant_id) REFERENCES plants(id,tenant_id), FOREIGN KEY(created_by) REFERENCES users(id), CHECK(methane_pct BETWEEN 0 AND 100), CHECK(co2_pct BETWEEN 0 AND 100)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cgob_evidence (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plant_id BIGINT UNSIGNED NOT NULL, period_start DATE NOT NULL, period_end DATE NOT NULL, evidence_type VARCHAR(80) NOT NULL, document_ref VARCHAR(190) NOT NULL, notes TEXT, status ENUM('draft','under_review','validated','rejected') 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(period_end >= period_start)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(90) NOT NULL, entity_type VARCHAR(80), entity_id BIGINT UNSIGNED NULL, occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ip_hash CHAR(64), metadata_json JSON NULL, INDEX(tenant_id,occurred_at)) ENGINE=InnoDB;
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');