CREATE TABLE `racket_listings` (
  `id` int AUTO_INCREMENT NOT NULL,
  `source` enum('xianyu','jd','zhuanzhuan') NOT NULL,
  `sourceListingId` varchar(128) NOT NULL,
  `title` varchar(512) NOT NULL,
  `description` text,
  `listingUrl` text NOT NULL,
  `imageUrl` text,
  `price` float NOT NULL,
  `originalPrice` float,
  `sellerName` varchar(128),
  `location` varchar(128),
  `publishedAtRaw` varchar(128),
  `brand` varchar(64),
  `model` varchar(128),
  `series` varchar(128),
  `category` varchar(64),
  `weightGram` float,
  `conditionLevel` enum('brand_new','almost_new','used_good','used_fair','unknown') NOT NULL DEFAULT 'unknown',
  `gradeLevel` enum('high_value','standard','overpriced','pending_review') NOT NULL DEFAULT 'pending_review',
  `gradeReason` text,
  `isLowPriceCandidate` int NOT NULL DEFAULT 0,
  `fingerprint` varchar(128) NOT NULL,
  `extra` json,
  `fetchedAt` timestamp NOT NULL DEFAULT (now()),
  `createdAt` timestamp NOT NULL DEFAULT (now()),
  `updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `racket_listings_id` PRIMARY KEY(`id`),
  CONSTRAINT `racket_listings_source_listing_idx` UNIQUE(`source`,`sourceListingId`),
  CONSTRAINT `racket_listings_fingerprint_idx` UNIQUE(`fingerprint`)
);
--> statement-breakpoint

CREATE TABLE `racket_watch_rules` (
  `id` int AUTO_INCREMENT NOT NULL,
  `userId` int NOT NULL,
  `title` varchar(256) NOT NULL,
  `brand` varchar(64) NOT NULL,
  `modelKeyword` varchar(128),
  `seriesKeyword` varchar(128),
  `category` varchar(64),
  `weightMinGram` float,
  `weightMaxGram` float,
  `targetPrice` float NOT NULL,
  `pushEnabled` int NOT NULL DEFAULT 1,
  `isActive` int NOT NULL DEFAULT 1,
  `lastCheckedAt` timestamp,
  `lastMatchedAt` timestamp,
  `createdAt` timestamp NOT NULL DEFAULT (now()),
  `updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `racket_watch_rules_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint

CREATE TABLE `racket_watch_hits` (
  `id` int AUTO_INCREMENT NOT NULL,
  `watchRuleId` int NOT NULL,
  `userId` int NOT NULL,
  `listingId` int NOT NULL,
  `matchedPrice` float NOT NULL,
  `status` enum('matched','push_queued','pushed','suppressed') NOT NULL DEFAULT 'matched',
  `firstMatchedAt` timestamp NOT NULL DEFAULT (now()),
  `lastMatchedAt` timestamp NOT NULL DEFAULT (now()),
  `lastPushPrice` float,
  `pushedAt` timestamp,
  `pushCount` int NOT NULL DEFAULT 0,
  `createdAt` timestamp NOT NULL DEFAULT (now()),
  `updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `racket_watch_hits_id` PRIMARY KEY(`id`),
  CONSTRAINT `racket_watch_hits_rule_listing_idx` UNIQUE(`watchRuleId`,`listingId`)
);
--> statement-breakpoint

ALTER TABLE `background_tasks`
  MODIFY COLUMN `type` enum(
    'media_finalize',
    'training_plan_generate',
    'training_plan_adjust',
    'analysis_corrections',
    'pose_correction_multimodal',
    'ntrp_refresh_user',
    'ntrp_refresh_all',
    'market_source_sync',
    'market_watch_refresh',
    'market_push_delivery'
  ) NOT NULL;
