psql.upgrade

ALTER TABLE "Slices" ADD COLUMN IF NOT EXISTS project_id VARCHAR;
ALTER TABLE "Slices" ADD COLUMN IF NOT EXISTS slc_state INTEGER;
ALTER TABLE "Reservations" ADD COLUMN IF NOT EXISTS project_id VARCHAR;
ALTER TABLE "Reservations" ADD COLUMN IF NOT EXISTS site VARCHAR;
ALTER TABLE "Reservations" ADD COLUMN IF NOT EXISTS rsv_type VARCHAR;
ALTER TABLE "Delegations" ADD COLUMN IF NOT EXISTS site VARCHAR;
UPDATE "Slices" set slc_state=2 where slc_name='broker';
CREATE SEQUENCE IF NOT EXISTS site_id start 1 increment 1;
CREATE TABLE IF NOT EXISTS "Sites" (
  site_id  INTEGER NOT NULL DEFAULT nextval('site_id') PRIMARY KEY,
  name VARCHAR NOT NULL,
  state INTEGER NOT NULL,
  properties BYTEA
);

CREATE TABLE IF NOT EXISTS "Components" (
    reservation_id INTEGER,
    component VARCHAR,
    PRIMARY KEY (reservation_id, component),
    FOREIGN KEY (reservation_id) REFERENCES "Reservations"(rsv_id)
);

ALTER TABLE "Reservations" ADD COLUMN IF NOT EXISTS components VARCHAR;


-- Add new columns with the TIMESTAMP WITH TIME ZONE data type
ALTER TABLE "Reservations" ADD COLUMN lease_start_with_tz TIMESTAMPTZ;
ALTER TABLE "Reservations" ADD COLUMN lease_end_with_tz TIMESTAMPTZ;

-- Update the new columns with data from the existing columns
UPDATE "Reservations" SET lease_start_with_tz = lease_start::TIMESTAMPTZ;
UPDATE "Reservations" SET lease_end_with_tz = lease_end::TIMESTAMPTZ;

-- Drop the existing columns
ALTER TABLE "Reservations" DROP COLUMN lease_start;
ALTER TABLE "Reservations" DROP COLUMN lease_end;

-- Rename the new columns to the original column names
ALTER TABLE "Reservations" RENAME COLUMN lease_start_with_tz TO lease_start;
ALTER TABLE "Reservations" RENAME COLUMN lease_end_with_tz TO lease_end;


ALTER TABLE "Slices" ADD COLUMN IF NOT EXISTS components VARCHAR;


-- Add new columns with the TIMESTAMP WITH TIME ZONE data type
ALTER TABLE "Slices" ADD COLUMN lease_start_with_tz TIMESTAMPTZ;
ALTER TABLE "Slices" ADD COLUMN lease_end_with_tz TIMESTAMPTZ;

-- Update the new columns with data from the existing columns
UPDATE "Slices" SET lease_start_with_tz = lease_start::TIMESTAMPTZ;
UPDATE "Slices" SET lease_end_with_tz = lease_end::TIMESTAMPTZ;

-- Drop the existing columns
ALTER TABLE "Slices" DROP COLUMN lease_start;
ALTER TABLE "Slices" DROP COLUMN lease_end;

-- Rename the new columns to the original column names
ALTER TABLE "Slices" RENAME COLUMN lease_start_with_tz TO lease_start;
ALTER TABLE "Slices" RENAME COLUMN lease_end_with_tz TO lease_end;



-- Add new columns with the TIMESTAMP WITH TIME ZONE data type
ALTER TABLE "Poas" ADD COLUMN last_update_time_with_tz TIMESTAMPTZ;

-- Update the new columns with data from the existing columns
UPDATE "Poas" SET last_update_time_with_tz = last_update_time::TIMESTAMPTZ;

-- Drop the existing columns
ALTER TABLE "Poas" DROP COLUMN last_update_time;

-- Rename the new columns to the original column names
ALTER TABLE "Poas" RENAME COLUMN last_update_time_with_tz TO last_update_time;

CREATE TABLE IF NOT EXISTS "Metrics" (
  m_id  INTEGER NOT NULL DEFAULT nextval('m_id') PRIMARY KEY,
  user_id VARCHAR NOT NULL,
  project_id VARCHAR NOT NULL,
  slice_count INTEGER NOT NULL,
);

ALTER TABLE "Reservations" ADD COLUMN host VARCHAR(255) NULL;
ALTER TABLE "Reservations" ADD COLUMN ip_subnet VARCHAR(255) NULL;

CREATE INDEX idx_host ON "Reservations"(host);
CREATE INDEX idx_ip_subnet ON "Reservations"(ip_subnet);

ALTER TABLE "Slices"
ADD COLUMN last_update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();

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

CREATE TRIGGER slices_last_update_time_trigger
BEFORE UPDATE ON "Slices"
FOR EACH ROW
EXECUTE FUNCTION update_last_update_time();

-- Create the Links table
CREATE TABLE IF NOT EXISTS "Links" (
    reservation_id INTEGER NOT NULL,
    node_id VARCHAR NOT NULL,
    layer VARCHAR,
    type VARCHAR,
    bw INTEGER,
    properties BYTEA,
    PRIMARY KEY (reservation_id, node_id),
    FOREIGN KEY (reservation_id) REFERENCES "Reservations" (rsv_id)
);

-- Create an index on node_id for faster lookups
CREATE INDEX IF NOT EXISTS ix_Links_node_id ON "Links" (node_id);

-- Add closed_at column to track when a reservation was actually closed
ALTER TABLE "Reservations" ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ;