-- OCDP Backend PostgreSQL 数据库初始化脚本
-- 创建数据库和必要的表结构

-- ===== Users 表 =====
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    email VARCHAR(255) NOT NULL,
    revoked_after TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_revoked_after ON users(revoked_after);

COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户 ID (UUID)';
COMMENT ON COLUMN users.username IS '用户名（唯一）';
COMMENT ON COLUMN users.password_hash IS '密码哈希';
COMMENT ON COLUMN users.email IS '邮箱';

-- ===== Clusters 表 =====
CREATE TABLE IF NOT EXISTS clusters (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    host TEXT NOT NULL,
    ca_data TEXT,
    cert_data TEXT,
    key_data TEXT,
    token TEXT,
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_clusters_name ON clusters(name);

COMMENT ON TABLE clusters IS 'Kubernetes 集群表';
COMMENT ON COLUMN clusters.id IS '集群 ID (UUID)';
COMMENT ON COLUMN clusters.name IS '集群名称（唯一）';
COMMENT ON COLUMN clusters.host IS 'Kubernetes API Server URL';
COMMENT ON COLUMN clusters.ca_data IS 'CA 证书（加密存储）';
COMMENT ON COLUMN clusters.cert_data IS '客户端证书（加密存储）';
COMMENT ON COLUMN clusters.key_data IS '客户端密钥（加密存储）';
COMMENT ON COLUMN clusters.token IS 'Bearer Token（加密存储）';

-- ===== Registries 表 =====
CREATE TABLE IF NOT EXISTS registries (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    url TEXT NOT NULL,
    description TEXT,
    username VARCHAR(255),
    password TEXT,
    insecure BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_registries_name ON registries(name);

COMMENT ON TABLE registries IS 'OCI Registry 表';
COMMENT ON COLUMN registries.id IS 'Registry ID (UUID)';
COMMENT ON COLUMN registries.name IS 'Registry 名称（唯一）';
COMMENT ON COLUMN registries.url IS 'Registry URL';
COMMENT ON COLUMN registries.username IS '认证用户名';
COMMENT ON COLUMN registries.password IS '认证密码（加密存储）';
COMMENT ON COLUMN registries.insecure IS '是否跳过 TLS 验证';

-- ===== Instances 表 =====
CREATE TABLE IF NOT EXISTS instances (
    id VARCHAR(36) PRIMARY KEY,
    cluster_id VARCHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    namespace VARCHAR(255) NOT NULL,
    registry_id VARCHAR(36) NOT NULL,
    repository TEXT NOT NULL,
    chart VARCHAR(255) NOT NULL,
    version VARCHAR(255) NOT NULL,
    description TEXT,
    values JSONB,
    values_yaml TEXT,
    status VARCHAR(50) NOT NULL,
    status_reason TEXT,
    last_operation VARCHAR(50),
    last_error TEXT,
    revision INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cluster FOREIGN KEY (cluster_id) REFERENCES clusters(id) ON DELETE CASCADE,
    CONSTRAINT fk_registry FOREIGN KEY (registry_id) REFERENCES registries(id) ON DELETE CASCADE,
    CONSTRAINT unique_cluster_name UNIQUE (cluster_id, name, namespace)
);

CREATE INDEX IF NOT EXISTS idx_instances_cluster ON instances(cluster_id);
CREATE INDEX IF NOT EXISTS idx_instances_registry ON instances(registry_id);
CREATE INDEX IF NOT EXISTS idx_instances_name ON instances(name);
CREATE INDEX IF NOT EXISTS idx_instances_status ON instances(status);

COMMENT ON TABLE instances IS 'Helm 应用实例表';
COMMENT ON COLUMN instances.id IS '实例 ID (UUID)';
COMMENT ON COLUMN instances.cluster_id IS '所属集群 ID';
COMMENT ON COLUMN instances.name IS 'Helm Release 名称';
COMMENT ON COLUMN instances.namespace IS 'Kubernetes 命名空间';
COMMENT ON COLUMN instances.registry_id IS '所属 Registry ID';
COMMENT ON COLUMN instances.repository IS 'OCI Repository';
COMMENT ON COLUMN instances.chart IS 'Chart 名称';
COMMENT ON COLUMN instances.version IS 'Chart 版本';
COMMENT ON COLUMN instances.values IS 'Helm Values (JSON 格式)';
COMMENT ON COLUMN instances.values_yaml IS 'Helm Values (YAML 格式)';
COMMENT ON COLUMN instances.status IS '实例状态';
COMMENT ON COLUMN instances.status_reason IS '状态说明';
COMMENT ON COLUMN instances.last_operation IS '最后一次操作类型';
COMMENT ON COLUMN instances.last_error IS '最近一次错误信息';
COMMENT ON COLUMN instances.revision IS 'Helm Release Revision';

-- ===== 数据库版本表 =====
CREATE TABLE IF NOT EXISTS schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE schema_migrations IS '数据库迁移版本记录';

-- 插入初始版本
INSERT INTO schema_migrations (version) VALUES ('v1.0.0')
ON CONFLICT (version) DO NOTHING;