-- Car listings and related tables

CREATE TABLE `car_listings` (
    `id` VARCHAR(191) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,
    `status` ENUM('PENDING_APPROVAL', 'APPROVED', 'REJECTED', 'SOLD', 'INACTIVE') NOT NULL DEFAULT 'PENDING_APPROVAL',
    `approved_at` DATETIME(3) NULL,
    `approved_by_id` VARCHAR(191) NULL,
    `rejection_reason` VARCHAR(500) NULL,
    `submitted_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `title` VARCHAR(200) NOT NULL,
    `brand_id` VARCHAR(191) NOT NULL,
    `model_id` VARCHAR(191) NOT NULL,
    `variant_id` VARCHAR(191) NOT NULL,
    `manufacturing_year` INTEGER NOT NULL,
    `registration_year` INTEGER NOT NULL,
    `fuel_type_id` VARCHAR(191) NOT NULL,
    `transmission_id` VARCHAR(191) NOT NULL,
    `color_id` VARCHAR(191) NOT NULL,
    `description` TEXT NULL,
    `registration_number` VARCHAR(20) NOT NULL,
    `registration_state` VARCHAR(80) NOT NULL,
    `registration_city` VARCHAR(80) NOT NULL,
    `rto` VARCHAR(120) NOT NULL,
    `vin_number` VARCHAR(80) NULL,
    `hide_vin_from_buyers` BOOLEAN NOT NULL DEFAULT false,
    `engine_number` VARCHAR(80) NULL,
    `hide_engine_from_buyers` BOOLEAN NOT NULL DEFAULT false,
    `odometer_reading` INTEGER NOT NULL,
    `previous_owners` VARCHAR(10) NOT NULL,
    `insurance_status` ENUM('ACTIVE', 'EXPIRED') NOT NULL,
    `insurance_expiry_date` DATE NULL,
    `puc_valid_till` DATE NULL,
    `road_tax_paid_until` DATE NULL,
    `registration_type` ENUM('PRIVATE', 'COMMERCIAL') NOT NULL,
    `running_condition` ENUM('YES', 'NO') NOT NULL,
    `accident_history` ENUM('YES', 'NO') NOT NULL,
    `flood_damage` ENUM('YES', 'NO') NOT NULL,
    `fire_damage` ENUM('YES', 'NO') NOT NULL,
    `major_repairs` ENUM('YES', 'NO') NOT NULL,
    `service_history_available` ENUM('YES', 'NO') NOT NULL,
    `last_service_date` DATE NULL,
    `non_smoker_vehicle` ENUM('YES', 'NO') NOT NULL,
    `warranty_available` ENUM('YES', 'NO') NOT NULL,
    `price` DECIMAL(12, 2) NOT NULL,
    `negotiable` BOOLEAN NOT NULL DEFAULT false,
    `seller_name` VARCHAR(120) NOT NULL,
    `seller_phone` VARCHAR(15) NOT NULL,
    `seller_email` VARCHAR(120) NOT NULL,
    `seller_city` VARCHAR(80) NOT NULL,
    `seller_state` VARCHAR(80) NOT NULL,
    `seller_type` ENUM('INDIVIDUAL', 'DEALER') NOT NULL,
    `same_as_seller_info` BOOLEAN NOT NULL DEFAULT true,
    `location_state` VARCHAR(80) NOT NULL,
    `location_city` VARCHAR(80) NOT NULL,
    `area_locality` VARCHAR(120) NOT NULL,
    `pincode` VARCHAR(10) NOT NULL,
    `map_link` VARCHAR(500) NULL,
    `contact_preference` ENUM('CHAT', 'PHONE', 'BOTH') NOT NULL,
    `deleted_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

CREATE TABLE `car_listing_photos` (
    `id` VARCHAR(191) NOT NULL,
    `listing_id` VARCHAR(191) NOT NULL,
    `slot_id` VARCHAR(40) NOT NULL,
    `label` VARCHAR(80) NOT NULL,
    `image_url` VARCHAR(500) NOT NULL,
    `sort_order` INTEGER NOT NULL DEFAULT 0,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `car_listing_photos_listing_id_slot_id_key`(`listing_id`, `slot_id`),
    INDEX `car_listing_photos_listing_id_idx`(`listing_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `car_listing_documents` (
    `id` VARCHAR(191) NOT NULL,
    `listing_id` VARCHAR(191) NOT NULL,
    `document_type` ENUM('RC_BOOK', 'INSURANCE', 'PUC_CERTIFICATE', 'SERVICE_RECORDS', 'LOAN_NOC') NOT NULL,
    `file_url` VARCHAR(500) NOT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `share_with_buyers` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `car_listing_documents_listing_id_document_type_key`(`listing_id`, `document_type`),
    INDEX `car_listing_documents_listing_id_idx`(`listing_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `car_listing_features` (
    `listing_id` VARCHAR(191) NOT NULL,
    `feature_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`listing_id`, `feature_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX `car_listings_user_id_deleted_at_idx` ON `car_listings`(`user_id`, `deleted_at`);
CREATE INDEX `car_listings_status_deleted_at_idx` ON `car_listings`(`status`, `deleted_at`);
CREATE INDEX `car_listings_brand_id_idx` ON `car_listings`(`brand_id`);
CREATE INDEX `car_listings_model_id_idx` ON `car_listings`(`model_id`);

ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_approved_by_id_fkey` FOREIGN KEY (`approved_by_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_brand_id_fkey` FOREIGN KEY (`brand_id`) REFERENCES `brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_model_id_fkey` FOREIGN KEY (`model_id`) REFERENCES `car_models`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_variant_id_fkey` FOREIGN KEY (`variant_id`) REFERENCES `car_variants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_fuel_type_id_fkey` FOREIGN KEY (`fuel_type_id`) REFERENCES `fuel_types`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_transmission_id_fkey` FOREIGN KEY (`transmission_id`) REFERENCES `transmissions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `car_listings` ADD CONSTRAINT `car_listings_color_id_fkey` FOREIGN KEY (`color_id`) REFERENCES `colors`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `car_listing_photos` ADD CONSTRAINT `car_listing_photos_listing_id_fkey` FOREIGN KEY (`listing_id`) REFERENCES `car_listings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `car_listing_documents` ADD CONSTRAINT `car_listing_documents_listing_id_fkey` FOREIGN KEY (`listing_id`) REFERENCES `car_listings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `car_listing_features` ADD CONSTRAINT `car_listing_features_listing_id_fkey` FOREIGN KEY (`listing_id`) REFERENCES `car_listings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `car_listing_features` ADD CONSTRAINT `car_listing_features_feature_id_fkey` FOREIGN KEY (`feature_id`) REFERENCES `car_features`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;