below is my database ===Database tax_assist_1sterp

== Table structure for table accounts

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|account_group_id|int(11)|No|
|account_name|varchar(255)|No|
|**account_code**|varchar(50)|Yes|NULL
|description|text|Yes|NULL
|opening_balance|decimal(15,2)|Yes|0.00
|opening_balance_date|date|Yes|NULL
|is_active|tinyint(1)|Yes|1
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table accounts

|1|1|1|Example Account|EX123|This is a sample account|0.00|2025-04-01|1|2025-04-03 11:56:53|2025-04-03 11:56:53
|2|2|11|Cash in Hand|101|Cash held in the office|10000.00|2023-04-01|1|2025-04-03 12:00:35|2025-04-03 12:00:35
|3|2|11|HDFC Bank Account|102|Current account with HDFC Bank|50000.00|2023-04-01|1|2025-04-03 12:00:35|2025-04-03 12:00:35
|4|2|18|Sales Revenue|401|Revenue from sales|0.00|2023-04-01|1|2025-04-03 12:00:35|2025-04-03 12:00:35
|5|2|20|Purchase of Raw Materials|501|Cost of raw materials|0.00|2023-04-01|1|2025-04-03 12:00:35|2025-04-03 12:00:35
|8|1|21|Water Charges|001| |0.00|2025-04-01|1|2025-04-03 12:13:04|2025-04-03 12:13:04
|11|1|21|Audit Charges|EXP001| |0.00|2025-04-01|1|2025-04-03 12:15:30|2025-04-03 12:15:30
|12|1|1|Equipments|A001| |15000.00|2025-04-01|1|2025-04-03 12:16:29|2025-04-03 12:16:29
|13|1|11|Cash Account|CA001| |25000.00|2025-04-01|1|2025-04-03 12:24:03|2025-04-03 12:24:03
|16|1|5|Anand Current A/c|6445| |145010.00|2025-04-01|1|2025-04-03 12:47:17|2025-04-03 12:47:17
|17|1|10|Investments |IN-001| |0.00|2025-04-03|1|2025-04-03 15:12:34|2025-04-03 15:12:34
|18|1|10|Deposits|Dep001| |45000.00|2025-04-04|1|2025-04-04 16:26:27|2025-04-04 16:26:27
|19|1|10|Other assests|other| |26567.00|2025-04-04|1|2025-04-04 16:26:53|2025-04-04 16:26:53
== Table structure for table account_groups

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|group_name|varchar(255)|No|
|parent_id|int(11)|Yes|NULL
|description|text|Yes|NULL
== Dumping data for table account_groups

|1|Assets|NULL|All assets of the company
|2|Liabilities|NULL|All liabilities of the company
|3|Income|NULL|All income accounts
|4|Expenses|NULL|All expense accounts
|5|Capital|NULL|Capital accounts
|9|Assets|NULL|All assets of the company
|10|Current Assets|1|Assets that can be converted to cash within one year
|11|Cash and Bank Balances|1|Cash in hand and bank accounts
|13|Current Liabilities|2|Liabilities due within one year
|14|Duties and Taxes|2|Taxes payable
|15|Equity|5|Owner&#039;s equity
|17|Revenue|3|Income generated from sales and services
|18|Sales|3|Revenue from sales of goods
|20|Direct Expenses|4|Expenses directly related to production
|21|Indirect Expenses|4|Expenses not directly related to production
|23|Suspense|NULL| 
== Table structure for table activity_logs

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|user_id|int(11)|No|
|module|varchar(50)|No|
|action|varchar(50)|No|
|description|text|Yes|NULL
|ip_address|varchar(45)|Yes|NULL
|created_at|timestamp|No|current_timestamp()
== Dumping data for table activity_logs

