`scripts/setup-search.sql`

146 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,
    size,
    color,
    material,
    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,
    size, color, material
)
SELECT 
    external_id, source, title, brand, model,
    condition, location, seller_name, description,
    size, color, material
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,
        size, color, material
    ) VALUES (
        new.external_id, new.source, new.title, new.brand, new.model,
        new.condition, new.location, new.seller_name, new.description,
        new.size, new.color, new.material
    );
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,
        size = new.size,
        color = new.color,
        material = new.material
    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_crawled ON listings(crawled_at);
CREATE INDEX IF NOT EXISTS idx_listings_size ON listings(size);
CREATE INDEX IF NOT EXISTS idx_listings_color ON listings(color);
CREATE INDEX IF NOT EXISTS idx_listings_material ON listings(material);
CREATE INDEX IF NOT EXISTS idx_listings_seller ON listings(seller_name);
CREATE INDEX IF NOT EXISTS idx_listings_location ON listings(location);

-- 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.size,
    l.color,
    l.material,
    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
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);