-- Remove the global UNIQUE constraint on allocations.cidr so that a released
-- subnet can be re-allocated later. Enforce uniqueness only among active and
-- reserved allocations within the same pool.

CREATE TABLE IF NOT EXISTS allocations_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pool_id INTEGER NOT NULL REFERENCES pools(id),
    cidr TEXT NOT NULL,
    prefix_length INTEGER NOT NULL,
    address_count INTEGER NOT NULL,
    usable_host_count INTEGER NOT NULL,
    note TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('active', 'reserved', 'released')),
    created_at TEXT NOT NULL,
    created_by TEXT NOT NULL DEFAULT 'local',
    released_at TEXT NULL,
    released_by TEXT NULL,
    release_note TEXT NULL
);

INSERT INTO allocations_new SELECT * FROM allocations;

DROP TABLE allocations;

ALTER TABLE allocations_new RENAME TO allocations;

CREATE INDEX IF NOT EXISTS idx_allocations_pool_id ON allocations(pool_id);
CREATE INDEX IF NOT EXISTS idx_allocations_status ON allocations(status);
CREATE INDEX IF NOT EXISTS idx_allocations_pool_status ON allocations(pool_id, status);
CREATE INDEX IF NOT EXISTS idx_allocations_created_at ON allocations(created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS idx_allocations_active_unique
    ON allocations(pool_id, cidr)
    WHERE status IN ('active', 'reserved');