-- Add migration script here
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 数据入库记录表
CREATE TABLE IF NOT EXISTS data_ingestion (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ingestion_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data_time TIMESTAMP NOT NULL,
    source VARCHAR(25) NOT NULL,
    storage_url TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加字段注释
COMMENT ON COLUMN data_ingestion.id IS '数据唯一标识符';
COMMENT ON COLUMN data_ingestion.ingestion_time IS '入库时间';
COMMENT ON COLUMN data_ingestion.data_time IS '数据表示的时间';
COMMENT ON COLUMN data_ingestion.source IS '存放源/数据来源';
COMMENT ON COLUMN data_ingestion.storage_url IS '存放URL或路径';

-- 为快速查找创建索引
CREATE INDEX IF NOT EXISTS idx_data_ingestion_ingestion_time ON data_ingestion(ingestion_time);
CREATE INDEX IF NOT EXISTS idx_data_ingestion_id_ingestion_time ON data_ingestion(id, ingestion_time);
CREATE INDEX IF NOT EXISTS idx_data_ingestion_data_time ON data_ingestion(data_time);
CREATE INDEX IF NOT EXISTS idx_data_ingestion_source ON data_ingestion(source);

-- 可选的复合索引（根据实际查询需求启用）
-- 如果经常按时间范围+来源查询，取消注释下面的索引
-- CREATE INDEX IF NOT EXISTS idx_data_ingestion_data_time_source ON data_ingestion(data_time, source);
-- CREATE INDEX IF NOT EXISTS idx_data_ingestion_source_data_time ON data_ingestion(source, data_time);

-- 创建更新时间自动更新的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为表创建更新时间触发器
CREATE TRIGGER update_data_ingestion_updated_at 
    BEFORE UPDATE ON data_ingestion 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- 创建用于查询的视图（可选）
CREATE OR REPLACE VIEW v_data_ingestion_summary AS
SELECT 
    id,
    source,
    COUNT(*) as record_count,
    MIN(data_time) as earliest_data_time,
    MAX(data_time) as latest_data_time,
    MIN(ingestion_time) as first_ingestion_time,
    MAX(ingestion_time) as last_ingestion_time
FROM data_ingestion
GROUP BY id, source;