-- Network wall outlets attached to buildings, floors, or rooms.
CREATE TABLE IF NOT EXISTS locations_wall_outlets (
    id SERIAL PRIMARY KEY,
    location_id INTEGER NOT NULL REFERENCES locations_locations(id) ON DELETE CASCADE,
    outlet_number VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    patch_panel VARCHAR(255),
    patch_port VARCHAR(100),
    switch_name VARCHAR(255),
    switch_port VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'unknown'
        CHECK (status IN ('available', 'active', 'reserved', 'faulty', 'unknown')),
    notes TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_locations_wall_outlets_unique_location_number
    ON locations_wall_outlets(location_id, lower(outlet_number))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_locations_wall_outlets_location ON locations_wall_outlets(location_id);
CREATE INDEX IF NOT EXISTS idx_locations_wall_outlets_status ON locations_wall_outlets(status) WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION update_locations_wall_outlets_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_locations_wall_outlets_updated_at ON locations_wall_outlets;
CREATE TRIGGER trg_locations_wall_outlets_updated_at
    BEFORE UPDATE ON locations_wall_outlets
    FOR EACH ROW EXECUTE FUNCTION update_locations_wall_outlets_updated_at();