-- SoftSuite Finance upgrade, 2026-09-16
-- Import this into the EXISTING application database in phpMyAdmin.
-- It adds bank accounts, invoices, account ledgers, invoice payments and actual loan payments.

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `financial_accounts` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `type` varchar(255) NOT NULL DEFAULT 'bank',
 `bank_name` varchar(255) NULL, `account_title` varchar(255) NULL, `account_number` varchar(255) NULL, `iban` varchar(255) NULL,
 `currency` varchar(10) NOT NULL DEFAULT 'PKR', `opening_balance` decimal(16,2) NOT NULL DEFAULT 0.00, `active` tinyint(1) NOT NULL DEFAULT 1,
 `notes` text NULL, `created_at` timestamp NULL, `updated_at` timestamp NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoices` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `invoice_number` varchar(255) NOT NULL, `client_id` bigint unsigned NOT NULL, `project_id` bigint unsigned NULL,
 `issue_date` date NOT NULL, `due_date` date NULL, `status` varchar(255) NOT NULL DEFAULT 'unpaid', `subtotal` decimal(16,2) NOT NULL DEFAULT 0,
 `discount` decimal(16,2) NOT NULL DEFAULT 0, `tax` decimal(16,2) NOT NULL DEFAULT 0, `total` decimal(16,2) NOT NULL DEFAULT 0,
 `notes` text NULL, `created_at` timestamp NULL, `updated_at` timestamp NULL, PRIMARY KEY (`id`), UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
 KEY `invoices_client_id_foreign` (`client_id`), KEY `invoices_project_id_foreign` (`project_id`),
 CONSTRAINT `invoices_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
 CONSTRAINT `invoices_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoice_items` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `invoice_id` bigint unsigned NOT NULL, `description` varchar(255) NOT NULL, `qty` decimal(12,2) NOT NULL DEFAULT 1,
 `unit_price` decimal(16,2) NOT NULL DEFAULT 0, `amount` decimal(16,2) NOT NULL DEFAULT 0, `created_at` timestamp NULL, `updated_at` timestamp NULL,
 PRIMARY KEY (`id`), KEY `invoice_items_invoice_id_foreign` (`invoice_id`), CONSTRAINT `invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `account_transactions` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `financial_account_id` bigint unsigned NOT NULL, `transaction_date` date NOT NULL, `type` varchar(255) NOT NULL,
 `category` varchar(255) NOT NULL DEFAULT 'other', `amount` decimal(16,2) NOT NULL, `description` varchar(255) NOT NULL, `client_id` bigint unsigned NULL,
 `project_id` bigint unsigned NULL, `invoice_id` bigint unsigned NULL, `loan_id` bigint unsigned NULL, `transfer_account_id` bigint unsigned NULL,
 `reference` varchar(255) NULL, `notes` text NULL, `created_at` timestamp NULL, `updated_at` timestamp NULL, PRIMARY KEY (`id`),
 KEY `account_transactions_account_date_index` (`financial_account_id`,`transaction_date`),
 CONSTRAINT `account_transactions_financial_account_id_foreign` FOREIGN KEY (`financial_account_id`) REFERENCES `financial_accounts` (`id`) ON DELETE CASCADE,
 CONSTRAINT `account_transactions_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
 CONSTRAINT `account_transactions_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL,
 CONSTRAINT `account_transactions_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
 CONSTRAINT `account_transactions_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE SET NULL,
 CONSTRAINT `account_transactions_transfer_account_id_foreign` FOREIGN KEY (`transfer_account_id`) REFERENCES `financial_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoice_payments` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `invoice_id` bigint unsigned NOT NULL, `financial_account_id` bigint unsigned NOT NULL, `payment_date` date NOT NULL,
 `amount` decimal(16,2) NOT NULL, `method` varchar(255) NULL, `reference` varchar(255) NULL, `notes` text NULL, `created_at` timestamp NULL, `updated_at` timestamp NULL,
 PRIMARY KEY (`id`), CONSTRAINT `invoice_payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
 CONSTRAINT `invoice_payments_financial_account_id_foreign` FOREIGN KEY (`financial_account_id`) REFERENCES `financial_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loan_payments` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT, `loan_id` bigint unsigned NOT NULL, `financial_account_id` bigint unsigned NOT NULL, `payment_date` date NOT NULL,
 `principal_amount` decimal(16,2) NOT NULL DEFAULT 0, `interest_amount` decimal(16,2) NOT NULL DEFAULT 0, `other_charges` decimal(16,2) NOT NULL DEFAULT 0,
 `total_amount` decimal(16,2) NOT NULL DEFAULT 0, `reference` varchar(255) NULL, `notes` text NULL, `created_at` timestamp NULL, `updated_at` timestamp NULL,
 PRIMARY KEY (`id`), CONSTRAINT `loan_payments_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
 CONSTRAINT `loan_payments_financial_account_id_foreign` FOREIGN KEY (`financial_account_id`) REFERENCES `financial_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add tracking fields to existing project payments only if they are missing.
SET @db = DATABASE();
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='project_payments' AND COLUMN_NAME='financial_account_id')=0,
 'ALTER TABLE project_payments ADD COLUMN financial_account_id BIGINT UNSIGNED NULL AFTER project_id, ADD CONSTRAINT project_payments_financial_account_id_foreign FOREIGN KEY (financial_account_id) REFERENCES financial_accounts(id) ON DELETE SET NULL', 'SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='project_payments' AND COLUMN_NAME='invoice_id')=0,
 'ALTER TABLE project_payments ADD COLUMN invoice_id BIGINT UNSIGNED NULL AFTER financial_account_id, ADD CONSTRAINT project_payments_invoice_id_foreign FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL', 'SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='project_payments' AND COLUMN_NAME='payment_date')=0,
 'ALTER TABLE project_payments ADD COLUMN payment_date DATE NULL AFTER period', 'SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Safe starter accounts. Edit these from the application after import.
INSERT INTO `financial_accounts` (`name`,`type`,`currency`,`opening_balance`,`active`,`notes`,`created_at`,`updated_at`)
SELECT 'Main Bank Account','bank','PKR',0,1,'Primary business bank account. Add actual bank details from Accounts.',NOW(),NOW()
WHERE NOT EXISTS (SELECT 1 FROM financial_accounts WHERE name='Main Bank Account');
INSERT INTO `financial_accounts` (`name`,`type`,`currency`,`opening_balance`,`active`,`notes`,`created_at`,`updated_at`)
SELECT 'Cash in Hand','cash','PKR',0,1,'Physical cash ledger.',NOW(),NOW()
WHERE NOT EXISTS (SELECT 1 FROM financial_accounts WHERE name='Cash in Hand');
INSERT INTO `financial_accounts` (`name`,`type`,`currency`,`opening_balance`,`active`,`notes`,`created_at`,`updated_at`)
SELECT 'Upwork Clearing','upwork','PKR',0,1,'Use for Upwork receipts before transferring to a bank account.',NOW(),NOW()
WHERE NOT EXISTS (SELECT 1 FROM financial_accounts WHERE name='Upwork Clearing');

SET FOREIGN_KEY_CHECKS=1;