-- CreateTable
CREATE TABLE `city` (
    `city_id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `state` VARCHAR(50) NOT NULL,
    `country` VARCHAR(50) NOT NULL DEFAULT 'India',
    `timezone` VARCHAR(50) NOT NULL DEFAULT 'Asia/Kolkata',
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `locality` (
    `locality_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `display_order` INTEGER NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `locality_city_id_idx`(`city_id`),
    UNIQUE INDEX `locality_city_id_name_key`(`city_id`, `name`),
    PRIMARY KEY (`locality_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `samuday` (
    `samuday_id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `display_order` INTEGER NOT NULL DEFAULT 0,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `samuday_name_key`(`name`),
    PRIMARY KEY (`samuday_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `location` (
    `location_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `location_type` ENUM('upashray', 'temple', 'residence', 'dharamshala', 'other') NOT NULL,
    `formatted_address` VARCHAR(500) NULL,
    `latitude` DECIMAL(10, 7) NULL,
    `longitude` DECIMAL(10, 7) NULL,
    `google_place_id` VARCHAR(255) NULL,
    `locality_id` INTEGER NULL,
    `created_by` INTEGER NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `location_city_id_name_idx`(`city_id`, `name`),
    INDEX `location_city_id_location_type_idx`(`city_id`, `location_type`),
    UNIQUE INDEX `location_city_id_google_place_id_key`(`city_id`, `google_place_id`),
    PRIMARY KEY (`location_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user` (
    `user_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NULL,
    `username` VARCHAR(50) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `home_locality_id` INTEGER NULL,
    `is_captain` BOOLEAN NOT NULL DEFAULT false,
    `is_volunteer` BOOLEAN NOT NULL DEFAULT true,
    `is_super_admin` BOOLEAN NOT NULL DEFAULT false,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `created_by` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `last_login_at` DATETIME(3) NULL,

    INDEX `user_city_id_is_active_idx`(`city_id`, `is_active`),
    INDEX `user_city_id_is_captain_idx`(`city_id`, `is_captain`),
    INDEX `user_city_id_is_volunteer_idx`(`city_id`, `is_volunteer`),
    INDEX `user_city_id_home_locality_id_idx`(`city_id`, `home_locality_id`),
    UNIQUE INDEX `user_city_id_username_key`(`city_id`, `username`),
    UNIQUE INDEX `user_city_id_phone_key`(`city_id`, `phone`),
    PRIMARY KEY (`user_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `vihar` (
    `vihar_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NOT NULL,
    `vihar_date` DATE NOT NULL,
    `planned_start_time` VARCHAR(8) NOT NULL,
    `sadhuji_count` SMALLINT NOT NULL DEFAULT 0,
    `sadhviji_count` SMALLINT NOT NULL DEFAULT 0,
    `other_count` SMALLINT NOT NULL DEFAULT 0,
    `head_saint_honorific` VARCHAR(50) NULL,
    `head_saint_name` VARCHAR(150) NOT NULL,
    `samuday_id` INTEGER NULL,
    `departure_location_id` INTEGER NOT NULL,
    `arrival_location_id` INTEGER NOT NULL,
    `departure_locality_id` INTEGER NULL,
    `temple_darshan` BOOLEAN NOT NULL DEFAULT false,
    `updhi` BOOLEAN NOT NULL DEFAULT false,
    `remarks` VARCHAR(1000) NULL,
    `planned_distance_m` INTEGER NULL,
    `planned_duration_s` INTEGER NULL,
    `status` ENUM('planned', 'in_progress', 'completed', 'cancelled', 'auto_closed') NOT NULL DEFAULT 'planned',
    `cancel_reason` VARCHAR(500) NULL,
    `actual_start_at` DATETIME(3) NULL,
    `actual_end_at` DATETIME(3) NULL,
    `actual_distance_km` DECIMAL(6, 2) NULL,
    `distance_was_overridden` BOOLEAN NOT NULL DEFAULT false,
    `closed_by_user_id` INTEGER NULL,
    `closed_by_captain` BOOLEAN NOT NULL DEFAULT false,
    `auto_closed` BOOLEAN NOT NULL DEFAULT false,
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `vihar_city_id_vihar_date_status_idx`(`city_id`, `vihar_date`, `status`),
    INDEX `vihar_city_id_departure_locality_id_idx`(`city_id`, `departure_locality_id`),
    INDEX `vihar_city_id_samuday_id_idx`(`city_id`, `samuday_id`),
    INDEX `vihar_status_vihar_date_idx`(`status`, `vihar_date`),
    PRIMARY KEY (`vihar_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `vihar_volunteer` (
    `allocation_id` INTEGER NOT NULL AUTO_INCREMENT,
    `vihar_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `allocated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `allocated_by` INTEGER NOT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `replaced_by_allocation_id` INTEGER NULL,
    `check_in_at` DATETIME(3) NULL,
    `check_in_lat` DECIMAL(10, 7) NULL,
    `check_in_lng` DECIMAL(10, 7) NULL,
    `check_in_accuracy_m` INTEGER NULL,
    `check_in_without_gps` BOOLEAN NOT NULL DEFAULT false,
    `check_out_at` DATETIME(3) NULL,
    `check_out_lat` DECIMAL(10, 7) NULL,
    `check_out_lng` DECIMAL(10, 7) NULL,
    `check_out_accuracy_m` INTEGER NULL,
    `entered_start_time` VARCHAR(8) NULL,
    `entered_by_captain` BOOLEAN NOT NULL DEFAULT false,
    `auto_filled_by_cron` BOOLEAN NOT NULL DEFAULT false,
    `volunteer_distance_km` DECIMAL(6, 2) NULL,
    `distance_approval_status` ENUM('auto_accepted', 'pending_captain', 'approved', 'rejected') NOT NULL DEFAULT 'auto_accepted',
    `distance_approved_by` INTEGER NULL,
    `distance_approved_at` DATETIME(3) NULL,
    `notes` VARCHAR(500) NULL,

    INDEX `vihar_volunteer_vihar_id_is_active_idx`(`vihar_id`, `is_active`),
    INDEX `vihar_volunteer_user_id_is_active_idx`(`user_id`, `is_active`),
    INDEX `vihar_volunteer_distance_approval_status_idx`(`distance_approval_status`),
    PRIMARY KEY (`allocation_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `route_distance_cache` (
    `cache_id` INTEGER NOT NULL AUTO_INCREMENT,
    `cache_key` VARCHAR(520) NOT NULL,
    `origin_place_id` VARCHAR(255) NOT NULL,
    `destination_place_id` VARCHAR(255) NOT NULL,
    `distance_meters` INTEGER NOT NULL,
    `duration_seconds` INTEGER NOT NULL,
    `polyline` TEXT NULL,
    `fetched_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `expires_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `route_distance_cache_cache_key_key`(`cache_key`),
    INDEX `route_distance_cache_expires_at_idx`(`expires_at`),
    PRIMARY KEY (`cache_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `city_setting` (
    `setting_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NOT NULL,
    `key` VARCHAR(100) NOT NULL,
    `value` VARCHAR(1000) NOT NULL,
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `city_setting_city_id_key_key`(`city_id`, `key`),
    PRIMARY KEY (`setting_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `message_template` (
    `template_id` INTEGER NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NOT NULL,
    `template_key` VARCHAR(50) NOT NULL,
    `body` TEXT NOT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT true,
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `message_template_city_id_template_key_key`(`city_id`, `template_key`),
    PRIMARY KEY (`template_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `audit_log` (
    `audit_id` BIGINT NOT NULL AUTO_INCREMENT,
    `city_id` INTEGER NULL,
    `user_id` INTEGER NULL,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INTEGER NOT NULL,
    `action` VARCHAR(50) NOT NULL,
    `old_value_json` TEXT NULL,
    `new_value_json` TEXT NULL,
    `revert_sql` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `audit_log_city_id_entity_type_entity_id_idx`(`city_id`, `entity_type`, `entity_id`),
    INDEX `audit_log_user_id_created_at_idx`(`user_id`, `created_at`),
    PRIMARY KEY (`audit_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `locality` ADD CONSTRAINT `locality_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `location` ADD CONSTRAINT `location_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `location` ADD CONSTRAINT `location_locality_id_fkey` FOREIGN KEY (`locality_id`) REFERENCES `locality`(`locality_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `location` ADD CONSTRAINT `location_created_by_fkey` FOREIGN KEY (`created_by`) REFERENCES `user`(`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user` ADD CONSTRAINT `user_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user` ADD CONSTRAINT `user_home_locality_id_fkey` FOREIGN KEY (`home_locality_id`) REFERENCES `locality`(`locality_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user` ADD CONSTRAINT `user_created_by_fkey` FOREIGN KEY (`created_by`) REFERENCES `user`(`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_samuday_id_fkey` FOREIGN KEY (`samuday_id`) REFERENCES `samuday`(`samuday_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_departure_location_id_fkey` FOREIGN KEY (`departure_location_id`) REFERENCES `location`(`location_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_arrival_location_id_fkey` FOREIGN KEY (`arrival_location_id`) REFERENCES `location`(`location_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_departure_locality_id_fkey` FOREIGN KEY (`departure_locality_id`) REFERENCES `locality`(`locality_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_created_by_fkey` FOREIGN KEY (`created_by`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar` ADD CONSTRAINT `vihar_closed_by_user_id_fkey` FOREIGN KEY (`closed_by_user_id`) REFERENCES `user`(`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar_volunteer` ADD CONSTRAINT `vihar_volunteer_vihar_id_fkey` FOREIGN KEY (`vihar_id`) REFERENCES `vihar`(`vihar_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar_volunteer` ADD CONSTRAINT `vihar_volunteer_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar_volunteer` ADD CONSTRAINT `vihar_volunteer_allocated_by_fkey` FOREIGN KEY (`allocated_by`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `vihar_volunteer` ADD CONSTRAINT `vihar_volunteer_distance_approved_by_fkey` FOREIGN KEY (`distance_approved_by`) REFERENCES `user`(`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `city_setting` ADD CONSTRAINT `city_setting_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `message_template` ADD CONSTRAINT `message_template_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `audit_log` ADD CONSTRAINT `audit_log_city_id_fkey` FOREIGN KEY (`city_id`) REFERENCES `city`(`city_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `audit_log` ADD CONSTRAINT `audit_log_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;