`scripts/setup-search-fixed.sql`

163 lines
-- Setup Full-Text Search for LUXVAULT Database
-- This script creates comprehensive search capabilities for all data

-- Create Full-Text Search virtual table for listings
CREATE VIRTUAL TABLE IF NOT EXISTS listings_fts USING fts5(
    external_id,
    source,
    title,
    brand,
    model,
    condition,
    location,
    seller_name,
    description,
    content=listings,
    content_rowid=id
);

-- Populate the FTS table with existing data
INSERT OR REPLACE INTO listings_fts (
    external_id, source, title, brand, model, 
    condition, location, seller_name, description
)
SELECT 
    external_id, source, title, brand, model,
    condition, location, seller_name, description
FROM listings;

-- Create triggers to keep FTS in sync
CREATE TRIGGER IF NOT EXISTS listings_ai 
AFTER INSERT ON listings 
BEGIN
    INSERT INTO listings_fts (
        external_id, source, title, brand, model,
        condition, location, seller_name, description
    ) VALUES (
        new.external_id, new.source, new.title, new.brand, new.model,
        new.condition, new.location, new.seller_name, new.description
    );
END;

CREATE TRIGGER IF NOT EXISTS listings_ad 
AFTER DELETE ON listings 
BEGIN
    DELETE FROM listings_fts WHERE rowid = old.id;
END;

CREATE TRIGGER IF NOT EXISTS listings_au 
AFTER UPDATE ON listings 
BEGIN
    UPDATE listings_fts SET
        external_id = new.external_id,
        source = new.source,
        title = new.title,
        brand = new.brand,
        model = new.model,
        condition = new.condition,
        location = new.location,
        seller_name = new.seller_name,
        description = new.description
    WHERE rowid = new.id;
END;

-- Create comprehensive indexes for fast searching
CREATE INDEX IF NOT EXISTS idx_listings_brand_model ON listings(brand, model);
CREATE INDEX IF NOT EXISTS idx_listings_price_range ON listings(price_usd, price_jpy);
CREATE INDEX IF NOT EXISTS idx_listings_condition ON listings(condition);
CREATE INDEX IF NOT EXISTS idx_listings_source ON listings(source);
CREATE INDEX IF NOT EXISTS idx_listings_seller ON listings(seller_name);
CREATE INDEX IF NOT EXISTS idx_listings_location ON listings(location);
CREATE INDEX IF NOT EXISTS idx_listings_title ON listings(title);
CREATE INDEX IF NOT EXISTS idx_listings_description ON listings(description);

-- Create a view for easy searching with all data
CREATE VIEW IF NOT EXISTS searchable_listings AS
SELECT 
    l.id,
    l.external_id,
    l.source,
    l.title,
    l.brand,
    l.model,
    l.price_jpy,
    l.price_usd,
    l.condition,
    l.image_url,
    l.thumbnail_url,
    l.product_url,
    l.listing_type,
    l.auction_end_date,
    l.location,
    l.seller_name,
    l.description,
    l.crawled_at,
    da.deal_percentage,
    da.avg_us_price,
    da.is_deal,
    CASE 
        WHEN l.image_url IS NOT NULL THEN 'Yes'
        ELSE 'No'
    END as has_image,
    CASE 
        WHEN l.title LIKE '%birkin%' OR l.title LIKE '%バーキン%' THEN 'Birkin'
        WHEN l.title LIKE '%kelly%' OR l.title LIKE '%ケリー%' THEN 'Kelly'
        WHEN l.title LIKE '%constance%' OR l.title LIKE '%コンスタンス%' THEN 'Constance'
        WHEN l.title LIKE '%neverfull%' OR l.title LIKE '%ネヴァーフル%' THEN 'Neverfull'
        WHEN l.title LIKE '%speedy%' OR l.title LIKE '%スピーディ%' THEN 'Speedy'
        WHEN l.title LIKE '%classic flap%' OR l.title LIKE '%クラシック%' THEN 'Classic Flap'
        WHEN l.model IS NOT NULL THEN l.model
        ELSE 'Other'
    END as detected_model,
    CASE 
        WHEN l.title LIKE '%black%' OR l.title LIKE '%ブラック%' OR l.title LIKE '%黒%' THEN 'Black'
        WHEN l.title LIKE '%brown%' OR l.title LIKE '%ブラウン%' OR l.title LIKE '%茶%' THEN 'Brown'
        WHEN l.title LIKE '%red%' OR l.title LIKE '%レッド%' OR l.title LIKE '%赤%' THEN 'Red'
        WHEN l.title LIKE '%blue%' OR l.title LIKE '%ブルー%' OR l.title LIKE '%青%' THEN 'Blue'
        WHEN l.title LIKE '%white%' OR l.title LIKE '%ホワイト%' OR l.title LIKE '%白%' THEN 'White'
        WHEN l.title LIKE '%pink%' OR l.title LIKE '%ピンク%' THEN 'Pink'
        WHEN l.title LIKE '%gold%' OR l.title LIKE '%ゴールド%' OR l.title LIKE '%金%' THEN 'Gold'
        ELSE NULL
    END as detected_color,
    CASE 
        WHEN l.title LIKE '%leather%' OR l.title LIKE '%レザー%' OR l.title LIKE '%革%' THEN 'Leather'
        WHEN l.title LIKE '%canvas%' OR l.title LIKE '%キャンバス%' THEN 'Canvas'
        WHEN l.title LIKE '%suede%' OR l.title LIKE '%スエード%' THEN 'Suede'
        WHEN l.title LIKE '%crocodile%' OR l.title LIKE '%クロコダイル%' THEN 'Crocodile'
        WHEN l.title LIKE '%python%' OR l.title LIKE '%パイソン%' THEN 'Python'
        ELSE NULL
    END as detected_material
FROM listings l
LEFT JOIN deal_analysis da ON l.id = da.listing_id;

-- Add metadata table for tracking searches
CREATE TABLE IF NOT EXISTS search_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    search_query TEXT NOT NULL,
    results_count INTEGER,
    search_type TEXT,
    searched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a table for image metadata
CREATE TABLE IF NOT EXISTS image_metadata (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    listing_id INTEGER,
    image_url TEXT,
    thumbnail_url TEXT,
    image_hash TEXT,
    image_size INTEGER,
    has_logo INTEGER DEFAULT 0,
    primary_color TEXT,
    indexed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
);

-- Index for image searching
CREATE INDEX IF NOT EXISTS idx_image_url ON image_metadata(image_url);
CREATE INDEX IF NOT EXISTS idx_image_hash ON image_metadata(image_hash);
CREATE INDEX IF NOT EXISTS idx_image_color ON image_metadata(primary_color);

-- Analyze tables for query optimization
ANALYZE listings;
ANALYZE listings_fts;