-- CreateTable
CREATE TABLE `users` (
    `id` CHAR(36) NOT NULL,
    `full_name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `phone` VARCHAR(30) NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('PLANTER', 'DONOR', 'ADMIN') NOT NULL DEFAULT 'PLANTER',
    `status` ENUM('PENDING', 'ACTIVE', 'SUSPENDED') NOT NULL DEFAULT 'PENDING',
    `kyc_status` ENUM('PENDING_SUBMISSION', 'PENDING_REVIEW', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'PENDING_SUBMISSION',
    `wallet_balance` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `upi_id` VARCHAR(191) NULL,
    `email_notifications` BOOLEAN NOT NULL DEFAULT true,
    `sms_alerts` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `users_email_key`(`email`),
    UNIQUE INDEX `users_phone_key`(`phone`),
    INDEX `users_role_idx`(`role`),
    INDEX `users_status_idx`(`status`),
    INDEX `users_kyc_status_idx`(`kyc_status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `kyc_documents` (
    `id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `type` ENUM('ID_FRONT', 'ID_BACK', 'SELFIE', 'ADDRESS_PROOF') NOT NULL,
    `file_url` VARCHAR(500) NOT NULL,
    `status` ENUM('PENDING', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `rejection_note` TEXT NULL,
    `uploaded_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `reviewed_at` DATETIME(3) NULL,
    `reviewed_by_id` CHAR(36) NULL,

    INDEX `kyc_documents_user_id_idx`(`user_id`),
    INDEX `kyc_documents_status_idx`(`status`),
    INDEX `kyc_documents_reviewed_by_id_idx`(`reviewed_by_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `trees` (
    `id` CHAR(36) NOT NULL,
    `public_code` VARCHAR(30) NOT NULL,
    `planter_id` CHAR(36) NOT NULL,
    `donor_id` CHAR(36) NULL,
    `approved_by_id` CHAR(36) NULL,
    `species` VARCHAR(120) NULL,
    `latitude` DECIMAL(10, 7) NOT NULL,
    `longitude` DECIMAL(10, 7) NOT NULL,
    `address` VARCHAR(255) NULL,
    `photo_url` VARCHAR(500) NULL,
    `status` ENUM('PROPOSED', 'APPROVED', 'REJECTED', 'ARCHIVED') NOT NULL DEFAULT 'PROPOSED',
    `planted_at` DATETIME(3) NULL,
    `approved_at` DATETIME(3) NULL,
    `rejection_note` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `trees_public_code_key`(`public_code`),
    INDEX `trees_planter_id_idx`(`planter_id`),
    INDEX `trees_donor_id_idx`(`donor_id`),
    INDEX `trees_approved_by_id_idx`(`approved_by_id`),
    INDEX `trees_status_idx`(`status`),
    INDEX `trees_latitude_longitude_idx`(`latitude`, `longitude`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `monitoring_reports` (
    `id` CHAR(36) NOT NULL,
    `public_code` VARCHAR(30) NOT NULL,
    `tree_id` CHAR(36) NOT NULL,
    `inspector_id` CHAR(36) NOT NULL,
    `reviewed_by_id` CHAR(36) NULL,
    `photo_url` VARCHAR(500) NOT NULL,
    `gps_latitude` DECIMAL(10, 7) NOT NULL,
    `gps_longitude` DECIMAL(10, 7) NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `gps_match` BOOLEAN NOT NULL DEFAULT false,
    `time_valid` BOOLEAN NOT NULL DEFAULT true,
    `ip_valid` BOOLEAN NOT NULL DEFAULT true,
    `status` ENUM('PENDING', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `inspected_at` DATETIME(3) NOT NULL,
    `reviewed_at` DATETIME(3) NULL,
    `review_note` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `monitoring_reports_public_code_key`(`public_code`),
    INDEX `monitoring_reports_tree_id_idx`(`tree_id`),
    INDEX `monitoring_reports_inspector_id_idx`(`inspector_id`),
    INDEX `monitoring_reports_reviewed_by_id_idx`(`reviewed_by_id`),
    INDEX `monitoring_reports_status_idx`(`status`),
    INDEX `monitoring_reports_inspected_at_idx`(`inspected_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `donations` (
    `id` CHAR(36) NOT NULL,
    `donor_id` CHAR(36) NOT NULL,
    `tree_id` CHAR(36) NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'INR',
    `payment_provider` VARCHAR(80) NULL,
    `provider_ref` VARCHAR(191) NULL,
    `status` ENUM('PENDING', 'PAID', 'FAILED', 'REFUNDED') NOT NULL DEFAULT 'PENDING',
    `paid_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `donations_provider_ref_key`(`provider_ref`),
    INDEX `donations_donor_id_idx`(`donor_id`),
    INDEX `donations_tree_id_idx`(`tree_id`),
    INDEX `donations_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `wallet_transactions` (
    `id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `type` ENUM('CREDIT', 'DEBIT', 'REWARD', 'WITHDRAWAL', 'DONATION', 'ADJUSTMENT') NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'INR',
    `status` ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    `description` VARCHAR(255) NULL,
    `reference` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `wallet_transactions_reference_key`(`reference`),
    INDEX `wallet_transactions_user_id_idx`(`user_id`),
    INDEX `wallet_transactions_type_idx`(`type`),
    INDEX `wallet_transactions_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

-- AddForeignKey
ALTER TABLE `kyc_documents` ADD CONSTRAINT `kyc_documents_reviewed_by_id_fkey` FOREIGN KEY (`reviewed_by_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trees` ADD CONSTRAINT `trees_planter_id_fkey` FOREIGN KEY (`planter_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trees` ADD CONSTRAINT `trees_donor_id_fkey` FOREIGN KEY (`donor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trees` ADD CONSTRAINT `trees_approved_by_id_fkey` FOREIGN KEY (`approved_by_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `monitoring_reports` ADD CONSTRAINT `monitoring_reports_tree_id_fkey` FOREIGN KEY (`tree_id`) REFERENCES `trees`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `monitoring_reports` ADD CONSTRAINT `monitoring_reports_inspector_id_fkey` FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `monitoring_reports` ADD CONSTRAINT `monitoring_reports_reviewed_by_id_fkey` FOREIGN KEY (`reviewed_by_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `donations` ADD CONSTRAINT `donations_donor_id_fkey` FOREIGN KEY (`donor_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `donations` ADD CONSTRAINT `donations_tree_id_fkey` FOREIGN KEY (`tree_id`) REFERENCES `trees`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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