|1|1|1|invoice|delete|Deleted sales invoice #S20250403083151933|::1|2025-04-03 15:04:22
|2|1|1|invoice|delete|Deleted sales invoice #S20250403112939509|::1|2025-04-03 15:04:25
|3|1|1|invoice|delete|Deleted purchase invoice #P20250403083112641|::1|2025-04-03 19:18:44
|4|1|1|invoice|delete|Deleted sales invoice #S20250403113519284|::1|2025-04-03 20:44:26
|5|1|1|invoice|delete|Deleted sales invoice #S20250403171406480|::1|2025-04-03 20:45:51
|6|1|1|invoice|delete|Deleted sales invoice #S20250403171440657|::1|2025-04-03 20:45:54
|7|1|1|invoice|delete|Deleted purchase invoice #P20250403170431644|::1|2025-04-03 20:46:11
|8|1|1|invoice|delete|Deleted purchase invoice #P20250403170414665|::1|2025-04-03 20:46:13
|9|1|1|invoice|delete|Deleted sales invoice #S20250403171534762|::1|2025-04-04 09:38:10
|10|1|1|invoice|delete|Deleted sales invoice #S20250404060759636|::1|2025-04-04 17:47:53
== Table structure for table bank_accounts

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|account_id|int(11)|No|
|bank_name|varchar(100)|No|
|account_number|varchar(50)|No|
|ifsc_code|varchar(20)|Yes|NULL
|branch|varchar(100)|Yes|NULL
|is_active|tinyint(1)|Yes|1
|created_at|timestamp|No|current_timestamp()
== Dumping data for table bank_accounts

== Table structure for table bank_reconciliation

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|bank_account_id|int(11)|No|
|voucher_id|int(11)|No|
|reconciliation_date|date|Yes|NULL
|is_reconciled|tinyint(1)|Yes|0
|notes|text|Yes|NULL
|created_at|timestamp|No|current_timestamp()
== Dumping data for table bank_reconciliation

== Table structure for table companies

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|user_id|int(11)|No|
|company_name|varchar(255)|No|
|**gstin**|varchar(15)|Yes|NULL
|**pan**|varchar(10)|Yes|NULL
|**tan**|varchar(10)|Yes|NULL
|**cin**|varchar(21)|Yes|NULL
|address|text|Yes|NULL
|city|varchar(255)|Yes|NULL
|state|varchar(255)|Yes|NULL
|pincode|varchar(10)|Yes|NULL
|phone|varchar(20)|Yes|NULL
|email|varchar(255)|Yes|NULL
|financial_year_start|date|Yes|NULL
|financial_year_end|date|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table companies

|1|1|A1 Tax Assist|22AAAAA0000A1Z5|AZNPA2013P| | |No 52 Kodigehalli Kodigehalli Post Bangalore North |<PRESIDIO_ANONYMIZED_EMAIL_ADDRESS>|2025-04-01|2026-03-31|2025-04-03 11:21:49|2025-04-03 11:21:49
|2|1|Acme Corp India|29AAACC1234A1Z5|ABCDE1234F|XYZ12345A|U12345MH2023PTC987654|789 Pine Ln, Anytown|Mumbai|Maharashtra|400001|022-12345678|info@acmecorp.in|2023-04-01|2024-03-31|2025-04-03 12:00:35|2025-04-03 12:00:35
|3|2|Beta Solutions Pvt Ltd|27BBBBB5678B2R7|FGHIJ5678G|UVW67890B|L67890KA2023PLC543210|101 MG Road, Bangalore|Bangalore|Karnataka|560001|080-98765432|contact@betasolutions.com|2023-04-01|2024-03-31|2025-04-03 12:00:35|2025-04-03 12:00:35
== Table structure for table customers

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|customer_name|varchar(255)|No|
|gstin|varchar(15)|Yes|NULL
|pan|varchar(10)|Yes|NULL
|address|text|Yes|NULL
|city|varchar(255)|Yes|NULL
|state|varchar(255)|Yes|NULL
|pincode|varchar(10)|Yes|NULL
|phone|varchar(20)|Yes|NULL
|email|varchar(255)|Yes|NULL
|contact_person|varchar(255)|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table customers

