-- CreateTable
CREATE TABLE `expense_categories` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `sort_order` INTEGER NOT NULL DEFAULT 0,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `expense_categories_gym_id_is_active_idx`(`gym_id`, `is_active`),
    UNIQUE INDEX `expense_categories_gym_id_name_key`(`gym_id`, `name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `payment_accounts` (
    `id` CHAR(36) NOT NULL,
    `gym_id` CHAR(36) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `opening_balance_nrs` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `current_balance_nrs` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `is_system` BOOLEAN NOT NULL DEFAULT false,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `payment_accounts_gym_id_is_active_idx`(`gym_id`, `is_active`),
    UNIQUE INDEX `payment_accounts_gym_id_name_key`(`gym_id`, `name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Seed default expense categories for each gym
INSERT INTO `expense_categories` (`id`, `gym_id`, `name`, `is_active`, `sort_order`, `created_at`, `updated_at`)
SELECT UUID(), g.`id`, c.`name`, true, c.`sort_order`, NOW(3), NOW(3)
FROM `gyms` g
CROSS JOIN (
    SELECT 'Rent' AS `name`, 0 AS `sort_order`
    UNION ALL SELECT 'Electricity', 1
    UNION ALL SELECT 'Equipment', 2
    UNION ALL SELECT 'Salary', 3
    UNION ALL SELECT 'Maintenance', 4
    UNION ALL SELECT 'Other', 5
) c;

-- Seed default payment accounts for each gym (includes legacy method accounts for backfill)
INSERT INTO `payment_accounts` (`id`, `gym_id`, `name`, `opening_balance_nrs`, `current_balance_nrs`, `is_system`, `is_active`, `created_at`, `updated_at`)
SELECT UUID(), g.`id`, a.`name`, 0, 0, a.`is_system`, true, NOW(3), NOW(3)
FROM `gyms` g
CROSS JOIN (
    SELECT 'Cash' AS `name`, true AS `is_system`
    UNION ALL SELECT 'eSewa', false
    UNION ALL SELECT 'Khalti', false
    UNION ALL SELECT 'Bank Transfer', false
) a;

-- Add new FK columns (nullable during backfill)
ALTER TABLE `expenses` ADD COLUMN `category_id` CHAR(36) NULL;
ALTER TABLE `expenses` ADD COLUMN `account_id` CHAR(36) NULL;
ALTER TABLE `payments` ADD COLUMN `account_id` CHAR(36) NULL;

-- Backfill expense categories
UPDATE `expenses` e
JOIN `expense_categories` ec ON ec.`gym_id` = e.`gym_id` AND ec.`name` = (
    CASE e.`category`
        WHEN 'rent' THEN 'Rent'
        WHEN 'electricity' THEN 'Electricity'
        WHEN 'equipment' THEN 'Equipment'
        WHEN 'salary' THEN 'Salary'
        WHEN 'maintenance' THEN 'Maintenance'
        WHEN 'other' THEN 'Other'
        ELSE 'Other'
    END
)
SET e.`category_id` = ec.`id`;

-- Backfill expense accounts (historical expenses paid from Cash)
UPDATE `expenses` e
JOIN `payment_accounts` pa ON pa.`gym_id` = e.`gym_id` AND pa.`name` = 'Cash'
SET e.`account_id` = pa.`id`;

-- Backfill payment accounts from legacy payment_method
UPDATE `payments` p
JOIN `payment_accounts` pa ON pa.`gym_id` = p.`gym_id` AND pa.`name` = (
    CASE p.`payment_method`
        WHEN 'cash' THEN 'Cash'
        WHEN 'esewa' THEN 'eSewa'
        WHEN 'khalti' THEN 'Khalti'
        WHEN 'bank_transfer' THEN 'Bank Transfer'
        ELSE 'Cash'
    END
)
SET p.`account_id` = pa.`id`;

-- Drop legacy columns
ALTER TABLE `expenses` DROP COLUMN `category`;
ALTER TABLE `payments` DROP COLUMN `payment_method`;

-- Make new columns required
ALTER TABLE `expenses` MODIFY `category_id` CHAR(36) NOT NULL;
ALTER TABLE `expenses` MODIFY `account_id` CHAR(36) NOT NULL;
ALTER TABLE `payments` MODIFY `account_id` CHAR(36) NOT NULL;

-- Drop legacy indexes
DROP INDEX `expenses_gym_id_category_idx` ON `expenses`;
DROP INDEX `payments_gym_id_payment_method_idx` ON `payments`;

-- AddForeignKey
ALTER TABLE `expense_categories` ADD CONSTRAINT `expense_categories_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `payment_accounts` ADD CONSTRAINT `payment_accounts_gym_id_fkey` FOREIGN KEY (`gym_id`) REFERENCES `gyms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `expenses` ADD CONSTRAINT `expenses_category_id_fkey` FOREIGN KEY (`category_id`) REFERENCES `expense_categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `expenses` ADD CONSTRAINT `expenses_account_id_fkey` FOREIGN KEY (`account_id`) REFERENCES `payment_accounts`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `payments` ADD CONSTRAINT `payments_account_id_fkey` FOREIGN KEY (`account_id`) REFERENCES `payment_accounts`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Add indexes
CREATE INDEX `expenses_gym_id_category_id_idx` ON `expenses`(`gym_id`, `category_id`);
CREATE INDEX `expenses_gym_id_account_id_idx` ON `expenses`(`gym_id`, `account_id`);
CREATE INDEX `payments_gym_id_account_id_idx` ON `payments`(`gym_id`, `account_id`);

-- Recalculate account balances from transaction history
UPDATE `payment_accounts` pa
SET `current_balance_nrs` = pa.`opening_balance_nrs`
    + COALESCE((SELECT SUM(`amount_paid`) FROM `payments` p WHERE p.`account_id` = pa.`id`), 0)
    - COALESCE((SELECT SUM(`amount_nrs`) FROM `expenses` e WHERE e.`account_id` = pa.`id`), 0);