-- AlterTable
ALTER TABLE `users` ADD COLUMN `is_superadmin` BOOLEAN NOT NULL DEFAULT false;

-- CreateTable
CREATE TABLE `user_society_mapping` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER UNSIGNED NOT NULL,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `role` ENUM('superadmin', 'admin', 'member', 'secretary', 'treasurer', 'chairperson', 'manager') NOT NULL DEFAULT 'member',
    `is_admin` BOOLEAN NOT NULL DEFAULT false,
    `is_member` BOOLEAN NOT NULL DEFAULT true,
    `permissions` JSON NULL,
    `flat_number` VARCHAR(20) NULL,
    `wing` VARCHAR(10) NULL,
    `vehicles` VARCHAR(255) NULL,
    `family_members` INTEGER NULL,
    `move_in_date` DATE NULL,
    `ownership` ENUM('owner', 'tenant') NOT NULL DEFAULT 'owner',
    `is_approved` BOOLEAN NOT NULL DEFAULT false,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `member_since` DATE NULL,
    `maintenance_status` ENUM('verified', 'unverified') NOT NULL DEFAULT 'unverified',
    `joined_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_usm_society_id`(`society_id`),
    INDEX `idx_usm_user_id`(`user_id`),
    UNIQUE INDEX `uq_user_society`(`user_id`, `society_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `user_society_mapping` ADD CONSTRAINT `user_society_mapping_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_society_mapping` ADD CONSTRAINT `user_society_mapping_society_id_fkey` FOREIGN KEY (`society_id`) REFERENCES `society`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;