CREATE TABLE invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_id BIGINT UNSIGNED NOT NULL,branch_id BIGINT UNSIGNED NOT NULL,sale_id BIGINT UNSIGNED NOT NULL,customer_id BIGINT UNSIGNED NULL,user_id BIGINT UNSIGNED NOT NULL,status ENUM('draft','pending','stamped','cancel_pending','cancelled','error') NOT NULL DEFAULT 'draft',cfdi_version VARCHAR(10) NOT NULL DEFAULT '4.0',series VARCHAR(25) NULL,folio VARCHAR(40) NULL,uuid CHAR(36) NULL,rfc VARCHAR(13) NOT NULL,tax_name VARCHAR(255) NOT NULL,tax_zip VARCHAR(10) NOT NULL,tax_regime VARCHAR(10) NOT NULL,cfdi_use VARCHAR(10) NOT NULL,currency CHAR(3) NOT NULL DEFAULT 'MXN',subtotal DECIMAL(15,2) NOT NULL,total DECIMAL(15,2) NOT NULL,pac_provider VARCHAR(80) NULL,pac_reference VARCHAR(190) NULL,xml_path VARCHAR(500) NULL,pdf_path VARCHAR(500) NULL,error_message TEXT NULL,stamped_at DATETIME NULL,cancelled_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_invoice_sale(sale_id),UNIQUE KEY uq_invoice_uuid(uuid),KEY idx_invoice_business(business_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE invoice_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,invoice_id BIGINT UNSIGNED NOT NULL,sale_item_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,description VARCHAR(255) NOT NULL,sat_product_key VARCHAR(20) NOT NULL,sat_unit_key VARCHAR(10) NOT NULL,tax_object VARCHAR(10) NOT NULL,quantity DECIMAL(18,6) NOT NULL,unit_price DECIMAL(15,6) NOT NULL,discount DECIMAL(15,2) NOT NULL DEFAULT 0,tax DECIMAL(15,2) NOT NULL DEFAULT 0,line_total DECIMAL(15,2) NOT NULL,KEY idx_invoice_items(invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO permissions(code,name,module) VALUES('invoices.view','Ver facturas','invoicing'),('invoices.create','Facturar ventas','invoicing'),('invoices.cancel','Cancelar CFDI','invoicing'),('invoices.configure','Configurar facturación','invoicing') ON DUPLICATE KEY UPDATE name=VALUES(name);