CREATE TABLE IF NOT EXISTS invoice_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NOT NULL,
 description VARCHAR(255) NOT NULL,
 quantity DECIMAL(12,2) DEFAULT 1,
 unit_price DECIMAL(14,2) DEFAULT 0,
 tax_rate DECIMAL(6,2) DEFAULT 0,
 line_total DECIMAL(14,2) DEFAULT 0,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
 INDEX idx_invoice_items(company_id,invoice_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS client_statements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 period_start DATE NOT NULL,
 period_end DATE NOT NULL,
 generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 generated_by BIGINT UNSIGNED NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payroll_runs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 payroll_period_id BIGINT UNSIGNED NOT NULL,
 processed_at DATETIME NULL,
 processed_by BIGINT UNSIGNED NULL,
 total_gross DECIMAL(14,2) DEFAULT 0,
 total_deductions DECIMAL(14,2) DEFAULT 0,
 total_net DECIMAL(14,2) DEFAULT 0,
 status ENUM('draft','processed','approved','paid','closed') DEFAULT 'draft',
 FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
 FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO permissions(code,description) VALUES
('billing.manage','Create and manage invoices and payments'),
('billing.payments','Record client payments'),
('billing.statements','View client statements'),
('payroll.manage','Process payroll'),
('payroll.approve','Approve payroll');

INSERT IGNORE INTO role_permissions(role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.is_system=1 AND p.code IN ('billing.manage','billing.payments','billing.statements','payroll.manage','payroll.approve');