┌──────────────────┐     ┌───────────────────────┐     ┌────────────────────┐
│ product_catalog  │     │ product_image_search  │     │ image_search_log   │
│──────────────────│     │───────────────────────│     │────────────────────│
│ product_id (PK)  │◄────│ product_id (FK)       │     │ id (PK)            │
│ product_name     │     │ image_id (UK)         │     │ session_id         │
│ category         │     │ embedding vector(768) │     │ query_vector       │
│ brand            │     │ image_type            │     │ result_count       │
│ price            │     │ angle                 │     │ top_similarity     │
│ tags             │     │ metadata (JSONB)      │     │ search_time_ms     │
└──────────────────┘     └───────────────────────┘     └────────────────────┘

SELECT
    version() AS pg_version,
    current_database() AS database,
    current_user AS user_name,
    pg_size_pretty(pg_database_size(current_database())) AS db_size,
    inet_server_addr() AS server_ip,
    inet_server_port() AS server_port;

SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('vector', 'age', 'pg_trgm', 'pg_prewarm')
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS vector;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

DROP TABLE IF EXISTS product_image_search CASCADE;
DROP TABLE IF EXISTS product_catalog CASCADE;
DROP TABLE IF EXISTS image_search_log CASCADE;

CREATE TABLE product_catalog (
    id              BIGSERIAL PRIMARY KEY,
    product_id      VARCHAR(32) NOT NULL UNIQUE,
    product_name    TEXT NOT NULL,
    category        VARCHAR(100) NOT NULL,
    sub_category    VARCHAR(100),
    brand           VARCHAR(100),
    price           NUMERIC(10, 2),
    description     TEXT,
    tags            TEXT[],
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE product_catalog IS 'Product catalog table - storing basic product information';

CREATE TABLE product_image_search (
    id              BIGSERIAL PRIMARY KEY,
    image_id        VARCHAR(64) NOT NULL UNIQUE,
    product_id      VARCHAR(32) NOT NULL REFERENCES product_catalog(product_id),
    image_url       TEXT NOT NULL,
    thumbnail_url   TEXT,
    embedding       vector(768) NOT NULL,
    image_type      VARCHAR(20) DEFAULT 'main',
    angle           VARCHAR(20) DEFAULT 'front',
    background      VARCHAR(20) DEFAULT 'white',
    metadata        JSONB DEFAULT '{}',
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE product_image_search IS 'Product image vector table - core table for image search by image';
COMMENT ON COLUMN product_image_search.embedding IS '768-dimensional image feature vector generated by the CLIP ViT-L/14 model';
COMMENT ON COLUMN product_image_search.image_type IS 'Image type: main=main image, detail=detail image, scene=scene image';
COMMENT ON COLUMN product_image_search.angle IS 'Shooting angle: front=front view, side=side view, back=back view, top=top view';

CREATE TABLE image_search_log (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64),
    query_vector    vector(768),
    query_image_url TEXT,
    result_count    INT,
    top_similarity  REAL,
    search_time_ms  REAL,
    filter_category VARCHAR(100),
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE image_search_log IS 'Image-based search log - record search behavior and performance';

SELECT
    c.relname AS table_name,
    obj_description(c.oid) AS comment,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND c.relname IN ('product_catalog', 'product_image_search', 'image_search_log')
ORDER BY c.relname;

SELECT
    column_name,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE table_name = 'product_image_search'
  AND table_schema = 'public'
ORDER BY ordinal_position;

INSERT INTO product_catalog (product_id, product_name, category, sub_category, brand, price, description, tags, status)
SELECT
    'P' || lpad(g::text, 6, '0'),
    CASE (g % 8)
        WHEN 0 THEN 'Smartphone ' || (ARRAY['Pro','Max','Ultra','Lite','SE','Plus','Mini','Air'])[(g % 8) + 1] || ' ' || g
        WHEN 1 THEN 'Laptop ' || (ARRAY['ThinkPad','MacBook','Surface','MateBook','XPS','Yoga','ZenBook','Swift'])[(g % 8) + 1] || ' ' || g
        WHEN 2 THEN 'Sneakers ' || (ARRAY['Air Max','Ultra Boost','Gel','Fresh Foam','React','Zoom','NB','Asics'])[(g % 8) + 1] || ' ' || g
        WHEN 3 THEN 'Mechanical Keyboard ' || (ARRAY['Cherry','Filco','Leopold','HHKB','Ducky','Razer','Logitech','Keychron'])[(g % 8) + 1] || ' ' || g
        WHEN 4 THEN 'Smartwatch ' || (ARRAY['Apple Watch','Galaxy Watch','Huawei Watch','Amazfit','Garmin','Fitbit','TicWatch','Suunto'])[(g % 8) + 1] || ' ' || g
        WHEN 5 THEN 'Wireless Earbuds ' || (ARRAY['AirPods','Galaxy Buds','FreeBuds','WF-1000','WH-1000','Bose QC','Jabra','Sony'])[(g % 8) + 1] || ' ' || g
        WHEN 6 THEN 'Backpack ' || (ARRAY['Osprey','North Face','Arc','Samsonite','Tumi','Incase','Herschel','Peak'])[(g % 8) + 1] || ' ' || g
        ELSE 'Coffee Machine ' || (ARRAY['Nespresso','De Longhi','Breville','Jura','Saeco','Krups','Moccamaster','Fellow'])[(g % 8) + 1] || ' ' || g
    END,
    (ARRAY['Mobile & Digital','Computers & Office','Sports & Outdoors','Peripherals & Accessories','Wearable Devices','Audio Equipment','Luggage & Leather Goods','Home Appliances'])[(g % 8) + 1],
    -- ... (Brand/Price/Description/Tag/Status auto-generated)
FROM generate_series(1, 200) g;

SELECT
    category,
    count(*) AS product_count,
    round(avg(price), 2) AS avg_price,
    min(price) AS min_price,
    max(price) AS max_price
FROM product_catalog
WHERE status = 1
GROUP BY category
ORDER BY product_count DESC;

INSERT INTO product_image_search
    (image_id, product_id, image_url, thumbnail_url, embedding, image_type, angle, background, metadata, status)
SELECT
    'IMG-' || lpad(sub.img_idx::text, 8, '0'),
    sub.pid,
    'https://cdn.example.com/products/' || sub.pid || '/' || sub.itype || '.jpg',
    'https://cdn.example.com/products/' || sub.pid || '/' || sub.itype || '_thumb.jpg',
    -- Generate similar vectors for images of the same product (adding noise to simulate different angles)
    (
        SELECT array_agg(
            sin(sub.base_seed + i * 0.01 + sub.noise_offset) * 0.5
            + cos(sub.base_seed * 2 + i * 0.02) * 0.3
            + (random() - 0.5) * sub.noise_factor
        )::vector(768)
        FROM generate_series(1, 768) i
    ),
    sub.itype,
    sub.angle_val,
    (ARRAY['white','transparent','scene','studio'])[(sub.img_idx % 4) + 1],
    jsonb_build_object(
        'width', 800 + (sub.img_idx % 400),
        'height', 800 + (sub.img_idx % 400),
        'format', 'jpg',
        'size_kb', 50 + (sub.img_idx % 200),
        'model', 'CLIP-ViT-L/14',
        'dim', 768
    ),
    1
FROM (
    SELECT
        p.product_id AS pid,
        (p.id - 1) * 3 + t.n AS img_idx,
        CASE t.n WHEN 1 THEN 'main' WHEN 2 THEN 'detail' ELSE 'scene' END AS itype,
        CASE t.n WHEN 1 THEN 'front' WHEN 2 THEN 'side' ELSE 'top' END AS angle_val,
        (p.id * 7.13 + (p.id % 8) * 100) AS base_seed,
        t.n * 0.05 AS noise_offset,
        CASE t.n WHEN 1 THEN 0.02 WHEN 2 THEN 0.05 ELSE 0.08 END AS noise_factor
    FROM product_catalog p
    CROSS JOIN (SELECT generate_series(1, 3) AS n) t
) sub;

SELECT
    pis.image_type,
    count(*) AS image_count,
    count(DISTINCT pis.product_id) AS product_count,
    pg_size_pretty(sum(pg_column_size(pis.embedding))) AS vector_storage
FROM product_image_search pis
WHERE pis.status = 1
GROUP BY pis.image_type
ORDER BY image_count DESC;

ANALYZE product_catalog;
ANALYZE product_image_search;
ANALYZE image_search_log;

CREATE INDEX idx_product_image_embedding_cosine
ON product_image_search
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

-- product ID association index
CREATE INDEX idx_product_image_product_id ON product_image_search (product_id);

-- Image type filtering index (partial index)
CREATE INDEX idx_product_image_type ON product_image_search (image_type) WHERE status = 1;

-- Product classification index (partial index)
CREATE INDEX idx_product_catalog_category ON product_catalog (category) WHERE status = 1;

-- Brand filtering index
CREATE INDEX idx_product_catalog_brand ON product_catalog (brand) WHERE status = 1;

-- Price range index
CREATE INDEX idx_product_catalog_price ON product_catalog (price) WHERE status = 1;

-- Product Tag GIN index
CREATE INDEX idx_product_catalog_tags ON product_catalog USING GIN (tags) WHERE status = 1;

-- Image metadata GIN index
CREATE INDEX idx_product_image_metadata ON product_image_search USING GIN (metadata);

-- Search log time index
CREATE INDEX idx_search_log_created ON image_search_log (created_at DESC);

SELECT
    indexrelname AS index_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname IN ('product_image_search', 'product_catalog', 'image_search_log')
ORDER BY pg_relation_size(indexrelid) DESC;