/*
  Warnings:

  - You are about to drop the column `wing` on the `users` table. All the data in the column will be lost.

*/
-- AlterTable
ALTER TABLE `users` DROP COLUMN `wing`,
    MODIFY `flat_number` VARCHAR(20) NULL;

-- CreateTable
CREATE TABLE `transactions` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `member_id` INTEGER UNSIGNED NULL,
    `reference_type` ENUM('maintenance', 'expense') NOT NULL,
    `reference_id` INTEGER UNSIGNED NOT NULL,
    `member_name` VARCHAR(150) NOT NULL,
    `flat_number` VARCHAR(20) NULL,
    `transaction_type` VARCHAR(100) NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `direction` ENUM('credit', 'debit') NOT NULL,
    `payment_mode` ENUM('upi', 'cash', 'bank_transfer', 'cheque', 'other') NULL,
    `transaction_number` VARCHAR(100) NULL,
    `transaction_date` TIMESTAMP(0) NOT NULL,
    `approval_status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    `status` BOOLEAN NOT NULL DEFAULT true,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_society_id`(`society_id`),
    INDEX `idx_member_id`(`member_id`),
    INDEX `idx_reference`(`reference_type`, `reference_id`),
    INDEX `idx_transaction_date`(`transaction_date`),
    INDEX `idx_approval_status`(`approval_status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;