-- CreateTable
CREATE TABLE `activity_log` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `activity_type` ENUM('maintenance_payment', 'expense_added', 'announcement_posted', 'member_registered') NOT NULL,
    `reference_id` INTEGER UNSIGNED NULL,
    `title` VARCHAR(200) NOT NULL,
    `subtitle` VARCHAR(300) NULL,
    `amount` DECIMAL(12, 2) NULL,
    `done_by` VARCHAR(150) NULL,
    `activity_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_activity_at`(`activity_at`),
    INDEX `idx_activity_type`(`activity_type`),
    INDEX `idx_society_id`(`society_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `announcements` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `posted_by` INTEGER UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `category` ENUM('general', 'maintenance', 'events') NOT NULL DEFAULT 'general',
    `priority` ENUM('normal', 'urgent') NOT NULL DEFAULT 'normal',
    `has_attachment` BOOLEAN NOT NULL DEFAULT false,
    `attachment_url` VARCHAR(255) NULL,
    `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_category`(`category`),
    INDEX `idx_created_at`(`created_at`),
    INDEX `idx_priority`(`priority`),
    INDEX `idx_society_id`(`society_id`),
    INDEX `announcements_posted_by_fkey`(`posted_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `expenses` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `added_by` INTEGER UNSIGNED NOT NULL,
    `category` ENUM('maintenance', 'electricity', 'security', 'water', 'gardening', 'other') NOT NULL,
    `description` VARCHAR(500) NOT NULL,
    `vendor` VARCHAR(200) NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `expense_date` DATE NOT NULL,
    `payment_mode` ENUM('upi', 'cash', 'bank_transfer', 'cheque', 'other') NOT NULL DEFAULT 'upi',
    `receipt_url` VARCHAR(255) NULL,
    `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_category`(`category`),
    INDEX `idx_expense_date`(`expense_date`),
    INDEX `idx_society_id`(`society_id`),
    INDEX `expenses_added_by_fkey`(`added_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `maintenance_payments` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `member_id` INTEGER UNSIGNED NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `for_month` DATE NOT NULL,
    `payment_mode` ENUM('upi', 'cash', 'bank_transfer', 'cheque', 'other') NOT NULL DEFAULT 'upi',
    `transaction_number` VARCHAR(100) NULL,
    `remark` VARCHAR(300) NULL,
    `screenshot_url` VARCHAR(255) NULL,
    `approval_status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    `approved_by` INTEGER UNSIGNED NULL,
    `approved_at` TIMESTAMP(0) NULL,
    `reference_no` VARCHAR(100) NULL,
    `notes` VARCHAR(300) NULL,
    `status` BOOLEAN NOT NULL DEFAULT true,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `due_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `due_status` ENUM('pending', 'partial', 'paid') NOT NULL DEFAULT 'pending',
    `paid_date` DATE NULL,

    INDEX `idx_for_month`(`for_month`),
    INDEX `idx_member_id`(`member_id`),
    INDEX `idx_society_id`(`society_id`),
    INDEX `maintenance_payments_approved_by_fkey`(`approved_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `society` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `registration_no` VARCHAR(100) NULL,
    `type` ENUM('residential_apartment', 'gated_community', 'cooperative_housing', 'villa_township', 'commercial', 'other') NOT NULL DEFAULT 'residential_apartment',
    `address` TEXT NULL,
    `city` VARCHAR(100) NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `country` VARCHAR(100) NOT NULL DEFAULT 'India',
    `website` VARCHAR(255) NULL,
    `total_units` SMALLINT UNSIGNED NULL,
    `maintenance_charge` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `logo_url` VARCHAR(255) NULL,
    `admin_member_id` INTEGER UNSIGNED NULL,
    `plan` ENUM('free', 'basic', 'pro', 'enterprise') NOT NULL DEFAULT 'free',
    `plan_expires_at` DATE NULL,
    `registration_request_id` INTEGER UNSIGNED NULL,
    `status` ENUM('active', 'suspended', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `monthly_maintenance` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `is_deleted` BOOLEAN NOT NULL DEFAULT false,

    UNIQUE INDEX `uq_registration_no`(`registration_no`),
    UNIQUE INDEX `society_admin_member_id_key`(`admin_member_id`),
    UNIQUE INDEX `society_registration_request_id_key`(`registration_request_id`),
    INDEX `idx_city`(`city`),
    INDEX `idx_status`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `society_join_requests` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `mobile_number` VARCHAR(15) NOT NULL,
    `email` VARCHAR(150) NULL,
    `id_proof_url` VARCHAR(255) NULL,
    `id_proof_type` ENUM('aadhaar', 'pan', 'passport', 'other') NULL,
    `wing` VARCHAR(10) NULL,
    `flat_number` VARCHAR(20) NULL,
    `ownership_type` ENUM('owner', 'tenant') NULL DEFAULT 'owner',
    `additional_docs` VARCHAR(255) NULL,
    `remarks` VARCHAR(300) NULL,
    `step_completed` BOOLEAN NOT NULL DEFAULT true,
    `status` ENUM('draft', 'submitted', 'approved', 'rejected') NOT NULL DEFAULT 'draft',
    `reviewed_by` INTEGER UNSIGNED NULL,
    `reviewed_at` TIMESTAMP(0) NULL,
    `rejection_note` VARCHAR(300) NULL,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_mobile_number`(`mobile_number`),
    INDEX `idx_society_id`(`society_id`),
    INDEX `idx_status`(`status`),
    INDEX `society_join_requests_reviewed_by_fkey`(`reviewed_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `society_registration_requests` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_name` VARCHAR(150) NOT NULL,
    `contact_mobile` VARCHAR(15) NOT NULL,
    `contact_email` VARCHAR(150) NOT NULL,
    `designation` VARCHAR(100) NOT NULL,
    `society_name` VARCHAR(200) NOT NULL,
    `society_type` ENUM('residential_apartment', 'gated_community', 'cooperative_housing', 'villa_township', 'commercial', 'other') NOT NULL DEFAULT 'residential_apartment',
    `registration_no` VARCHAR(100) NULL,
    `total_units` SMALLINT UNSIGNED NULL,
    `address` TEXT NULL,
    `city` VARCHAR(100) NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `country` VARCHAR(100) NOT NULL DEFAULT 'India',
    `website` VARCHAR(255) NULL,
    `reg_certificate_url` VARCHAR(255) NULL,
    `other_docs_url` VARCHAR(255) NULL,
    `approval_status` ENUM('pending', 'under_review', 'approved', 'rejected') NOT NULL,
    `reviewed_by` INTEGER UNSIGNED NULL,
    `reviewed_at` TIMESTAMP(0) NULL,
    `review_notes` TEXT NULL,
    `society_id` INTEGER UNSIGNED NULL,
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `logo_url` VARCHAR(255) NULL,

    INDEX `idx_contact_email`(`contact_email`),
    INDEX `idx_society_name`(`society_name`),
    INDEX `idx_approval_status`(`approval_status`),
    INDEX `society_registration_requests_reviewed_by_fkey`(`reviewed_by`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_otps` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `mobile_number` VARCHAR(15) NOT NULL,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `member_id` INTEGER UNSIGNED NULL,
    `otp` VARCHAR(10) NOT NULL,
    `purpose` ENUM('login', 'forgot_password') NOT NULL DEFAULT 'login',
    `device_id` VARCHAR(255) NULL,
    `is_verified` BOOLEAN NOT NULL DEFAULT false,
    `expire_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `resend_count` INTEGER NOT NULL DEFAULT 0,
    `locked_until` DATETIME(0) NULL,

    INDEX `idx_member_otp`(`member_id`, `otp`),
    INDEX `idx_mobile_otp`(`mobile_number`, `otp`),
    INDEX `user_otps_society_id_fkey`(`society_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `society_id` INTEGER UNSIGNED NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `mobile_number` VARCHAR(15) NOT NULL,
    `flat_number` VARCHAR(20) NOT NULL,
    `vehicles` VARCHAR(255) NULL,
    `family_members` INTEGER NULL,
    `role` ENUM('superadmin', 'admin', 'member') NOT NULL DEFAULT 'member',
    `profile_photo` VARCHAR(255) NULL,
    `password_hash` VARCHAR(255) NULL,
    `fcm_token` VARCHAR(255) NULL,
    `last_login_at` TIMESTAMP(0) NULL,
    `member_since` DATE NULL,
    `is_approved` BOOLEAN NOT NULL DEFAULT false,
    `is_active` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `move_in_date` DATE NOT NULL DEFAULT (curdate()),
    `ownership` ENUM('owner', 'tenant') NOT NULL,

    INDEX `idx_email`(`email`),
    INDEX `idx_mobile`(`mobile_number`),
    INDEX `idx_society_id`(`society_id`),
    UNIQUE INDEX `uq_society_email`(`society_id`, `email`),
    UNIQUE INDEX `uq_society_mobile`(`society_id`, `mobile_number`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

-- AddForeignKey
ALTER TABLE `announcements` ADD CONSTRAINT `announcements_posted_by_fkey` FOREIGN KEY (`posted_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `expenses` ADD CONSTRAINT `expenses_added_by_fkey` FOREIGN KEY (`added_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `maintenance_payments` ADD CONSTRAINT `maintenance_payments_approved_by_fkey` FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `maintenance_payments` ADD CONSTRAINT `maintenance_payments_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `society` ADD CONSTRAINT `society_admin_member_id_fkey` FOREIGN KEY (`admin_member_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `society` ADD CONSTRAINT `society_registration_request_id_fkey` FOREIGN KEY (`registration_request_id`) REFERENCES `society_registration_requests`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `society_join_requests` ADD CONSTRAINT `society_join_requests_reviewed_by_fkey` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `society_registration_requests` ADD CONSTRAINT `society_registration_requests_reviewed_by_fkey` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_otps` ADD CONSTRAINT `user_otps_member_id_fkey` FOREIGN KEY (`member_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

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