-- 安装 TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 创建超表（Hypertable）
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2),
    location TEXT
);

SELECT create_hypertable('sensor_data', 'time');

-- 插入数据
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location) VALUES
    ('2024-03-15 10:00:00+08', 1, 25.5, 60.2, 'warehouse_a'),
    ('2024-03-15 10:01:00+08', 1, 25.6, 60.1, 'warehouse_a'),
    ('2024-03-15 10:00:00+08', 2, 23.1, 55.8, 'warehouse_b');

-- 创建索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC);

-- 时间范围查询
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature)::NUMERIC(5,2) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
WHERE time >= '2024-03-15' AND time < '2024-03-16'
GROUP BY hour, sensor_id
ORDER BY hour;

-- 连续聚合（Continuous Aggregate）
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature)::NUMERIC(5,2) AS avg_temp,
    AVG(humidity)::NUMERIC(5,2) AS avg_humidity
FROM sensor_data
GROUP BY hour, sensor_id;

-- 自动刷新策略
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- 数据保留策略
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- 按月分区
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    metric_name TEXT NOT NULL,
    value NUMERIC
) PARTITION BY RANGE (time);

CREATE TABLE metrics_2024_03 PARTITION OF metrics
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE metrics_2024_04 PARTITION OF metrics
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- BRIN 索引（时序数据物理有序）
CREATE INDEX idx_metrics_time ON metrics USING BRIN (time);

-- 安装 PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;

-- 创建 POI 表
CREATE TABLE pois (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    geom GEOGRAPHY(POINT, 4326) NOT NULL,
    properties JSONB DEFAULT '{}'
);

CREATE INDEX idx_pois_geom ON pois USING GIST (geom);
CREATE INDEX idx_pois_category ON pois (category);

-- 插入数据（经度, 纬度）
INSERT INTO pois (name, category, geom) VALUES
    ('北京大学', 'university', ST_SetSRID(ST_MakePoint(116.3100, 39.9928), 4326)::GEOGRAPHY),
    ('清华大学', 'university', ST_SetSRID(ST_MakePoint(116.3268, 40.0030), 4326)::GEOGRAPHY),
    ('中关村', 'shopping', ST_SetSRID(ST_MakePoint(116.3190, 39.9830), 4326)::GEOGRAPHY),
    ('天安门', 'landmark', ST_SetSRID(ST_MakePoint(116.3975, 39.9087), 4326)::GEOGRAPHY);

-- 查找 3 公里内的 POI
SELECT
    name,
    category,
    ST_Distance(geom, ST_SetSRID(ST_MakePoint(116.3100, 39.9928), 4326)::GEOGRAPHY) AS distance_m
FROM pois
WHERE ST_DWithin(geom,
    ST_SetSRID(ST_MakePoint(116.3100, 39.9928), 4326)::GEOGRAPHY,
    3000
)
ORDER BY distance_m;

-- 最近邻查询
SELECT name, category,
    geom <-> ST_SetSRID(ST_MakePoint(116.3100, 39.9928), 4326)::GEOGRAPHY AS distance
FROM pois
ORDER BY distance
LIMIT 5;

-- 范围查询（多边形）
SELECT * FROM pois
WHERE ST_Within(geom::GEOMETRY,
    ST_GeomFromText('POLYGON((116.2 39.9, 116.4 39.9, 116.4 40.1, 116.2 40.1, 116.2 39.9))', 4326)
);

-- 基本全文搜索
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建 tsvector 列
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- 更新 tsvector
UPDATE articles SET search_vector =
    setweight(to_tsvector('simple', title), 'A') ||
    setweight(to_tsvector('simple', content), 'B');

-- GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- 搜索
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('simple', 'PostgreSQL 优化') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 高亮显示
SELECT
    title,
    ts_headline('simple', content, plainto_tsquery('simple', 'PostgreSQL'),
        'StartSel=<b>, StopSel=</b>, MaxFragments=3') AS highlighted
FROM articles
WHERE search_vector @@ plainto_tsquery('simple', 'PostgreSQL');

-- 自动更新触发器
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('simple', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_search_vector();

-- 使用 pg_trgm 模糊搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
SELECT * FROM articles WHERE title % 'postgrs';  -- 拼写错误也能匹配

-- 所有租户共享同一套表
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id INT NOT NULL,
    customer_id INT NOT NULL,
    total NUMERIC(12,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 行级安全策略
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- 查询时设置租户
SET app.tenant_id = '42';
SELECT * FROM orders;  -- 只返回 tenant_id=42 的数据

-- 每个租户一个 Schema
CREATE SCHEMA tenant_001;
CREATE SCHEMA tenant_002;

-- 在 Schema 中创建相同的表结构
CREATE TABLE tenant_001.orders (LIKE public.orders_template INCLUDING ALL);
CREATE TABLE tenant_002.orders (LIKE public.orders_template INCLUDING ALL);

-- 切换 Schema
SET search_path = tenant_001, public;
SELECT * FROM orders;  -- 查看 tenant_001 的订单