-- MyTree cPanel MySQL/MariaDB schema.
-- Import this file into an empty database you created in cPanel/phpMyAdmin.
-- Keep this file in sync with prisma/schema.prisma and prisma/migrations.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- CreateTable
CREATE TABLE `users` (
    `id` CHAR(36) NOT NULL,
    `full_name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NULL,
    `phone` VARCHAR(30) NULL,
    `pin_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,
    `referral_code` VARCHAR(32) NULL,
    `referred_by_id` CHAR(36) 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`),
    UNIQUE INDEX `users_referral_code_key`(`referral_code`),
    INDEX `users_role_idx`(`role`),
    INDEX `users_status_idx`(`status`),
    INDEX `users_kyc_status_idx`(`kyc_status`),
    INDEX `users_referred_by_id_idx`(`referred_by_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `auth_otps` (
    `id` CHAR(36) NOT NULL,
    `identifier_type` ENUM('EMAIL', 'MOBILE') NOT NULL,
    `identifier` VARCHAR(191) NOT NULL,
    `purpose` ENUM('LOGIN', 'REGISTER', 'RESET_PIN') NOT NULL,
    `code_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(191) NULL,
    `pin_hash` VARCHAR(255) NULL,
    `role` ENUM('PLANTER', 'DONOR', 'ADMIN') NULL,
    `referral_code` VARCHAR(32) NULL,
    `referrer_id` CHAR(36) NULL,
    `user_id` CHAR(36) NULL,
    `attempts` INTEGER NOT NULL DEFAULT 0,
    `expires_at` DATETIME(3) NOT NULL,
    `consumed_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `auth_otps_identifier_type_identifier_purpose_idx`(`identifier_type`, `identifier`, `purpose`),
    INDEX `auth_otps_expires_at_idx`(`expires_at`),
    INDEX `auth_otps_user_id_idx`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_sessions` (
    `id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `token_hash` VARCHAR(255) NOT NULL,
    `expires_at` DATETIME(3) NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `user_sessions_token_hash_key`(`token_hash`),
    INDEX `user_sessions_user_id_idx`(`user_id`),
    INDEX `user_sessions_expires_at_idx`(`expires_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `referral_settings` (
    `id` VARCHAR(32) NOT NULL DEFAULT 'default',
    `enabled` BOOLEAN NOT NULL DEFAULT true,
    `planter_bonus_amount` DECIMAL(12, 2) NOT NULL DEFAULT 25.00,
    `donor_bonus_amount` DECIMAL(12, 2) NOT NULL DEFAULT 25.00,
    `currency` CHAR(3) NOT NULL DEFAULT 'INR',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `referral_rewards` (
    `id` CHAR(36) NOT NULL,
    `referrer_id` CHAR(36) NOT NULL,
    `referred_user_id` CHAR(36) NOT NULL,
    `referral_code` VARCHAR(32) NOT NULL,
    `referrer_role` ENUM('PLANTER', 'DONOR', 'ADMIN') NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'INR',
    `status` ENUM('PAID', 'REJECTED') NOT NULL DEFAULT 'PAID',
    `wallet_transaction_id` CHAR(36) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `credited_at` DATETIME(3) NULL,

    UNIQUE INDEX `referral_rewards_referred_user_id_key`(`referred_user_id`),
    UNIQUE INDEX `referral_rewards_wallet_transaction_id_key`(`wallet_transaction_id`),
    INDEX `referral_rewards_referrer_id_idx`(`referrer_id`),
    INDEX `referral_rewards_referral_code_idx`(`referral_code`),
    INDEX `referral_rewards_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO `referral_settings` (`id`, `enabled`, `planter_bonus_amount`, `donor_bonus_amount`, `currency`)
VALUES ('default', true, 25.00, 25.00, 'INR');

-- CreateTable
CREATE TABLE `content_pages` (
    `id` CHAR(36) NOT NULL,
    `title` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `description` TEXT NOT NULL,
    `tags` JSON NULL,
    `status` ENUM('DRAFT', 'PUBLISHED') NOT NULL DEFAULT 'PUBLISHED',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `content_pages_slug_key`(`slug`),
    INDEX `content_pages_status_idx`(`status`),
    INDEX `content_pages_created_at_idx`(`created_at`),
    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 `users` ADD CONSTRAINT `users_referred_by_id_fkey` FOREIGN KEY (`referred_by_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `user_sessions` ADD CONSTRAINT `user_sessions_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_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;

-- AddForeignKey
ALTER TABLE `referral_rewards` ADD CONSTRAINT `referral_rewards_referrer_id_fkey` FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `referral_rewards` ADD CONSTRAINT `referral_rewards_referred_user_id_fkey` FOREIGN KEY (`referred_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `referral_rewards` ADD CONSTRAINT `referral_rewards_wallet_transaction_id_fkey` FOREIGN KEY (`wallet_transaction_id`) REFERENCES `wallet_transactions`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS=1;