-- ============================================================
-- CMDB Schema v1.0 — PostgreSQL
-- ============================================================
-- Strategy: soft delete everywhere, audit trail via triggers,
-- JSONB for extensible attributes, full indexing.
-- ============================================================

BEGIN;

-- -----------------------------------------------------------
-- 0. Extensions
-- -----------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- -----------------------------------------------------------
-- 1. Custom ENUM types
-- -----------------------------------------------------------
CREATE TYPE ci_status AS ENUM (
    'active', 'inactive', 'maintenance', 'deprecated', 'planned'
);

CREATE TYPE relationship_type AS ENUM (
    'depends_on', 'connected_to', 'hosted_on', 'runs_on',
    'manages', 'contains', 'part_of', 'related_to'
);

CREATE TYPE change_action AS ENUM (
    'create', 'update', 'delete', 'restore', 'relationship_add', 'relationship_remove'
);

CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');

-- -----------------------------------------------------------
-- 2. Locations
-- -----------------------------------------------------------
CREATE TABLE locations (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        TEXT NOT NULL UNIQUE,
    description TEXT,
    parent_id   UUID REFERENCES locations(id) ON DELETE SET NULL,
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ
);

CREATE INDEX idx_locations_parent ON locations(parent_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_locations_metadata ON locations USING gin(metadata);

-- -----------------------------------------------------------
-- 3. Users / Teams (owners)
-- -----------------------------------------------------------
CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username    TEXT NOT NULL UNIQUE,
    email       TEXT NOT NULL UNIQUE,
    full_name   TEXT,
    role        user_role NOT NULL DEFAULT 'viewer',
    team        TEXT,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    password_hash TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ
);

CREATE INDEX idx_users_role ON users(role) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_team ON users(team) WHERE deleted_at IS NULL AND team IS NOT NULL;

-- -----------------------------------------------------------
-- 4. CI Classes (taxonomy)
-- -----------------------------------------------------------
CREATE TABLE ci_classes (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        TEXT NOT NULL UNIQUE,          -- e.g. 'Server', 'NetworkDevice', 'Service'
    description TEXT,
    parent_id   UUID REFERENCES ci_classes(id) ON DELETE SET NULL,
    icon        TEXT,                           -- optional icon name for UI
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ
);

