-- 安装扩展
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 查看已安装扩展
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;
\dx

-- 升级扩展
ALTER EXTENSION "pgcrypto" UPDATE;

-- 卸载扩展
DROP EXTENSION IF EXISTS "pgcrypto";

-- 安装
CREATE EXTENSION IF NOT EXISTS postgis;

-- 创建地理表
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    category TEXT
);

-- 插入数据
INSERT INTO places (name, location, category) VALUES
    ('故宫', ST_SetSRID(ST_MakePoint(116.3972, 39.9153), 4326)::GEOGRAPHY, '景点'),
    ('天安门', ST_SetSRID(ST_MakePoint(116.3975, 39.9087), 4326)::GEOGRAPHY, '景点'),
    ('颐和园', ST_SetSRID(ST_MakePoint(116.2755, 39.9999), 4326)::GEOGRAPHY, '景点'),
    ('鸟巢', ST_SetSRID(ST_MakePoint(116.3962, 39.9929), 4326)::GEOGRAPHY, '场馆');

-- 空间索引
CREATE INDEX idx_places_location ON places USING GIST (location);

-- 距离查询（米）
SELECT name, ST_Distance(location, 
    ST_SetSRID(ST_MakePoint(116.397, 39.91), 4326)::GEOGRAPHY
) AS distance_m
FROM places
ORDER BY distance_m;

-- 范围查询（5公里内）
SELECT name FROM places
WHERE ST_DWithin(location,
    ST_SetSRID(ST_MakePoint(116.397, 39.91), 4326)::GEOGRAPHY,
    5000
);

-- 几何类型操作
SELECT ST_AsText(ST_Buffer(location::GEOMETRY, 100)) FROM places WHERE name = '故宫';

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建 GIN 索引
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- 模糊搜索（支持前后缀匹配）
SELECT * FROM products WHERE name % 'laptap';  -- 相似度匹配
SELECT * FROM products WHERE name ILIKE '%laptop%';  -- 传统模糊
SELECT similarity(name, 'laptop') AS score FROM products ORDER BY score DESC;

-- 三元组匹配度
SELECT show_trgm('laptop');
-- {"  l"," la","apt","lap","opt","pat","pt ","to "}

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 对称加密
SELECT pgp_sym_encrypt('secret data', 'my_key');
SELECT pgp_sym_decrypt(encrypted_data, 'my_key');

-- 哈希
SELECT md5('hello');
SELECT sha256('hello');
SELECT crypt('password', gen_salt('bf', 12));

-- 随机数
SELECT gen_random_uuid();
SELECT encode(gen_random_bytes(16), 'hex');

-- postgres_fdw（连接其他 PG）
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote-host', dbname 'remote_db', port '5432');

CREATE USER MAPPING FOR local_user
    SERVER remote_server
    OPTIONS (user 'remote_user', password 'remote_pass');

CREATE FOREIGN TABLE remote_users (
    id INT,
    name TEXT,
    email TEXT
) SERVER remote_server OPTIONS (schema_name 'public', table_name 'users');

SELECT * FROM remote_users;

-- file_fdw（读取 CSV 文件）
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE csv_data (
    col1 TEXT, col2 TEXT, col3 TEXT
) SERVER csv_server OPTIONS (filename '/tmp/data.csv', format 'csv', header 'true');

-- pgvector 向量搜索（AI 场景热门）
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI 嵌入维度
);
CREATE INDEX idx_emb ON embeddings USING IVFFlat (embedding vector_cosine_ops);
SELECT * FROM embeddings ORDER BY embedding <=> '[0.1, 0.2, ...]'::VECTOR LIMIT 5;

-- ltree 层级路径
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    path LTREE NOT NULL,
    name TEXT
);
INSERT INTO categories (path, name) VALUES
    ('electronics', '电子产品'),
    ('electronics.computers', '电脑'),
    ('electronics.computers.laptops', '笔记本');
CREATE INDEX idx_path ON categories USING GIST (path);
SELECT * FROM categories WHERE path <@ 'electronics.computers';