migrations

var Migrations = mgx.Migrations(
	mgx.NewMigration("initial", func(ctx context.Context, commands mgx.Commands) error {
		if _, err := commands.Exec(ctx, `
				CREATE TABLE service_status
				(
					name VARCHAR(16) PRIMARY KEY
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				INSERT INTO
					service_status(name)
				VALUES
					('AVAILABLE'),
					('PENDING_CREATE'),
					('PENDING_UPDATE'),
					('PENDING_DELETE'),
					('UNAVAILABLE')
				;`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE service
				(
					id                UUID           DEFAULT gen_random_uuid() PRIMARY KEY,
					enabled           BOOLEAN        DEFAULT true NOT NULL,
					name              VARCHAR(64)    NOT NULL,
					description       VARCHAR(255)   NOT NULL,
					network_id        UUID           NOT NULL,
					ip_addresses      INET[]         NOT NULL,
                    port              INTEGER        NOT NULL,
					status            VARCHAR(14)    DEFAULT 'PENDING_CREATE' NOT NULL,
					require_approval  BOOLEAN        NOT NULL,
					visibility        VARCHAR(7)     NOT NULL,
					availability_zone VARCHAR(64)    NULL,
					host              VARCHAR(64)    NULL,
					proxy_protocol    BOOLEAN        NOT NULL,
					created_at        TIMESTAMP      NOT NULL DEFAULT now(),
					updated_at        TIMESTAMP      NOT NULL DEFAULT now(),
					project_id        VARCHAR(36)    NOT NULL,
					tags              VARCHAR(64)[]  NOT NULL DEFAULT '{}',
					CONSTRAINT visibility CHECK (visibility IN ('private', 'public')),
					CONSTRAINT status FOREIGN KEY (status) REFERENCES service_status(name),
					UNIQUE (network_id, ip_addresses, availability_zone)
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE service_port
				(
					service_id UUID NOT NULL,
					port_id    UUID NOT NULL,
					UNIQUE(port_id),
					CONSTRAINT fk_service FOREIGN KEY(service_id) REFERENCES service(id) ON DELETE CASCADE
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE endpoint_status
				(
					name VARCHAR(16) PRIMARY KEY
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				INSERT INTO
					endpoint_status(name)
				VALUES
					('AVAILABLE'),
					('PENDING_APPROVAL'),
					('PENDING_CREATE'),
					('PENDING_DELETE'),
					('PENDING_REJECTED'),
					('REJECTED'),
					('FAILED')
				;`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE endpoint
				(
					id                UUID           DEFAULT gen_random_uuid() PRIMARY KEY,
					service_id        UUID           NOT NULL,
					status            VARCHAR(18)    NOT NULL DEFAULT 'PENDING_CREATE',
					created_at        TIMESTAMP      NOT NULL DEFAULT now(),
					updated_at        TIMESTAMP      NOT NULL DEFAULT now(),
					project_id        VARCHAR(36)    NOT NULL,
					tags              VARCHAR(64)[]  NOT NULL DEFAULT '{}',
					CONSTRAINT fk_service FOREIGN KEY(service_id) REFERENCES service(id),
					CONSTRAINT fk_status FOREIGN KEY (status) REFERENCES endpoint_status(name)
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE endpoint_port
				(
				    endpoint_id UUID NOT NULL PRIMARY KEY,
					port_id	    UUID NOT NULL,
					subnet      UUID NOT NULL,
					network     UUID NOT NULL,
					ip_address  INET NOT NULL,
					UNIQUE(endpoint_id),
					CONSTRAINT fk_port FOREIGN KEY(endpoint_id) REFERENCES endpoint(id) ON DELETE CASCADE
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE agents
				(
					host                 VARCHAR(255) NOT NULL,
					availability_zone    VARCHAR(64),
					UNIQUE(host)
				);`,
		); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE rbac
				(
					id                UUID         DEFAULT gen_random_uuid() PRIMARY KEY,
					target_project    VARCHAR(36)  NOT NULL,
					service_id        UUID         NOT NULL,
					project_id        VARCHAR(36)  NOT NULL,
					created_at        TIMESTAMP    NOT NULL DEFAULT now(),
					updated_at        TIMESTAMP    NOT NULL DEFAULT now(),
					CONSTRAINT fk_service FOREIGN KEY(service_id) REFERENCES service(id) ON DELETE CASCADE,
					UNIQUE(target_project, service_id)
				);`); err != nil {
			return err
		}

		if _, err := commands.Exec(ctx, `
				CREATE TABLE quota
				(
					project_id  VARCHAR(36)  NOT NULL PRIMARY KEY,
					service     BIGINT       NOT NULL,
					endpoint    BIGINT       NOT NULL
				);`); err != nil {
			return err
		}

		return nil
	}),
	mgx.NewMigration("add_provider", func(ctx context.Context, commands mgx.Commands) error {
		if _, err := commands.Exec(ctx, `
			ALTER TABLE service
    			ADD COLUMN provider VARCHAR(64) DEFAULT 'tenant' CONSTRAINT provider CHECK (provider IN ('tenant', 'cp'));
		`); err != nil {
			return err
		}

		return nil
	}),
	mgx.NewMigration("add_agents", func(ctx context.Context, commands mgx.Commands) error {
		if _, err := commands.Exec(ctx, `
			DROP TABLE IF EXISTS agents;
			CREATE TABLE agents
			(
				host              VARCHAR(64)  NOT NULL PRIMARY KEY,
				availability_zone VARCHAR(64)  NOT NULL,
				created_at        TIMESTAMP    NOT NULL DEFAULT now(),
				updated_at        TIMESTAMP    NOT NULL DEFAULT now(),
				enabled           BOOLEAN      DEFAULT true NOT NULL,
				provider          VARCHAR(64)  DEFAULT 'tenant' NOT NULL
			);
		`); err != nil {
			return err
		}

		return nil
	}),
	mgx.NewMigration("add_quota_error", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, "INSERT INTO service_status(name) VALUES ('ERROR_QUOTA');")
		return err
	}),
	mgx.NewMigration("adapt_constraint", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service DROP CONSTRAINT service_network_id_ip_addresses_availability_zone_key;
			ALTER TABLE service ADD CONSTRAINT service_const UNIQUE (host, network_id, ip_addresses, availability_zone);
		`)
		return err
	}),
	mgx.NewMigration("add_endpoint_name", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE endpoint ADD COLUMN name VARCHAR(64) NOT NULL DEFAULT '';
			ALTER TABLE endpoint ADD COLUMN description VARCHAR(255) NOT NULL DEFAULT '';
		`)
		return err
	}),
	mgx.NewMigration("unique_endpoint_port", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE endpoint_port ADD CONSTRAINT endpoint_port_uniq UNIQUE (port_id);
		`)
		return err
	}),
	mgx.NewMigration("add_endpoint_port_ownership", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE endpoint_port ADD COLUMN owned BOOLEAN NOT NULL DEFAULT true;
		`)
		return err
	}),
	mgx.NewMigration("add_endpoint_port_segment_id", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE endpoint_port ADD COLUMN segment_id INTEGER NULL;
		`)
		return err
	}),
	mgx.NewMigration("make_agents_az_nullable", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE agents ALTER COLUMN availability_zone DROP NOT NULL;
			UPDATE agents SET availability_zone = NULL WHERE availability_zone = '';
		`)
		return err
	}),
	mgx.NewMigration("drop_service_port_table", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			DROP TABLE service_port;
		`)
		return err
	}),
	mgx.NewMigration("add_endpoint_pending_update_status", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
				INSERT INTO
					endpoint_status(name)
				VALUES
					('PENDING_UPDATE');
		`)
		return err
	}),

	mgx.NewMigration("add_protocol", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service
			    ADD COLUMN protocol VARCHAR(5) NOT NULL DEFAULT 'HTTP'
				CONSTRAINT protocol CHECK (protocol IN ('TCP', 'HTTP'));
		`)
		return err
	}),

	mgx.NewMigration("add_physnet", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE agents ADD COLUMN physnet VARCHAR(64) NULL;
		`)
		return err
	}),

	mgx.NewMigration("fix_service_constraint", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service DROP CONSTRAINT service_const;
			ALTER TABLE service ADD CONSTRAINT service_const UNIQUE (host, network_id, ip_addresses, availability_zone, port);
		`)
		return err
	}),
	mgx.NewMigration("port_array", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service ALTER COLUMN port TYPE INTEGER[] USING ARRAY[port];
			-- rename port to ports
			ALTER TABLE service RENAME COLUMN port TO ports;
			UPDATE service SET status = 'PENDING_UPDATE' WHERE status = 'AVAILABLE';
			UPDATE endpoint SET status = 'PENDING_UPDATE' WHERE status = 'AVAILABLE';
		`)
		return err
	}),
	mgx.NewMigration("set_to_pending_update", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			-- set all services and endpoints to PENDING_UPDATE to trigger reconciliation
			UPDATE service SET status = 'PENDING_UPDATE' WHERE status = 'AVAILABLE' AND provider = 'tenant';
			UPDATE endpoint SET status = 'PENDING_UPDATE' WHERE status = 'AVAILABLE' AND endpoint.id IN (SELECT ep.id FROM endpoint ep JOIN service s ON ep.service_id = s.id WHERE s.provider = 'tenant');
		`)
		return err
	}),
	mgx.NewMigration("add_health_status", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service ADD COLUMN health_status VARCHAR(16) DEFAULT 'UNCHECKED'
				CONSTRAINT health_status CHECK (health_status IN ('ONLINE', 'DEGRADED', 'OFFLINE', 'UNCHECKED'));
		`)
		return err
	}),
	mgx.NewMigration("add_agent_heartbeat", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE agents ADD COLUMN heartbeat_at TIMESTAMP NOT NULL DEFAULT now();
			CREATE INDEX idx_agents_heartbeat ON agents (provider, enabled, heartbeat_at);
		`)
		return err
	}),

	mgx.NewMigration("relax_service_constraint", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service DROP CONSTRAINT service_const;
		`)
		return err
	}),
	mgx.NewMigration("increase_tag_length", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service ALTER COLUMN tags TYPE VARCHAR(128)[];
			ALTER TABLE endpoint ALTER COLUMN tags TYPE VARCHAR(128)[];
		`)
		return err
	}),
	mgx.NewMigration("add_connection_mirroring", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE endpoint ADD COLUMN connection_mirroring BOOLEAN NOT NULL DEFAULT false;

			-- Enable mirroring for all existing F5 (tenant provider) endpoints
			-- to preserve current behavior where Mirroring was hardcoded to "L4"
			UPDATE endpoint SET connection_mirroring = true
			WHERE service_id IN (SELECT id FROM service WHERE provider = 'tenant');
		`)
		return err
	}),
	mgx.NewMigration("add_snat_pool_size", func(ctx context.Context, commands mgx.Commands) error {
		_, err := commands.Exec(ctx, `
			ALTER TABLE service
				ADD COLUMN snat_pool_size INTEGER NULL
				CONSTRAINT snat_pool_size CHECK (snat_pool_size IS NULL OR (snat_pool_size BETWEEN 1 AND 8));
		`)
		return err
	}),
	mgx.NewMigration("default_snat_pool_size", func(ctx context.Context, commands mgx.Commands) error {

		_, err := commands.Exec(ctx, `
			UPDATE service SET snat_pool_size = 1 WHERE snat_pool_size IS NULL;
			ALTER TABLE service ALTER COLUMN snat_pool_size SET NOT NULL;
			ALTER TABLE service ALTER COLUMN snat_pool_size SET DEFAULT 1;
			ALTER TABLE service DROP CONSTRAINT snat_pool_size;
			ALTER TABLE service ADD CONSTRAINT snat_pool_size
				CHECK (snat_pool_size BETWEEN 1 AND 8);
		`)
		return err
	}),
)