|1|1|Anand Kumar |29AZNPA2013P12A|AZNPA2013P|No 52 kodeigahlli |<PRESIDIO_ANONYMIZED_EMAIL_ADDRESS>|Anand Kumar|2025-04-03 11:41:10|2025-04-03 11:41:10
== Table structure for table document_templates

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|template_name|varchar(100)|No|
|template_type|enum(&#039;invoice&#039;, &#039;receipt&#039;, &#039;statement&#039;, &#039;report&#039;)|No|
|content|text|Yes|NULL
|is_default|tinyint(1)|Yes|0
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table document_templates

== Table structure for table expenses

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|expense_date|date|No|
|account_id|int(11)|No|
|description|text|Yes|NULL
|amount|decimal(15,2)|No|
|payment_mode|enum(&#039;cash&#039;, &#039;bank&#039;, &#039;online&#039;)|Yes|cash
|reference_number|varchar(255)|Yes|NULL
|voucher_id|int(11)|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table expenses

|2|1|2025-04-03|11|paid|1000.00| | |22|2025-04-03 21:28:22|2025-04-03 21:28:22
== Table structure for table financial_years

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|start_date|date|No|
|end_date|date|No|
|is_active|tinyint(1)|Yes|0
|is_locked|tinyint(1)|Yes|0
|created_at|timestamp|No|current_timestamp()
== Dumping data for table financial_years

|1|1|2024-04-01|2024-03-31|1|0|2025-04-03 20:57:52
== Table structure for table hsn_codes

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|**hsn_code**|varchar(8)|No|
|description|text|Yes|NULL
|gst_rate|decimal(5,2)|No|
== Dumping data for table hsn_codes

|1|1|7254|Steel|18.00
== Table structure for table income

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|income_date|date|No|
|account_id|int(11)|No|
|description|text|Yes|NULL
|amount|decimal(15,2)|No|
|payment_mode|enum(&#039;cash&#039;, &#039;bank&#039;, &#039;online&#039;)|Yes|cash
|reference_number|varchar(255)|Yes|NULL
|voucher_id|int(11)|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table income

== Table structure for table invoices

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|**company_id**|int(11)|No|
|**invoice_number**|varchar(50)|No|
|invoice_date|date|No|
|customer_id|int(11)|Yes|NULL
|supplier_id|int(11)|Yes|NULL
|supply_place|varchar(255)|Yes|NULL
|billing_address|text|Yes|NULL
|shipping_address|text|Yes|NULL
|invoice_type|enum(&#039;sales&#039;, &#039;purchase&#039;)|No|
|total_amount|decimal(15,2)|No|
|discount|decimal(15,2)|Yes|0.00
|tax_amount|decimal(15,2)|Yes|0.00
|grand_total|decimal(15,2)|No|
|payment_terms|varchar(255)|Yes|NULL
|due_date|date|Yes|NULL
|status|enum(&#039;draft&#039;, &#039;sent&#039;, &#039;paid&#039;, &#039;overdue&#039;, &#039;cancelled&#039;)|Yes|draft
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table invoices

|13|1|P20250403171604805|2025-04-03|NULL|2|NULL|NULL|NULL|purchase|1500.00|0.00|270.00|1770.00| |2025-04-03|sent|2025-04-03 20:46:04|2025-04-03 20:46:04
|14|1|P20250404060423213|2025-04-04|NULL|2|NULL|NULL|NULL|purchase|15000.00|0.00|2700.00|17700.00| |NULL|sent|2025-04-04 09:34:23|2025-04-04 09:34:23
|16|1|S20250404060929563|2025-04-04|1|NULL|NULL|NULL|NULL|sales|90000.00|0.00|16200.00|106200.00| |NULL|sent|2025-04-04 09:39:29|2025-04-04 09:39:29
|17|1|P20250404090534150|2025-04-04|NULL|2|NULL|NULL|NULL|purchase|1800000.00|0.00|324000.00|2124000.00| |2025-04-04|sent|2025-04-04 12:35:34|2025-04-04 12:35:34
== Table structure for table invoice_items

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|invoice_id|int(11)|No|
|item_id|int(11)|Yes|NULL
|description|text|Yes|NULL
|quantity|decimal(10,2)|No|
|rate|decimal(15,2)|No|
|discount|decimal(15,2)|Yes|0.00
|tax_rate|decimal(5,2)|Yes|0.00
|amount|decimal(15,2)|No|
== Dumping data for table invoice_items

|16|13|1|NULL|1.00|1500.00|0.00|18.00|1770.00
|17|14|1|NULL|10.00|1500.00|0.00|18.00|17700.00
|19|16|1|NULL|50.00|1800.00|0.00|18.00|106200.00
|20|17|1|NULL|1200.00|1500.00|0.00|18.00|2124000.00
== Table structure for table items

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|item_category_id|int(11)|Yes|NULL
|item_name|varchar(255)|No|
|**item_code**|varchar(50)|Yes|NULL
|description|text|Yes|NULL
|unit|varchar(50)|Yes|NULL
|purchase_price|decimal(15,2)|Yes|0.00
|sales_price|decimal(15,2)|Yes|0.00
|opening_stock|decimal(10,2)|Yes|0.00
|reorder_level|decimal(10,2)|Yes|0.00
|gst_rate|decimal(5,2)|Yes|0.00
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
|hsn_code_id|int(11)|Yes|NULL
== Dumping data for table items

|1|1|1|8mm Steel|002| |piece|1500.00|1800.00|10.00|100.00|18.00|2025-04-03 11:42:04|2025-04-03 11:42:04|1
== Table structure for table item_categories

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|category_name|varchar(255)|No|
|description|text|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table item_categories

|1|1|Syam Steel| |2025-04-03 11:38:11|2025-04-03 11:38:11
== Table structure for table roles

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|role_name|varchar(50)|No|
|description|text|Yes|NULL
|permissions|text|Yes|NULL
|created_at|timestamp|No|current_timestamp()
== Dumping data for table roles

== Table structure for table suppliers

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|supplier_name|varchar(255)|No|
|gstin|varchar(15)|Yes|NULL
|pan|varchar(10)|Yes|NULL
|address|text|Yes|NULL
|city|varchar(255)|Yes|NULL
|state|varchar(255)|Yes|NULL
|pincode|varchar(10)|Yes|NULL
|phone|varchar(20)|Yes|NULL
|email|varchar(255)|Yes|NULL
|contact_person|varchar(255)|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table suppliers

|2|1|Veena kumari|22AAAAA0000A1Z9|AAAPA1234A|Bnagalore|Bangalore|karnataka|560112|8123030275|admin@example.com|Veena love|2025-04-03 19:19:43|2025-04-03 19:22:03
|<PRESIDIO_ANONYMIZED_EMAIL_ADDRESS>|sandy|2025-04-03 19:24:05|2025-04-03 19:24:05
== Table structure for table system_settings

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|setting_key|varchar(50)|No|
|setting_value|text|Yes|NULL
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table system_settings

== Table structure for table tax_rates

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|company_id|int(11)|No|
|tax_name|varchar(255)|No|
|tax_rate|decimal(5,2)|No|
|tax_type|enum(&#039;gst&#039;, &#039;tds&#039;, &#039;other&#039;)|No|
|description|text|Yes|NULL
== Dumping data for table tax_rates

== Table structure for table users

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|**username**|varchar(255)|No|
|password|varchar(255)|No|
|**email**|varchar(255)|No|
|first_name|varchar(255)|Yes|NULL
|last_name|varchar(255)|Yes|NULL
|phone|varchar(20)|Yes|NULL
|address|text|Yes|NULL
|is_active|tinyint(1)|Yes|1
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table users

|1|testuser1|$2a$12$ezrPDKtItubstuY46ilKO.mgeIfJUeUCqzHSKFDrKG0QxrXRhyk.K|testuser1@example.com|Test|User1|123-456-7890|123 Main St, Anytown|1|2025-04-02 14:01:16|2025-04-02 14:08:53
|2|testuser2|$2y$10$EXAMPLE_HASHED_PASSWORD_2|testuser2@example.com|Test|User2|987-654-3210|456 Oak Ave, Anytown|1|2025-04-02 14:01:16|2025-04-02 14:01:16
|5|gvaanand|$2y$10$oFYLERjMWCJV0IxymHql3uPNwAzv2JNZukLCc86RjMSn7ZvgZ/BlC|ananda.k.s@icai.org|Anand Kumar|KS|8123030275|No 52 kodiegehalli|1|2025-04-02 14:40:54|2025-04-02 14:40:54
== Table structure for table user_roles

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|user_id|int(11)|No|
|role_id|int(11)|No|
|created_at|timestamp|No|current_timestamp()
== Dumping data for table user_roles

== Table structure for table vouchers

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|**company_id**|int(11)|No|
|**voucher_number**|varchar(50)|No|
|voucher_date|date|No|
|voucher_type|enum(&#039;payment&#039;, &#039;receipt&#039;, &#039;journal&#039;, &#039;contra&#039;, &#039;purchase&#039;, &#039;sales&#039;)|No|
|description|text|Yes|NULL
|total_amount|decimal(15,2)|No|
|created_at|timestamp|No|current_timestamp()
|updated_at|timestamp|No|current_timestamp()
== Dumping data for table vouchers

|4|2|V001|2023-10-26|payment|Payment to supplier|5000.00|2025-04-03 12:00:35|2025-04-03 12:00:35
|11|1|005|2025-04-03|journal| |2500.00|2025-04-03 12:40:40|2025-04-03 12:40:40
|14|1|Bill01|2025-04-03|journal| |5200.00|2025-04-03 15:11:48|2025-04-03 15:11:48
|15|1|001|2025-04-03|payment| |52000.00|2025-04-03 15:13:07|2025-04-03 15:13:07
|21|1|V2025000008|2025-04-03|purchase|Invoice #P20250403171604805|1770.00|2025-04-03 20:46:04|2025-04-03 20:46:04
|22|1|V2025000009|2025-04-03|payment|paid|1000.00|2025-04-03 21:28:22|2025-04-03 21:28:22
|23|1|V2025000010|2025-04-04|purchase|Invoice #P20250404060423213|17700.00|2025-04-04 09:34:23|2025-04-04 09:34:23
|25|1|V2025000012|2025-04-04|sales|Invoice #S20250404060929563|106200.00|2025-04-04 09:39:29|2025-04-04 09:39:29
|26|1|V2025000013|2025-04-04|purchase|Invoice #P20250404090534150|2124000.00|2025-04-04 12:35:34|2025-04-04 12:35:34
|27|1|Bill005|2025-04-04|receipt| |55000.00|2025-04-04 16:24:51|2025-04-04 16:24:51
|28|1|Cap005|2025-04-04|payment| |25000.00|2025-04-04 16:28:17|2025-04-04 16:28:17
== Table structure for table voucher_entries

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|voucher_id|int(11)|No|
|account_id|int(11)|No|
|debit|decimal(15,2)|Yes|0.00
|credit|decimal(15,2)|Yes|0.00
|description|text|Yes|NULL
== Dumping data for table voucher_entries

|4|4|5|5000.00|0.00|Debit to Purchase Account
|5|4|2|0.00|5000.00|Credit to Cash Account
|16|11|11|2500.00|0.00|Debit entry
|17|11|8|0.00|2500.00|Credit entry
|22|14|8|5200.00|0.00|Debit entry
|23|14|16|0.00|5200.00|Credit entry
|24|15|17|52000.00|0.00|Debit entry
|25|15|16|0.00|52000.00|Credit entry
|36|21|4|1770.00|0.00|Invoice #P20250403171604805
|37|21|5|0.00|1770.00|Invoice #P20250403171604805
|38|23|4|17700.00|0.00|Invoice #P20250404060423213
|39|23|5|0.00|17700.00|Invoice #P20250404060423213
|42|25|5|106200.00|0.00|Invoice #S20250404060929563
|43|25|3|0.00|106200.00|Invoice #S20250404060929563
|44|26|4|2124000.00|0.00|Invoice #P20250404090534150
|45|26|5|0.00|2124000.00|Invoice #P20250404090534150
|46|27|13|55000.00|0.00|Debit entry
|47|27|17|0.00|55000.00|Credit entry
|48|28|13|25000.00|0.00|Debit entry
|49|28|16|0.00|25000.00|Credit entry
but my below balance sheet not able to get Accounts and  subgroups <?php
require_once 'config.php';
requireLogin();

$company_id = $_SESSION['company_id'] ?? null;

// Get company details
$company_sql = "SELECT * FROM companies WHERE id = ?";
$stmt = $conn->prepare($company_sql);
$stmt->bind_param('i', $company_id);
$stmt->execute();
$company = $stmt->get_result()->fetch_assoc();
$stmt->close();

if (!$company) {
    die("Company not found.");
}

// Get date range from request, default to current financial year
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : $company['financial_year_start'];
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : $company['financial_year_end'];

if (!$start_date || !$end_date) {
    $start_date = date('Y-04-01');
    $end_date = date('Y-03-31', strtotime('+1 year'));
}

// Function to fetch account balances based on group
function getAccountGroupBalances($conn, $company_id, $group_id, $start_date, $end_date) {
    $balances = [];
    $sql = "SELECT a.id, a.account_name, ag.group_name,
            COALESCE((
                SELECT SUM(debit) - SUM(credit)
                FROM voucher_entries ve
                JOIN vouchers v ON ve.voucher_id = v.id
                WHERE ve.account_id = a.id
                AND v.company_id = ?
                AND v.voucher_date BETWEEN ? AND ?
            ), 0) + a.opening_balance as balance
            FROM accounts a
            JOIN account_groups ag ON a.account_group_id = ag.id
            WHERE a.company_id = ?
            AND a.account_group_id = ?
            ORDER BY a.account_name";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('issii', $company_id, $start_date, $end_date, $company_id, $group_id);
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        $balances[] = $row;
    }
    $stmt->close();
    return $balances;
}

// Get hierarchical account groups
function getAccountGroupsWithChildren($conn, $parent_id = null) {
    $groups = [];
    $sql = "SELECT * FROM account_groups WHERE parent_id " . ($parent_id ? "= $parent_id" : "IS NULL") . " ORDER BY group_name";
    $result = $conn->query($sql);
    
    while ($group = $result->fetch_assoc()) {
        $group['children'] = getAccountGroupsWithChildren($conn, $group['id']);
        $groups[] = $group;
    }
    return $groups;
}

// Get accounts with balances for a group and its children
function getGroupBalancesRecursive($conn, $company_id, $group, $start_date, $end_date) {
    $accounts = [];
    
    // Get accounts for current group
    $sql = "SELECT a.id, a.account_name, ag.group_name,
            COALESCE((
                SELECT SUM(debit) - SUM(credit)
                FROM voucher_entries ve
                JOIN vouchers v ON ve.voucher_id = v.id
                WHERE ve.account_id = a.id
                AND v.company_id = ?
                AND v.voucher_date BETWEEN ? AND ?
            ), 0) + a.opening_balance as balance
            FROM accounts a
            JOIN account_groups ag ON a.account_group_id = ag.id
            WHERE a.company_id = ?
            AND a.account_group_id = ?
            ORDER BY a.account_name";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('issii', $company_id, $start_date, $end_date, $company_id, $group['id']);
    $stmt->execute();
    $result = $stmt->get_result();
    
    while ($row = $result->fetch_assoc()) {
        $accounts[] = $row;
    }
    $stmt->close();
    
    // Get accounts for child groups
    foreach ($group['children'] as $child) {
        $accounts = array_merge($accounts, getGroupBalancesRecursive($conn, $company_id, $child, $start_date, $end_date));
    }
    
    return $accounts;
}

// Get all account groups with hierarchy
$asset_groups = getAccountGroupsWithChildren($conn, 1); // Assets
$liability_groups = getAccountGroupsWithChildren($conn, 2); // Liabilities
$equity_groups = getAccountGroupsWithChildren($conn, 5); // Capital

// Retrieve account details with hierarchy
$assets = [];
foreach ($asset_groups as $group) {
    $assets = array_merge($assets, getGroupBalancesRecursive($conn, $company_id, $group, $start_date, $end_date));
}

$liabilities = [];
foreach ($liability_groups as $group) {
    $liabilities = array_merge($liabilities, getGroupBalancesRecursive($conn, $company_id, $group, $start_date, $end_date));
}

$equity = [];
foreach ($equity_groups as $group) {
    $equity = array_merge($equity, getGroupBalancesRecursive($conn, $company_id, $group, $start_date, $end_date));
}

// Function to generate CSV data with complete hierarchy
function generateCSVData($company, $assets, $liabilities, $equity, $end_date) {
    $csv_data = [
        ["Company Name", $company['company_name']],
        ["Balance Sheet", "As on " . date('d-m-Y', strtotime($end_date))],
        [],
        ["Liabilities and Equity", "", "Assets"],
    ];

    // Process Liabilities with complete hierarchy
    $total_liabilities = 0;
    $current_group = null;
    $current_parent = null;
    
    foreach ($liabilities as $liability) {
        // Check if we need to add parent group header
        if (isset($liability['parent_group_name']) && $current_parent !== $liability['parent_group_name']) {
            $current_parent = $liability['parent_group_name'];
            $csv_data[] = [
                $current_parent,
                "",
                ""
            ];
        }
        
        // Check if we need to add subgroup header
        if ($current_group !== $liability['group_name']) {
            $current_group = $liability['group_name'];
            $indent = isset($liability['parent_group_name']) ? "  " : "";
            $csv_data[] = [
                $indent . $current_group,
                "",
                ""
            ];
        }
        
        // Add account with proper indentation
        $indent = isset($liability['parent_group_name']) ? "    " : "  ";
        $csv_data[] = [
            $indent . $liability['account_name'],
            number_format($liability['balance'], 2),
            "",
        ];
        $total_liabilities += $liability['balance'];
    }

    // Process Equity
    $total_equity = 0;
    foreach ($equity as $eq) {
        $csv_data[] = [
            $eq['account_name'],
            number_format($eq['balance'], 2),
            "",
        ];
        $total_equity += $eq['balance'];
    }

    $csv_data[] = [
        "Total Liabilities & Equity",
        number_format($total_liabilities + $total_equity, 2),
        "",
    ];

    $csv_data[] = ["", "", ""];

    // Process Assets with complete hierarchy
    $total_assets = 0;
    $current_group = null;
    $current_parent = null;
    
    foreach ($assets as $asset) {
        // Check if we need to add parent group header
        if (isset($asset['parent_group_name']) && $current_parent !== $asset['parent_group_name']) {
            $current_parent = $asset['parent_group_name'];
            $csv_data[] = [
                "",
                "",
                $current_parent
            ];
        }
        
        // Check if we need to add subgroup header
        if ($current_group !== $asset['group_name']) {
            $current_group = $asset['group_name'];
            $indent = isset($asset['parent_group_name']) ? "  " : "";
            $csv_data[] = [
                "",
                "",
                $indent . $current_group
            ];
        }
        
        // Add account with proper indentation
        $indent = isset($asset['parent_group_name']) ? "    " : "  ";
        $csv_data[] = [
            "",
            "",
            $indent . $asset['account_name'],
            number_format($asset['balance'], 2),
        ];
        $total_assets += $asset['balance'];
    }

    $csv_data[] = [
        "",
        "",
        "Total Assets",
        number_format($total_assets, 2),
    ];

    return $csv_data;
}

// Handle Excel export
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    $filename = "balance_sheet_" . date('Ymd') . ".csv";
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $csv_data = generateCSVData($company, $assets, $liabilities, $equity, $end_date);

    $output = fopen('php://output', 'w');
    foreach ($csv_data as $row) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit();
}

require_once 'header.php';
?>

<div class="container-fluid px-4">
    <div class="d-flex justify-content-between align-items-center mb-4">
        <h2>Balance Sheet</h2>
        <div class="d-flex gap-2">
            <button onclick="window.print()" class="btn btn-primary no-print">
                <i class="fas fa-print"></i> Print
            </button>
            <a href="?export=excel&start_date=<?php echo htmlspecialchars($start_date); ?>&end_date=<?php echo htmlspecialchars($end_date); ?>" class="btn btn-success no-print">
                <i class="fas fa-file-excel"></i> Export to Excel
            </a>
            <button onclick="exportToPDF()" class="btn btn-danger no-print">
                <i class="fas fa-file-pdf"></i> Save as PDF
            </button>
        </div>
    </div>

    <!-- Date Range Filter -->
    <div class="card mb-4 no-print">
        <div class="card-body">
            <form method="get" class="row g-3">
                <div class="col-md-4">
                    <label class="form-label">Start Date</label>
                    <input type="date" class="form-control" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                </div>
                <div class="col-md-4">
                    <label class="form-label">End Date</label>
                    <input type="date" class="form-control" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                </div>
                <div class="col-md-4">
                    <label class="form-label">&nbsp;</label>
                    <button type="submit" class="btn btn-primary d-block">Apply Filter</button>
                </div>
            </form>
        </div>
    </div>

    <!-- Balance Sheet -->
    <div class="card" id="balance-sheet">
        <div class="card-body">
            <div class="text-center mb-4">
                <h3><?php echo htmlspecialchars($company['company_name']); ?></h3>
                <h4>Balance Sheet</h4>
                <p>As on <?php echo date('d-m-Y', strtotime($end_date)); ?></p>
            </div>

            <div class="row">
                <!-- Liabilities and Equity Section -->
                <div class="col-md-6">
                    <h5 class="border-bottom pb-2">Liabilities and Equity</h5>
                    <table class="table table-sm">
                        <?php
                        $total_liabilities = 0;
                        $current_group = null;
                        foreach ($liabilities as $liability): 
                            if ($current_group !== $liability['group_name']) {
                                $current_group = $liability['group_name'];
                                echo '<tr class="group-header" onclick="toggleGroup(this)">';
                                echo '<td colspan="2"><i class="fas fa-plus-circle me-2"></i>';
                                echo htmlspecialchars($current_group);
                                echo '</td></tr>';
                            }
                            echo '<tr class="group-item" style="display:none">';
                            echo '<td class="ps-4">' . htmlspecialchars($liability['account_name']) . '</td>';
                            echo '<td class="text-end">₹' . number_format($liability['balance'], 2) . '</td>';
                            echo '</tr>';
                            $total_liabilities += $liability['balance'];
                        endforeach; ?>

                        <?php
                        $total_equity = 0;
                        foreach ($equity as $eq): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($eq['account_name']); ?></td>
                                <td class="text-end">₹<?php echo number_format($eq['balance'], 2); ?></td>
                            </tr>
                            <?php
                            $total_equity += $eq['balance'];
                        endforeach; ?>

                        <tr class="table-info">
                            <th>Total Liabilities & Equity</th>
                            <th class="text-end">₹<?php echo number_format($total_liabilities + $total_equity, 2); ?></th>
                        </tr>
                    </table>
                </div>
                <!-- Assets Section -->
                <div class="col-md-6">
                    <h5 class="border-bottom pb-2">Assets</h5>
                    <table class="table table-sm">
                        <?php
                        $total_assets = 0;
                        $current_group = null;
                        foreach ($assets as $asset): 
                            if ($current_group !== $asset['group_name']) {
                                $current_group = $asset['group_name'];
                                echo '<tr class="group-header" onclick="toggleGroup(this)">';
                                echo '<td colspan="2"><i class="fas fa-plus-circle me-2"></i>';
                                echo htmlspecialchars($current_group);
                                echo '</td></tr>';
                            }
                            echo '<tr class="group-item" style="display:none">';
                            echo '<td class="ps-4">' . htmlspecialchars($asset['account_name']) . '</td>';
                            echo '<td class="text-end">₹' . number_format($asset['balance'], 2) . '</td>';
                            echo '</tr>';
                            $total_assets += $asset['balance'];
                        endforeach; ?>
                        <tr class="table-info">
                            <th>Total Assets</th>
                            <th class="text-end">₹<?php echo number_format($total_assets, 2); ?></th>
                        </tr>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
@media print {
    body * {
        visibility: hidden;
    }

    #balance-sheet,
    #balance-sheet * {
        visibility: visible;
    }

    #balance-sheet {
        position: absolute;
        left: 0;
        top: 0;
        width: 100%;
        height: 100%;
        margin: 0;
        padding: 0;
    }

    .no-print {
        display: none;
    }

    .card {
        border: none;
    }

    .table {
        width: 100%;
        border-collapse: collapse;
    }

    .table th,
    .table td {
        border: 1px solid #ddd;
        padding: 8px;
    }

    .container-fluid {
        width: 100%;
        padding: 0;
    }
}
</style>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
<script>
function toggleGroup(header) {
    const icon = header.querySelector('i');
    const items = header.parentElement.querySelectorAll('.group-item');
    
    if (icon.classList.contains('fa-plus-circle')) {
        icon.classList.remove('fa-plus-circle');
        icon.classList.add('fa-minus-circle');
        items.forEach(item => item.style.display = '');
    } else {
        icon.classList.remove('fa-minus-circle');
        icon.classList.add('fa-plus-circle');
        items.forEach(item => item.style.display = 'none');
    }
}

function exportToPDF() {
    const { jsPDF } = window.jspdf;
    const doc = new jsPDF();
    const elementHTML = document.getElementById('balance-sheet');

    doc.html(elementHTML, {
        callback: function(doc) {
            doc.save('balance_sheet.pdf');
        },
        margin: [10, 10, 10, 10],
        autoPaging: 'text',
        x: 0,
        y: 0,
        width: 190, //target width in the PDF document
        windowWidth: 675 //window width in CSS pixels
    });
}
</script>

<?php require_once 'footer.php'; ?>