CREATE INDEX idx_ci_classes_parent ON ci_classes(parent_id) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 5. CI Types (subtypes within a class)
-- -----------------------------------------------------------
CREATE TABLE ci_types (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    class_id    UUID NOT NULL REFERENCES ci_classes(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,                  -- e.g. 'PhysicalServer', 'VirtualMachine', 'Switch'
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ,
    UNIQUE(class_id, name)
);

CREATE INDEX idx_ci_types_class ON ci_types(class_id) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 6. Attribute definitions (template per class)
-- -----------------------------------------------------------
CREATE TABLE attributes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_class_id     UUID NOT NULL REFERENCES ci_classes(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,              -- e.g. 'cpu_cores', 'ram_gb'
    label           TEXT NOT NULL,              -- display label
    data_type       TEXT NOT NULL DEFAULT 'text', -- text, integer, float, boolean, date, ip, json
    is_required     BOOLEAN NOT NULL DEFAULT false,
    is_unique       BOOLEAN NOT NULL DEFAULT false,
    default_value   TEXT,
    validation_regex TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,
    UNIQUE(ci_class_id, name)
);

CREATE INDEX idx_attributes_class ON attributes(ci_class_id) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 7. Owners (many-to-many: CI ↔ User)
-- -----------------------------------------------------------
CREATE TABLE owners (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id       UUID NOT NULL,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role        TEXT NOT NULL DEFAULT 'owner', -- owner, admin, responsible
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(ci_id, user_id, role)
);

-- -----------------------------------------------------------
-- 8. Configuration Items (the core entity)
-- -----------------------------------------------------------
CREATE TABLE configuration_items (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_type_id      UUID NOT NULL REFERENCES ci_types(id) ON DELETE RESTRICT,
    name            TEXT NOT NULL,
    description     TEXT,
    status          ci_status NOT NULL DEFAULT 'active',
    location_id     UUID REFERENCES locations(id) ON DELETE SET NULL,
    serial_number   TEXT,
    asset_tag       TEXT,
    purchase_date   DATE,
    warranty_expiry DATE,
    attributes      JSONB DEFAULT '{}',        -- extensible key-value store
    tags            TEXT[] DEFAULT '{}',
    created_by      UUID REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,
    version         INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_ci_type ON configuration_items(ci_type_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_ci_status ON configuration_items(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_ci_location ON configuration_items(location_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_ci_name ON configuration_items(name) WHERE deleted_at IS NULL;
CREATE INDEX idx_ci_serial ON configuration_items(serial_number) WHERE deleted_at IS NULL AND serial_number IS NOT NULL;
CREATE INDEX idx_ci_asset_tag ON configuration_items(asset_tag) WHERE deleted_at IS NULL AND asset_tag IS NOT NULL;
CREATE INDEX idx_ci_tags ON configuration_items USING gin(tags) WHERE deleted_at IS NULL;
CREATE INDEX idx_ci_attributes ON configuration_items USING gin(attributes) WHERE deleted_at IS NULL;

-- Unique constraint on name + type for active items
CREATE UNIQUE INDEX idx_ci_name_type_active
    ON configuration_items(name, ci_type_id)
    WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 9. IP Addresses
-- -----------------------------------------------------------
CREATE TABLE ip_addresses (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id           UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    ip_address      INET NOT NULL,
    subnet_mask     INET,
    gateway         INET,
    dns_servers     INET[],
    is_primary      BOOLEAN NOT NULL DEFAULT false,
    vlan_id         INTEGER,
    dhcp_enabled    BOOLEAN DEFAULT false,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

CREATE INDEX idx_ip_address ON ip_addresses(ip_address) WHERE deleted_at IS NULL;
CREATE INDEX idx_ip_ci ON ip_addresses(ci_id) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 10. Network Interfaces
-- -----------------------------------------------------------
CREATE TABLE network_interfaces (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id           UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,              -- eth0, ens192, bond0
    mac_address     MACADDR,
    speed_mbps      INTEGER,
    interface_type  TEXT DEFAULT 'ethernet',    -- ethernet, bond, bridge, vlan
    is_up           BOOLEAN DEFAULT true,
    ip_address_id   UUID REFERENCES ip_addresses(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

CREATE INDEX idx_nic_ci ON network_interfaces(ci_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_nic_mac ON network_interfaces(mac_address) WHERE deleted_at IS NULL AND mac_address IS NOT NULL;

-- -----------------------------------------------------------
-- 11. Hardware Details
-- -----------------------------------------------------------
CREATE TABLE hardware_details (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id           UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    manufacturer    TEXT,
    model           TEXT,
    cpu_model       TEXT,
    cpu_cores       INTEGER,
    ram_gb          NUMERIC(10,2),
    storage_gb      NUMERIC(10,2),
    storage_type    TEXT,                       -- SSD, HDD, NVMe
    form_factor     TEXT,                       -- 1U, 2U,塔式, 刀片
    power_supply    TEXT,
    bios_version    TEXT,
    serial_number   TEXT,
    specs           JSONB DEFAULT '{}',         -- extra hardware specs
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_hw_ci ON hardware_details(ci_id) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 12. Software Instances
-- -----------------------------------------------------------
CREATE TABLE software_instances (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id           UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    vendor          TEXT,
    version         TEXT,
    license_key     TEXT,
    license_type    TEXT,                       -- open_source, commercial, trial
    install_path    TEXT,
    config_path     TEXT,
    port            INTEGER,
    protocol        TEXT,                       -- tcp, udp
    start_command   TEXT,
    service_user    TEXT,
    config          JSONB DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

CREATE INDEX idx_sw_ci ON software_instances(ci_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_sw_name ON software_instances(name) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 13. CI Relationships (graph edges)
-- -----------------------------------------------------------
CREATE TABLE ci_relationships (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source_ci_id    UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    target_ci_id    UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    relationship    relationship_type NOT NULL,
    description     TEXT,
    metadata        JSONB DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,
    UNIQUE(source_ci_id, target_ci_id, relationship)
);

CREATE INDEX idx_rel_source ON ci_relationships(source_ci_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_rel_target ON ci_relationships(target_ci_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_rel_type ON ci_relationships(relationship) WHERE deleted_at IS NULL;

-- -----------------------------------------------------------
-- 14. Change Log (audit trail / versioning)
-- -----------------------------------------------------------
CREATE TABLE changelog (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ci_id           UUID NOT NULL REFERENCES configuration_items(id) ON DELETE CASCADE,
    action          change_action NOT NULL,
    changed_by      UUID REFERENCES users(id),
    field_name      TEXT,
    old_value       TEXT,
    new_value       TEXT,
    snapshot        JSONB,                      -- full CI snapshot at time of change
    version         INTEGER NOT NULL,
    comment         TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_changelog_ci ON changelog(ci_id);
CREATE INDEX idx_changelog_action ON changelog(action);
CREATE INDEX idx_changelog_created ON changelog(created_at);
CREATE INDEX idx_changelog_version ON changelog(ci_id, version);

-- -----------------------------------------------------------
-- 15. Audit Trigger Function
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO changelog (ci_id, action, field_name, old_value, new_value, version, snapshot)
        VALUES (
            NEW.id,
            'update',
            'general',
            row_to_json(OLD)::text,
            row_to_json(NEW)::text,
            NEW.version,
            to_jsonb(NEW)
        );
        NEW.updated_at = now();
        NEW.version = OLD.version + 1;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO changelog (ci_id, action, snapshot, version)
        VALUES (
            OLD.id,
            'delete',
            to_jsonb(OLD),
            OLD.version
        );
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ci_audit
    AFTER UPDATE OR DELETE ON configuration_items
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_func();

-- -----------------------------------------------------------
-- 16. Updated_at auto-trigger
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_locations_updated
    BEFORE UPDATE ON locations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_users_updated
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_ci_classes_updated
    BEFORE UPDATE ON ci_classes
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_ci_types_updated
    BEFORE UPDATE ON ci_types
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_ip_updated
    BEFORE UPDATE ON ip_addresses
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_nic_updated
    BEFORE UPDATE ON network_interfaces
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_hw_updated
    BEFORE UPDATE ON hardware_details
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_sw_updated
    BEFORE UPDATE ON software_instances
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

COMMIT;