CREATE EXTENSION IF NOT EXISTS vector;

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

DROP TABLE IF EXISTS chat_messages CASCADE;   -- took 5.7ms
DROP TABLE IF EXISTS chat_sessions CASCADE;   -- took 2.7ms
DROP TABLE IF EXISTS text_vectors CASCADE;    -- took 3.7ms
DROP TABLE IF EXISTS image_vectors CASCADE;   -- took 3.4ms

CREATE TABLE image_vectors (
    id              BIGSERIAL PRIMARY KEY,
    image_id        VARCHAR(64) NOT NULL UNIQUE,
    image_url       TEXT NOT NULL,
    thumbnail_url   TEXT,
    embedding       vector(768) NOT NULL,     -- generated by large model, 768-dimensional output
    category        VARCHAR(100),
    tags            TEXT[],
    source          VARCHAR(50) DEFAULT 'upload',
    metadata        JSONB DEFAULT '{}',
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE image_vectors IS 'image vector table - intelligent customer service image search';
COMMENT ON COLUMN image_vectors.embedding IS '768-dimensional image vector generated by large model';

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'image_vectors'
ORDER BY ordinal_position;

CREATE TABLE text_vectors (
    id              BIGSERIAL PRIMARY KEY,
    doc_id          VARCHAR(64) NOT NULL UNIQUE,
    title           TEXT,
    content         TEXT NOT NULL,
    content_type    VARCHAR(20) DEFAULT 'faq',
    embedding       vector(1024) NOT NULL,    -- 1024-dimensional output generated by large model
    category        VARCHAR(100),
    tags            TEXT[],
    source          VARCHAR(100),
    metadata        JSONB DEFAULT '{}',
    priority        SMALLINT DEFAULT 0,
    status          SMALLINT DEFAULT 1,
    expire_at       TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE text_vectors IS 'text vector table - intelligent customer service knowledge base search';
COMMENT ON COLUMN text_vectors.embedding IS '1024-dimensional text vector generated by large model';

CREATE TABLE chat_sessions (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64) NOT NULL UNIQUE,
    user_id         VARCHAR(64) NOT NULL,
    channel         VARCHAR(20) DEFAULT 'web',
    status          VARCHAR(20) DEFAULT 'active',
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    closed_at       TIMESTAMPTZ
);

CREATE TABLE chat_messages (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64) NOT NULL,
    role            VARCHAR(10) NOT NULL,
    content_type    VARCHAR(20) DEFAULT 'text',
    content         TEXT NOT NULL,
    image_url       TEXT,
    embedding       vector(1024),             -- 1024-dimensional vector used for similar question recommendation
    matched_doc_ids TEXT[],
    confidence      REAL,
    feedback        SMALLINT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE chat_messages IS 'chat messages table - containing vector field used for similar question recommendation';

SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')
ORDER BY table_name;

-- Image classification index (partial index, only records with status=1)
CREATE INDEX idx_image_vectors_category ON image_vectors (category) WHERE status = 1;

-- Image Tag GIN index
CREATE INDEX idx_image_vectors_tags ON image_vectors USING GIN (tags) WHERE status = 1;

-- Image metadata JSONB GIN index
CREATE INDEX idx_image_vectors_metadata ON image_vectors USING GIN (metadata);

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')
ORDER BY tablename, indexname;

INSERT INTO text_vectors (doc_id, title, content, content_type, embedding, category, tags, priority)
VALUES (
    'faq-001',
    'How to reset password',
    'You can reset your password by following these steps: 1. Click the "forgot password" link on the login page; 2. Input your registered email;
     3. Check your inbox for the reset email; 4. Click the link in the email to set a new password.',
    'faq',
    (SELECT array_agg(random())::vector(1024) FROM generate_series(1, 1024)),
    'account management',
    ARRAY['account management', 'faq'],
    2
);

SELECT doc_id, title, category, priority, status,
       LEFT(content, 40) || '...' AS content_preview
FROM text_vectors
ORDER BY priority DESC, doc_id;

INSERT INTO image_vectors (image_id, image_url, embedding, category, tags, metadata)
VALUES (
    'img-001',
    'https://cdn.example.com/products/phone_x1.jpg',
    (SELECT array_agg(random())::vector(768) FROM generate_series(1, 768)),
    Product Images
    ARRAY['Product Images', 'smart_cs'],
    '{"width":800,"height":600,"format":"jpg"}'::jsonb
);

INSERT INTO chat_sessions (session_id, user_id, channel, status)
VALUES
    ('sess-001', 'user-1001', 'web', 'active'),
    ('sess-002', 'user-1002', 'wechat', 'active'),
    ('sess-003', 'user-1003', 'app', 'closed');

ANALYZE image_vectors;    -- took 2.8ms
ANALYZE text_vectors;     -- took 2.1ms
ANALYZE chat_sessions;    -- took 1.8ms
ANALYZE chat_messages;    -- took 1.9ms

SELECT
    'image_vectors' AS table_name, count(*) AS row_count FROM image_vectors
UNION ALL SELECT
    'text_vectors', count(*) FROM text_vectors
UNION ALL SELECT
    'chat_sessions', count(*) FROM chat_sessions
UNION ALL SELECT
    'chat_messages', count(*) FROM chat_messages
ORDER BY table_name;

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

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

CREATE INDEX idx_chat_messages_embedding_cosine
ON chat_messages
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SELECT
    indexname AS index_name,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%embedding_cosine'
ORDER BY indexname;

-- Set the search candidate list size (the larger the value, the more precise the search, recommended 100 ~ 200)
SET hnsw.ef_search = 100;

-- Enable iterative scan mode (to address the issue of insufficient results during WHERE filtering)
SET hnsw.iterative_scan = relaxed_order;

SHOW hnsw.ef_search;

# pgvector HNSW search parameters
hnsw.ef_search = 100
hnsw.iterative_scan = relaxed_order

SELECT
    iv.image_id,
    iv.image_url,
    iv.category,
    iv.tags,
    1 - (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001'))
        AS similarity
FROM image_vectors iv
WHERE iv.status = 1
ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')
LIMIT 5;

SELECT
    iv.image_id,
    iv.image_url,
    iv.category,
    1 - (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004'))
        AS similarity
FROM image_vectors iv
WHERE iv.status = 1
  AND iv.category = 'Product Images'
ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004')
LIMIT 5;